- MySQL8新特性概述
MySQL從5.7版本直接跳躍發(fā)布了8.0版本 ，可見這是一個(gè)令人興奮的里程碑版本。MySQL 8版本在功能上做了顯著的改進(jìn)與增強(qiáng)，開發(fā)者對(duì)MySQL的源代碼進(jìn)行了重構(gòu)，最突出的一點(diǎn)是多MySQL Optimizer優(yōu)化器進(jìn)行了改進(jìn)。不僅在速度上得到了改善，還為用戶帶來了更好的性能和更棒的體驗(yàn)。
1.1 MySQL8.0 新增特性 - 更簡便的NoSQL支持 NoSQL泛指非關(guān)系型數(shù)據(jù)庫和數(shù)據(jù)存儲(chǔ)。隨著互聯(lián)網(wǎng)平臺(tái)的規(guī)模飛速發(fā)展，傳統(tǒng)的關(guān)系型數(shù)據(jù)庫已經(jīng)越來越不能滿足需求。從5.6版本開始，MySQL就開始支持簡單的NoSQL存儲(chǔ)功能。MySQL 8對(duì)這一功能做了優(yōu)化，以更靈活的方式實(shí)現(xiàn)NoSQL功能，不再依賴模式（schema）。
- 更好的索引 在查詢中，正確地使用索引可以提高查詢的效率。MySQL 8中新增了 隱藏索引 和 降序索 引 。隱藏索引可以用來測(cè)試去掉索引對(duì)查詢性能的影響。在查詢中混合存在多列索引時(shí)，使用降序索引可以提高查詢的性能。
3.更完善的JSON支持 MySQL從5.7開始支持原生JSON數(shù)據(jù)的存儲(chǔ)，MySQL 8對(duì)這一功能做了優(yōu)化，增加了聚合函數(shù) JSON_ARRAYAGG() 和 JSON_OBJECTAGG() ，將參數(shù)聚合為JSON數(shù)組或?qū)ο?，新增了行?nèi)操作符 ->>，是列路徑運(yùn)算符 ->的增強(qiáng)，對(duì)JSON排序做了提升，并優(yōu)化了JSON的更新操作。
4.安全和賬戶管理 MySQL 8中新增了 caching_sha2_password 授權(quán)插件、角色、密碼歷史記錄和FIPS模式支持，這些特性提高了數(shù)據(jù)庫的安全性和性能，使數(shù)據(jù)庫管理員能夠更靈活地進(jìn)行賬戶管理工作。
5.InnoDB的變化 InnoDB是MySQL默認(rèn)的存儲(chǔ)引擎 ，是事務(wù)型數(shù)據(jù)庫的首選引擎，支持事務(wù)安全表（ACID），支持行鎖定和外鍵。在MySQL 8 版本中，InnoDB在自增、索引、加密、死鎖、共享鎖等方面做了大量的 改進(jìn)和優(yōu)化 ，并且支持原子數(shù)據(jù)定義語言（DDL），提高了數(shù)據(jù)安全性，對(duì)事務(wù)提供更好的支持。
6.數(shù)據(jù)字典 在之前的MySQL版本中，字典數(shù)據(jù)都存儲(chǔ)在元數(shù)據(jù)文件和非事務(wù)表中。從MySQL 8開始新增了事務(wù)數(shù)據(jù)字典，在這個(gè)字典里存儲(chǔ)著數(shù)據(jù)庫對(duì)象信息，這些數(shù)據(jù)字典存儲(chǔ)在內(nèi)部事務(wù)表中 -
原子數(shù)據(jù)定義語句 MySQL 8開始支持原子數(shù)據(jù)定義語句（Automic DDL），即 原子DDL 。目前，只有InnoDB存儲(chǔ)引擎支持原子DDL。原子數(shù)據(jù)定義語句（DDL）將與DDL操作相關(guān)的數(shù)據(jù)字典更新、存儲(chǔ)引擎操作、二進(jìn)制日志寫入結(jié)合到一個(gè)單獨(dú)的原子事務(wù)中，這使得即使服務(wù)器崩潰，事務(wù)也會(huì)提交或回滾。使用支持原子操作的存儲(chǔ)引擎所創(chuàng)建的表，在執(zhí)行DROP TABLE、CREATE TABLE、ALTER TABLE、 RENAME TABLE、TRUNCATE TABLE、CREATE TABLESPACE、DROP TABLESPACE等操作時(shí)，都支持原子操作，即事務(wù)要么完全操作成功，要么失敗后回滾，不再進(jìn)行部分提交。 對(duì)于從MySQL 5.7復(fù)制到MySQL 8版本中的語句，可以添加 IF EXISTS 或 IF NOT EXISTS 語句來避免發(fā)生錯(cuò)誤。
8.資源管理 MySQL 8開始支持創(chuàng)建和管理資源組，允許將服務(wù)器內(nèi)運(yùn)行的線程分配給特定的分組，以便線程根據(jù)組內(nèi)可用資源執(zhí)行。組屬性能夠控制組內(nèi)資源，啟用或限制組內(nèi)資源消耗。數(shù)據(jù)庫管理員能夠根據(jù)不同的工作負(fù)載適當(dāng)?shù)馗倪@些屬性。 目前，CPU時(shí)間是可控資源，由“虛擬CPU”這個(gè)概念來表示，此術(shù)語包含CPU的核心數(shù)，超線程，硬件線程等等。服務(wù)器在啟動(dòng)時(shí)確定可用的虛擬CPU數(shù)量。擁有對(duì)應(yīng)權(quán)限的數(shù)據(jù)庫管理員可以將這些CPU與資源組關(guān)聯(lián)，并為資源組分配線程。 資源組組件為MySQL中屬性，除去名字和類型，其他屬性都可在創(chuàng)建之后進(jìn)行更改。 在一些平臺(tái)下，或進(jìn)行了某些MySQL的配置時(shí)，資源管理的功能將受到限制，甚至不可用。例如，如果安裝了線程池插件，或者使用的是macOS系統(tǒng)，資源管理將處于不可用狀態(tài)。在FreeBSD和Solaris系統(tǒng)中，資源線程優(yōu)先級(jí)將失效。在Linux系統(tǒng)中，只有配置了CAP_SYS_NICE屬性，資源管理優(yōu)先級(jí)才能發(fā)揮作用。
9.字符集支持 MySQL 8中默認(rèn)的字符集由 latin1 更改為 utf8mb4 ，并首次增加了日語所特定使用的集合utf8mb4_ja_0900_as_cs。
10.優(yōu)化器增強(qiáng) MySQL優(yōu)化器開始支持隱藏索引和降序索引。隱藏索引不會(huì)被優(yōu)化器使用，驗(yàn)證索引的必要性時(shí)不需要?jiǎng)h除索引，先將索引隱藏，如果優(yōu)化器性能無影響就可以真正地刪除索引。降序索引允許優(yōu)化器對(duì)多個(gè)列進(jìn)行排序，并且允許排序順序不一致。
11.公用表表達(dá)式 公用表表達(dá)式（Common Table Expressions）簡稱為CTE，MySQL現(xiàn)在支持遞歸和非遞歸兩種形式的CTE。CTE通過在SELECT語句或其他特定語句前 使用WITH語句對(duì)臨時(shí)結(jié)果集 進(jìn)行命名。基礎(chǔ)語法如下：Subquery代表子查詢，子查詢前使用WITH語句將結(jié)果集命名為cte_name，在后續(xù)的查詢中即可使用cte_name進(jìn)行查詢。
12.窗口函數(shù) MySQL 8開始支持窗口函數(shù)。在之前的版本中已存在的大部分聚合函數(shù) 在MySQL 8中也可以作為窗口函數(shù)來使用。
image.png
13.正則表達(dá)式支持 MySQL在8.0.4以后的版本中采用支持Unicode的國際化組件庫實(shí)現(xiàn)正則表達(dá)式操作，
這種方式不僅能提供完全的Unicode支持，而且是多字節(jié)安全編碼。MySQL增加了REGEXP_LIKE()、 EGEXP_INSTR()、REGEXP_REPLACE()和 REGEXP_SUBSTR()等函數(shù)來提升性能。另外，regexp_stack_limit和 regexp_time_limit 系統(tǒng)變量能夠通過匹配引擎來控制資源消耗。
14.內(nèi)部臨時(shí)表 TempTable存儲(chǔ)引擎取代MEMORY存儲(chǔ)引擎成為內(nèi)部臨時(shí)表的默認(rèn)存儲(chǔ)引擎 。TempTable存儲(chǔ)引擎為VARCHAR和VARBINARY列提供高效存儲(chǔ)。internal_tmp_mem_storage_engine會(huì)話變量定義了內(nèi)部臨時(shí)表的存儲(chǔ)引擎，可選的值有兩個(gè)，TempTable和MEMORY，其中TempTable為默認(rèn)的存儲(chǔ)引擎。temptable_max_ram系統(tǒng)配置項(xiàng)定義了TempTable存儲(chǔ)引擎可使用的最大內(nèi)存數(shù)量。
15.日志記錄 在MySQL 8中錯(cuò)誤日志子系統(tǒng)由一系列MySQL組件構(gòu)成。這些組件的構(gòu)成由系統(tǒng)變量
log_error_services來配置，能夠?qū)崿F(xiàn)日志事件的過濾和寫入。
17.增強(qiáng)的MySQL復(fù)制 MySQL 8復(fù)制支持對(duì) JSON文檔 進(jìn)行部分更新的 二進(jìn)制日志記錄 ，該記錄 使用緊湊 的二進(jìn)制格式 ，從而節(jié)省記錄完整JSON文檔的空間。當(dāng)使用基于語句的日志記錄時(shí)，這種緊湊的日志記錄會(huì)自動(dòng)完成，并且可以通過將新的binlog_row_value_options系統(tǒng)變量值設(shè)置為PARTIAL_JSON來啟用。
1.2 MySQL8.0移除的舊特性
在MySQL 5.7版本上開發(fā)的應(yīng)用程序如果使用了MySQL8.0 移除的特性，語句可能會(huì)失敗，或者產(chǎn)生不同
的執(zhí)行結(jié)果。為了避免這些問題，對(duì)于使用了移除特性的應(yīng)用，應(yīng)當(dāng)盡力修正避免使用這些特性，并盡
可能使用替代方法。 - 查詢緩存 查詢緩存已被移除 ，刪除的項(xiàng)有：
（1）語句：FLUSH QUERY CACHE和RESET QUERY CACHE。
（2）系統(tǒng)變量：query_cache_limit、query_cache_min_res_unit、query_cache_size、 query_cache_type、query_cache_wlock_invalidate。
（3）狀態(tài)變量：Qcache_free_blocks、 Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、 Qcache_queries_in_cache、Qcache_total_blocks。
（4）線程狀態(tài)：checking privileges on cached query、checking query cache for query、invalidating query cache entries、sending cached result to client、storing result in query cache、waiting for query cache lock。
2.加密相關(guān) 刪除的加密相關(guān)的內(nèi)容有：ENCODE()、DECODE()、ENCRYPT()、DES_ENCRYPT()和 DES_DECRYPT()函數(shù)，配置項(xiàng)des-key-file，系統(tǒng)變量have_crypt，F(xiàn)LUSH語句的DES_KEY_FILE選項(xiàng)，
HAVE_CRYPT CMake選項(xiàng)。 對(duì)于移除的ENCRYPT()函數(shù)，考慮使用SHA2()替代，對(duì)于其他移除的函數(shù)，使
用AES_ENCRYPT()和AES_DECRYPT()替代。
3.空間函數(shù)相關(guān) 在MySQL 5.7版本中，多個(gè)空間函數(shù)已被標(biāo)記為過時(shí)。這些過時(shí)函數(shù)在MySQL 8中都已被移除，只保留了對(duì)應(yīng)的ST_和MBR函數(shù)。
4.\N和NULL 在SQL語句中，解析器不再將\N視為NULL，所以在SQL語句中應(yīng)使用NULL代替\N。這項(xiàng)變化不會(huì)影響使用LOAD DATA INFILE或者SELECT...INTO OUTFILE操作文件的導(dǎo)入和導(dǎo)出。在這類操作中，NULL仍等同于\N。 - mysql_install_db 在MySQL分布中，已移除了mysql_install_db程序，數(shù)據(jù)字典初始化需要調(diào)用帶著-- initialize或者--initialize-insecure選項(xiàng)的mysqld來代替實(shí)現(xiàn)。另外，--bootstrap和INSTALL_SCRIPTDIR CMake也已被刪除。
6.通用分區(qū)處理程序 通用分區(qū)處理程序已從MySQL服務(wù)中被移除。為了實(shí)現(xiàn)給定表分區(qū)，表所使用的存儲(chǔ)引擎需要自有的分區(qū)處理程序。 提供本地分區(qū)支持的MySQL存儲(chǔ)引擎有兩個(gè)，即InnoDB和NDB，而在MySQL 8中只支持InnoDB。
7.系統(tǒng)和狀態(tài)變量信息 在INFORMATION_SCHEMA數(shù)據(jù)庫中，對(duì)系統(tǒng)和狀態(tài)變量信息不再進(jìn)行維護(hù)。GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS表都已被刪除。另外，系統(tǒng)變量show_compatibility_56也已被刪除。被刪除的狀態(tài)變量有Slave_heartbeat_period、Slave_last_heartbeat,Slave_received_heartbeatsSlave_retried_transactions、Slave_running。以上被刪除的內(nèi)容都可使用性能模式中對(duì)應(yīng)的內(nèi)容進(jìn)行替代。
8.mysql_plugin工具 mysql_plugin工具用來配置MySQL服務(wù)器插件，現(xiàn)已被刪除，可使用--plugin-load或- -plugin-load-add選項(xiàng)在服務(wù)器啟動(dòng)時(shí)加載插件或者在運(yùn)行時(shí)使用INSTALL PLUGIN語句加載插件來替代該工具。 - 新特性1：窗口函數(shù)
MySQL從8.0版本開始支持窗口函數(shù)。窗口函數(shù)的作用類似于在查詢中對(duì)數(shù)據(jù)進(jìn)行分組，不同的是，分組
操作會(huì)把分組的結(jié)果聚合成一條記錄，而窗口函數(shù)是將結(jié)果置于每一條數(shù)據(jù)記錄中。
窗口函數(shù)可以分為 靜態(tài)窗口函數(shù) 和 動(dòng)態(tài)窗口函數(shù) 。
- 靜態(tài)窗口函數(shù)的窗口大小是固定的，不會(huì)因?yàn)橛涗浀牟煌煌?/li>
-
動(dòng)態(tài)窗口函數(shù)的窗口大小會(huì)隨著記錄的不同而變化。
窗口函數(shù)總體上可以分為序號(hào)函數(shù)、分布函數(shù)、前后函數(shù)、首尾函數(shù)和其他函數(shù)，如下表：
image.png
2.3 語法結(jié)構(gòu)
窗口函數(shù)的語法結(jié)構(gòu)是：
函數(shù) OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
或者是:
函數(shù) OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
OVER 關(guān)鍵字指定函數(shù)窗口的范圍。
如果省略后面括號(hào)中的內(nèi)容，則窗口會(huì)包含滿足WHERE條件的所有記錄，窗口函數(shù)會(huì)基于所
有滿足WHERE條件的記錄進(jìn)行計(jì)算。
如果OVER關(guān)鍵字后面的括號(hào)不為空，則可以使用如下語法設(shè)置窗口。
窗口名：為窗口設(shè)置一個(gè)別名，用來標(biāo)識(shí)窗口。
PARTITION BY子句：指定窗口函數(shù)按照哪些字段進(jìn)行分組。分組后，窗口函數(shù)可以在每個(gè)分組中分
別執(zhí)行。
ORDER BY子句：指定窗口函數(shù)按照哪些字段進(jìn)行排序。執(zhí)行排序操作使窗口函數(shù)按照排序后的數(shù)據(jù)
記錄的順序進(jìn)行編號(hào)。
FRAME子句：為分區(qū)中的某個(gè)子集定義規(guī)則，可以用來作為滑動(dòng)窗口使用。
- 序號(hào)函數(shù)
1．ROW_NUMBER()函數(shù)
ROW_NUMBER()函數(shù)能夠?qū)?shù)據(jù)中的序號(hào)進(jìn)行順序顯示。
舉例：查詢 goods 數(shù)據(jù)表中每個(gè)商品分類下價(jià)格降序排列的各個(gè)商品信息。
mysql> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
2．RANK()函數(shù)
使用RANK()函數(shù)能夠?qū)π蛱?hào)進(jìn)行并列排序，并且會(huì)跳過重復(fù)的序號(hào)，比如序號(hào)為1、1、3。
舉例：使用RANK()函數(shù)獲取 goods 數(shù)據(jù)表中各類別的價(jià)格從高到低排序的各商品信息
mysql> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
3．DENSE_RANK()函數(shù)
DENSE_RANK()函數(shù)對(duì)序號(hào)進(jìn)行并列排序，并且不會(huì)跳過重復(fù)的序號(hào)，比如序號(hào)為1、1、2。
舉例：使用DENSE_RANK()函數(shù)獲取 goods 數(shù)據(jù)表中各類別的價(jià)格從高到低排序的各商品信息。
mysql> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods; +---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 3 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 4 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 5 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 戶外運(yùn)動(dòng) | 山地自行車 | 1399.90 | 2500 | |
2 | 11 | 2 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 799.90 | 500 |
| 3 | 12 | 2 | 戶外運(yùn)動(dòng) | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 | 1000 |
| 4 | 10 | 2 | 戶外運(yùn)動(dòng) | 騎行裝備 | 399.90 | 3500 |
| 5 | 9 | 2 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 | 1500 | - 分布函數(shù)
1．PERCENT_RANK()函數(shù)
PERCENT_RANK()函數(shù)是等級(jí)值百分比函數(shù)。按照如下方式進(jìn)行計(jì)算。
(rank - 1) / (rows - 1)
其中，rank的值為使用RANK()函數(shù)產(chǎn)生的序號(hào)，rows的值為當(dāng)前窗口的總記錄數(shù)。
舉例：計(jì)算 goods 數(shù)據(jù)表中名稱為“女裝/女士精品”的類別下的商品的PERCENT_RANK值。
寫法一： SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r, PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr, id, category_id, category, NAME, price, stock FROM goods WHERE category_id = 1; #寫法二： mysql> SELECT RANK() OVER w AS r,
-> PERCENT_RANK() OVER w AS pr,
-> id, category_id, category, NAME, price, stock
-> FROM goods
-> WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);
| r | pr | id | category_id | category | NAME | price | stock |
| 1 | 0 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 0.2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 0.2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 0.6 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 0.8 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 1 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
6 rows in set (0.00 sec)
2．CUME_DIST()函數(shù)
CUME_DIST()函數(shù)主要用于查詢小于或等于某個(gè)值的比例。
舉例：查詢goods數(shù)據(jù)表中小于或等于當(dāng)前價(jià)格的比例。
mysql> SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
-> id, category, NAME, price
-> FROM goods;
| cd | id | category | NAME | price |
| 0.5 | 2 | 女裝/女士精品 | 連衣裙 | 79.90 |
| 0.8333333333333334 | 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 |
| 0.8333333333333334 | 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | | 1 | 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | | 0.16666666666666666 | 9 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 |
| 0.5 | 7 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 |
| 0.5 | 10 | 戶外運(yùn)動(dòng) | 騎行裝備 | 399.90 |
| 0.6666666666666666 | 12 | 戶外運(yùn)動(dòng) | 滑板 | 499.90 |
| 0.8333333333333334 | 11 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 799.90 |
| 1 | 8 | 戶外運(yùn)動(dòng) | 山地自行車 | 1399.90 |
- 前后函數(shù)
1．LAG(expr,n)函數(shù)
LAG(expr,n)函數(shù)返回當(dāng)前行的前n行的expr的值。
舉例：查詢goods數(shù)據(jù)表中前一個(gè)商品價(jià)格與當(dāng)前商品價(jià)格的差值。
mysql> SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
-> FROM (
-> SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
-> FROM goods
-> WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
| id | category | NAME | price | pre_price | diff_price |
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 89.90 | 0.00 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 89.90 | 310.00 |
| 9 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 | NULL | NULL |
| 7 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 | 59.90 | 340.00 |
| 10 | 戶外運(yùn)動(dòng) | 騎行裝備 | 399.90 | 399.90 | 0.00 |
| 12 | 戶外運(yùn)動(dòng) | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 799.90 | 499.90 | 300.00 |
| 8 | 戶外運(yùn)動(dòng) | 山地自行車 | 1399.90 | 799.90 | 600.00 |
2．LEAD(expr,n)函數(shù)
LEAD(expr,n)函數(shù)返回當(dāng)前行的后n行的expr的值。
舉例：查詢goods數(shù)據(jù)表中后一個(gè)商品價(jià)格與當(dāng)前商品價(jià)格的差值。
mysql> SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_price
-> FROM(
-> SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
| id | category | NAME | behind_price | price | diff_price |
| 1 | 女裝/女士精品 | T恤 | 79.90 | 39.90 | 40.00 |
| 2 | 女裝/女士精品 | 連衣裙 | 89.90 | 79.90 | 10.00 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 89.90 | 0.00 |
| 4 | 女裝/女士精品 | 牛仔褲 | 399.90 | 89.90 | 310.00 |
| 6 | 女裝/女士精品 | 呢絨外套 | NULL | 399.90 | NULL |
| 9 | 戶外運(yùn)動(dòng) | 登山杖 | 399.90 | 59.90 | 340.00 |
| 7 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 | 399.90 | 0.00 |
| 10 | 戶外運(yùn)動(dòng) | 騎行裝備 | 499.90 | 399.90 | 100.00 |
| 12 | 戶外運(yùn)動(dòng) | 滑板 | 799.90 | 499.90 | 300.00 |
| 11 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 1399.90 | 799.90 | 600.00 |
| 8 | 戶外運(yùn)動(dòng) | 山地自行車 | NULL | 1399.90 | NULL | - 首尾函數(shù)
1．FIRST_VALUE(expr)函數(shù)
FIRST_VALUE(expr)函數(shù)返回第一個(gè)expr的值。
舉例：按照價(jià)格排序，查詢第1個(gè)商品的價(jià)格信息。
mysql> SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS
first_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
| id | category | NAME | price | stock | first_price |
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 | 29.90 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 | 29.90 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 | 29.90 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 | 29.90 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 | 29.90 |
| 9 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 | 1000 | 59.90 |
| 10 | 戶外運(yùn)動(dòng) | 騎行裝備 | 399.90 | 3500 | 59.90 |
| 12 | 戶外運(yùn)動(dòng) | 滑板 | 499.90 | 1200 | 59.90 |
| 11 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 799.90 | 500 | 59.90 |
| 8 | 戶外運(yùn)動(dòng) | 山地自行車 | 1399.90 | 2500 | 59.90 |
2．LAST_VALUE(expr)函數(shù)
LAST_VALUE(expr)函數(shù)返回最后一個(gè)expr的值。
舉例：按照價(jià)格排序，查詢最后一個(gè)商品的價(jià)格信息。
mysql> SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
| id | category | NAME | price | stock | last_price |
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 | 39.90 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 | 79.90 | - 其他函數(shù)
1．NTH_VALUE(expr,n)函數(shù)
NTH_VALUE(expr,n)函數(shù)返回第n個(gè)expr的值。
舉例：查詢goods數(shù)據(jù)表中排名第2和第3的價(jià)格信息。
mysql> SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
-> NTH_VALUE(price,3) OVER w AS third_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
| id | category | NAME | price | second_price | third_price |
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 39.90 | NULL |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 39.90 | 79.90 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 39.90 | 79.90 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 39.90 | 79.90 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 39.90 | 79.90 |
| 9 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 | NULL | NULL |
| 7 | 戶外運(yùn)動(dòng) | 自行車 | 399.90 | 399.90 | 399.90 |
| 10 | 戶外運(yùn)動(dòng) | 騎行裝備 | 399.90 | 399.90 | 399.90 |
| 12 | 戶外運(yùn)動(dòng) | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 戶外運(yùn)動(dòng) | 運(yùn)動(dòng)外套 | 799.90 | 399.90 | 399.90 |
| 8 | 戶外運(yùn)動(dòng) | 山地自行車 | 1399.90 | 399.90 | 399.90 |
2．NTILE(n)函數(shù)
NTILE(n)函數(shù)將分區(qū)中的有序數(shù)據(jù)分為n個(gè)桶，記錄桶編號(hào)。
舉例：將goods表中的商品按照價(jià)格分為3組。
mysql> SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
| nt | id | category | NAME | price |
| 1 | 5 | 女裝/女士精品 | 百褶裙 | 29.90 |
| 1 | 1 | 女裝/女士精品 | T恤 | 39.90 |
| 2 | 2 | 女裝/女士精品 | 連衣裙 | 79.90 |
| 2 | 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 |
| 3 | 4 | 女裝/女士精品 | 牛仔褲 | 89.90 |
| 3 | 6 | 女裝/女士精品 | 呢絨外套 | 399.90 |
| 1 | 9 | 戶外運(yùn)動(dòng) | 登山杖 | 59.90 |
2.5 小 結(jié)
窗口函數(shù)的特點(diǎn)是可以分組，而且可以在分組內(nèi)排序。另外，窗口函數(shù)不會(huì)因?yàn)榉纸M而減少原表中的行
數(shù)，這對(duì)我們?cè)谠頂?shù)據(jù)的基礎(chǔ)上進(jìn)行統(tǒng)計(jì)和排序非常有用。 - 新特性2：公用表表達(dá)式
公用表表達(dá)式（或通用表表達(dá)式）簡稱為CTE（Common Table Expressions）。CTE是一個(gè)命名的臨時(shí)結(jié)果集，作用范圍是當(dāng)前語句。CTE可以理解成一個(gè)可以復(fù)用的子查詢，當(dāng)然跟子查詢還是有點(diǎn)區(qū)別的，CTE可以引用其他CTE，但子查詢不能引用其他子查詢。所以，可以考慮代替子查詢。依據(jù)語法結(jié)構(gòu)和執(zhí)行方式的不同，公用表表達(dá)式分為 普通公用表表達(dá)式 和 遞歸公用表表達(dá)式 2 種。
3.1 普通公用表表達(dá)式
普通公用表表達(dá)式的語法結(jié)構(gòu)是：
WITH CTE名稱 AS （子查詢） SELECT|DELETE|UPDATE 語句;
普通公用表表達(dá)式類似于子查詢，不過，跟子查詢不同的是，它可以被多次引用，而且可以被其他的普通公用表表達(dá)式所引用。
舉例：查詢員工所在的部門的詳細(xì)信息。
mysql> SELECT * FROM departments
-> WHERE department_id IN (
-> SELECT DISTINCT department_id
-> FROM employees
-> );
| department_id | department_name | manager_id | location_id |
| 10 | Administration | 200 | 1700 |
| 20 | Marketing | 201 | 1800 |
| 30 | Purchasing | 114 | 1700 |
| 40 | Human Resources | 203 | 2400 |
| 50 | Shipping | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
| 90 | Executive | 100 | 1700 |
| 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 |
11 rows in set (0.00 sec)
mysql> WITH emp_dept_id
-> AS (SELECT DISTINCT department_id FROM employees)
-> SELECT *
-> FROM departments d JOIN emp_dept_id e
-> ON d.department_id = e.department_id;
| department_id | department_name | manager_id | location_id | department_id |
| 90 | Executive | 100 | 1700 | 90 | | 60 | IT | 103 | 1400 | 60 |
| 100 | Finance | 108 | 1700 | 100 | | 30 | Purchasing | 114 | 1700 | 30 |
| 50 | Shipping | 121 | 1500 | 50 | | 80 | Sales | 145 | 2500 | 80 |
| 10 | Administration | 200 | 1700 | 10 | | 20 | Marketing | 201 | 1800 | 20 |
| 40 | Human Resources | 203 | 2400 | 40 |
| 70 | Public Relations | 204 | 2700 | 70 |
| 110 | Accounting | 205 | 1700 | 110 |
例子說明，公用表表達(dá)式可以起到子查詢的作用。以后如果遇到需要使用子查詢的場景，你可以在查詢之前，先定義公用表表達(dá)式，然后在查詢中用它來代替子查詢。而且，跟子查詢相比，公用表表達(dá)式有一個(gè)優(yōu)點(diǎn)，就是定義過公用表表達(dá)式之后的查詢，可以像一個(gè)表一樣多次引用公用表表達(dá)式，而子查詢則不能。
3.2 遞歸公用表表達(dá)式
遞歸公用表表達(dá)式也是一種公用表表達(dá)式，只不過，除了普通公用表表達(dá)式的特點(diǎn)以外，它還有自己的特點(diǎn)，就是可以調(diào)用自己。它的語法結(jié)構(gòu)是：
WITH RECURSIVE CTE名稱 AS （子查詢） SELECT|DELETE|UPDATE 語句;
遞歸公用表表達(dá)式由 2 部分組成，分別是種子查詢和遞歸查詢，中間通過關(guān)鍵字 UNION [ALL]進(jìn)行連接。
這里的種子查詢，意思就是獲得遞歸的初始值。這個(gè)查詢只會(huì)運(yùn)行一次，以創(chuàng)建初始數(shù)據(jù)集，之后遞歸
查詢會(huì)一直執(zhí)行，直到?jīng)]有任何新的查詢數(shù)據(jù)產(chǎn)生，遞歸返回。
案例：針對(duì)于我們常用的employees表，包含employee_id，last_name和manager_id三個(gè)字段。如果a是b
的管理者，那么，我們可以把b叫做a的下屬，如果同時(shí)b又是c的管理者，那么c就是b的下屬，是a的下下
屬。
下面我們嘗試用查詢語句列出所有具有下下屬身份的人員信息。
如果用我們之前學(xué)過的知識(shí)來解決，會(huì)比較復(fù)雜，至少要進(jìn)行 4 次查詢才能搞定：
第一步，先找出初代管理者，就是不以任何別人為管理者的人，把結(jié)果存入臨時(shí)表；
第二步，找出所有以初代管理者為管理者的人，得到一個(gè)下屬集，把結(jié)果存入臨時(shí)表；
第三步，找出所有以下屬為管理者的人，得到一個(gè)下下屬集，把結(jié)果存入臨時(shí)表。
第四步，找出所有以下下屬為管理者的人，得到一個(gè)結(jié)果集。
如果第四步的結(jié)果集為空，則計(jì)算結(jié)束，第三步的結(jié)果集就是我們需要的下下屬集了，否則就必須繼續(xù)
進(jìn)行第四步，一直到結(jié)果集為空為止。比如上面的這個(gè)數(shù)據(jù)表，就需要到第五步，才能得到空結(jié)果集。
用遞歸公用表表達(dá)式中的種子查詢，找出初代管理者。字段 n 表示代次，初始值為 1，表示是第一
代管理者。
用遞歸公用表表達(dá)式中的遞歸查詢，查出以這個(gè)遞歸公用表表達(dá)式中的人為管理者的人，并且代次
的值加 1。直到?jīng)]有人以這個(gè)遞歸公用表表達(dá)式中的人為管理者了，遞歸返回。
在最后的查詢中，選出所有代次大于等于 3 的人，他們肯定是第三代及以上代次的下屬了，也就是
下下屬了。這樣就得到了我們需要的結(jié)果集。
這里看似也是 3 步，實(shí)際上是一個(gè)查詢的 3 個(gè)部分，只需要執(zhí)行一次就可以了。而且也不需要用臨時(shí)表
保存中間結(jié)果，比剛剛的方法簡單多了。
代碼實(shí)現(xiàn)：
WITH RECURSIVE cte AS
(SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 種子查詢，找到第一代領(lǐng)導(dǎo)
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte ON (a.manager_id = cte.employee_id) -- 遞歸查詢，找出以遞歸公用表表達(dá)式的人為領(lǐng)導(dǎo)的人
)SELECT employee_id,last_name FROM cte WHERE n >= 3;
總之，遞歸公用表表達(dá)式對(duì)于查詢一個(gè)有共同的根節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù)，非常有用。它可以不受層級(jí)的
限制，輕松查出所有節(jié)點(diǎn)的數(shù)據(jù)。如果用其他的查詢方式，就比較復(fù)雜了。
3.3 小 結(jié)
公用表表達(dá)式的作用是可以替代子查詢，而且可以被多次引用。遞歸公用表表達(dá)式對(duì)查詢有一個(gè)共同根
節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù)非常高效，可以輕松搞定其他查詢方式難以處理的查詢。
