新特性解讀 | 8.0 新增 DML 語句（TABLE & VALUES）
- 2020 年 3 月 26 日
- 筆記
作者：楊濤濤
資深數據庫專家，專研 MySQL 十餘年。擅長 MySQL、PostgreSQL、MongoDB 等開源數據庫相關的備份恢復、SQL 調優、監控運維、高可用架構設計等。目前任職於愛可生，為各大運營商及銀行金融企業提供 MySQL 相關技術支持、MySQL 相關課程培訓等工作。
本文來源：原創投稿
*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
背景
MySQL 8.0.19 release 發佈了兩條新的 DML 語句。一條 TABLE 語句，一條 VALUES 語句。這裡不要把這兩條語句混淆了。
TABLE 不是廣義的表，而僅僅是一條語句，應用於需要全表掃描的場景。
還有 VALUES 語句也不要混淆為 INSERT…VALUES…這樣的傳統插入語句。VALUES 是一個全新的模擬記錄集的語句，類似於其他數據庫比如 PGSQL 的 ROW 語句。
一、應用場景
1. TABLE 語句
- 具體用在小表的全表掃描，比如路由表、配置類表、簡單的映射表等。
- 用來替換是被當做子查詢的這類小表的 SELECT 語句。
2. VALUES 語句
- VALUES 類似於其他數據庫的 ROW 語句，造數據時非常有用。
二、語法使用
那現在針對這兩類 DML 語句，結合實際例子說明下其具體用途。
2.1 TABLE 語句
具體語法：
TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]
其實從語法上看，可以排序，也可以過濾記錄集，不過比較簡單，沒有 SELECT 那麼強大。
示例 1
簡單的建一張很小的表 y1，記錄數為 10 條。
表 t1，插入 10 條記錄
mysql-(ytt/3305)->create table t1 (r1 int,r2 int); Query OK, 0 rows affected (0.02 sec) mysql-(ytt/3305)->insert into t1 with recursive aa(a,b) as ( select 1,1 union all select a+1,ceil(rand()*20) from aa where a < 10 ) select * from aa; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
簡單全表掃描
mysql-(ytt/3305)->select * from t1; +------+------+ | r1 | r2 | +------+------+ | 1 | 1 | | 2 | 9 | | 3 | 9 | | 4 | 17 | | 5 | 17 | | 6 | 16 | | 7 | 6 | | 8 | 1 | | 9 | 10 | | 10 | 3 | +------+------+ 10 rows in set (0.00 sec)
TABLE 結果
mysql-(ytt/3305)->table t1; +------+------+ | r1 | r2 | +------+------+ | 1 | 1 | | 2 | 9 | | 3 | 9 | | 4 | 17 | | 5 | 17 | | 6 | 16 | | 7 | 6 | | 8 | 1 | | 9 | 10 | | 10 | 3 | +------+------+ 10 rows in set (0.00 sec)
看下 table 的執行計劃
mysql-(ytt/3305)->explain table t1 order by r1 limit 2G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)
其實可以看到 TABLE 內部被 MySQL 轉換為 SELECT 了。
mysql-(ytt/3305)->show warningsG *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `ytt`.`t1`.`r1` AS `r1`,`ytt`.`t1`.`r2` AS `r2` from `ytt`.`t1` order by `ytt`.`t1`.`r1` limit 2 1 row in set (0.00 sec)
那其實從上面簡單的例子可以看到 TABLE 在內部被轉成了普通的 SELECT 來處理。
示例 2
應用於子查詢里的子表。這裡要注意，內表的字段數量必須和外表過濾的字段數量一致。
克隆表 t1 結構
mysql-(ytt/3305)->create table t2 like t1; Query OK, 0 rows affected (0.02 sec)
克隆表 t1 數據
mysql-(ytt/3305)->insert into t2 table t1; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
table t1 被當做內表，表 t1 有兩個字段，必須同時滿足 t2 檢索時過濾的字段也是兩個。
mysql-(ytt/3305)->select * from t2 where (r1,r2) in (table t1); +------+------+ | r1 | r2 | +------+------+ | 1 | 1 | | 2 | 9 | | 3 | 9 | | 4 | 17 | | 5 | 17 | | 6 | 16 | | 7 | 6 | | 8 | 1 | | 9 | 10 | | 10 | 3 | +------+------+ 10 rows in set (0.00 sec)
注意：這裡如果過濾的字段數量和子表數量不一致，則會報錯。
2.2 VALUES 語句
具體語法：
VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number] row_constructor_list: ROW(value_list)[, ROW(value_list)][, ...] value_list: value[, value][, ...] column_designator: column_index
VALUES 語句，用做功能展示或者快速造數據場景，結果列名字以 COLUMN_0 開頭，以此類推，舉個簡單例子。
單條 VALUES 語句
mysql-(ytt/3305)->values row(1,2,3); +----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1| 2| 3| +----------+----------+----------+ 1 row inset(0.00 sec)
多條 VALUES 語句
mysql-(ytt/3305)->values row(1,2,3),row(10,9,8); +----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 10 | 9 | 8 | +----------+----------+----------+ 2 rows in set (0.00 sec)
多條 VALUES 聯合 UNION ALL
mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values row(-1,-2,0),row(10,29,30),row(100,20,-9); +----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 10 | 9 | 8 | | -1 | -2 | 0 | | 10 | 29 | 30 | | 100 | 20 | -9 | +----------+----------+----------+ 5 rows in set (0.00 sec)
根據字段下標排序，從 1 開始
mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values row(-1,-2,0),row(10,29,30),row(100,20,-9) order by 1 desc ; +----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 100 | 20 | -9 | | 10 | 9 | 8 | | 10 | 29 | 30 | | 1 | 2 | 3 | | -1 | -2 | 0 | +----------+----------+----------+ 5 rows in set (0.00 sec)
類型可以任意組合：bit,json,datetime,int,decimal 等
mysql-(ytt/3305)->values row(100,200,300), row('2020-03-10 12:14:15','mysql','test'), row(16.22,TRUE,b'1'), row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble')); +---------------------+----------------------------+--------------------+ | column_0 | column_1 | column_2 | +---------------------+----------------------------+--------------------+ | 100 | 200 | 0x333030 | | 2020-03-10 12:14:15 | mysql | 0x74657374 | | 16.22 | 1 | 0x01 | | c86fd1a7 | {"name":"lucy","age":"28"} | 0x3634363236433635 | +---------------------+----------------------------+--------------------+ 4 rows in set (0.00 sec)
新建表 t3，把剛才這些記錄寫進去
mysql-(ytt/3305)->create table t3 (r1 varchar(100),r2 varchar(100),r3 varchar(100)); Query OK, 0 rows affected (0.02 sec)
寫入到表 t3
mysql-(ytt/3305)->insert into t3 values row(100,200,300), row('2020-03-10 12:14:15','mysql','test'), row(16.22,TRUE,b'1'), row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble')); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
總結
這裡介紹了 MySQL 8.0.19 里發佈後新增的兩條 DML 語句 TABLE 和 VALUES，希望對大家有幫助。
