作者:杨涛涛

资深数据库专家,专研 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 语句

具体语法:
  1. TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]

其实从语法上看,可以排序,也可以过滤记录集,不过比较简单,没有 SELECT 那么强大。

示例 1

简单的建一张很小的表 y1,记录数为 10 条。
表 t1,插入 10 条记录
  1. mysql-(ytt/3305)->create table t1 (r1 int,r2 int);

  2. Query OK, 0 rows affected (0.02 sec)


  3. mysql-(ytt/3305)->insert into t1

  4. with recursive aa(a,b) as (

  5. select 1,1

  6. union all

  7. select a+1,ceil(rand()*20) from aa where a < 10

  8. ) select * from aa;

  9. Query OK, 10 rows affected (0.00 sec)

  10. Records: 10 Duplicates: 0 Warnings: 0

简单全表扫描
  1. mysql-(ytt/3305)->select * from t1;

  2. +------+------+

  3. | r1 | r2 |

  4. +------+------+

  5. | 1 | 1 |

  6. | 2 | 9 |

  7. | 3 | 9 |

  8. | 4 | 17 |

  9. | 5 | 17 |

  10. | 6 | 16 |

  11. | 7 | 6 |

  12. | 8 | 1 |

  13. | 9 | 10 |

  14. | 10 | 3 |

  15. +------+------+

  16. 10 rows in set (0.00 sec)

TABLE 结果
  1. mysql-(ytt/3305)->table t1;

  2. +------+------+

  3. | r1 | r2 |

  4. +------+------+

  5. | 1 | 1 |

  6. | 2 | 9 |

  7. | 3 | 9 |

  8. | 4 | 17 |

  9. | 5 | 17 |

  10. | 6 | 16 |

  11. | 7 | 6 |

  12. | 8 | 1 |

  13. | 9 | 10 |

  14. | 10 | 3 |

  15. +------+------+

  16. 10 rows in set (0.00 sec)

看下 table 的执行计划
  1. mysql-(ytt/3305)->explain table t1 order by r1 limit 2\G

  2. *************************** 1. row ***************************

  3. id: 1

  4. select_type: SIMPLE

  5. table: t1

  6. partitions: NULL

  7. type: ALL

  8. possible_keys: NULL

  9. key: NULL

  10. key_len: NULL

  11. ref: NULL

  12. rows: 10

  13. filtered: 100.00

  14. Extra: Using filesort

  15. 1 row in set, 1 warning (0.00 sec)

其实可以看到 TABLE 内部被 MySQL 转换为 SELECT 了。
  1. mysql-(ytt/3305)->show warnings\G

  2. *************************** 1. row ***************************

  3. Level: Note

  4. Code: 1003

  5. Message: /* select#1 */ select `ytt`.`t1`.`r1` AS `r1`,`ytt`.`t1`.`r2` AS `r2` from `ytt`.`t1` order by `ytt`.`t1`.`r1` limit 2

  6. 1 row in set (0.00 sec)

那其实从上面简单的例子可以看到 TABLE 在内部被转成了普通的 SELECT 来处理。

示例 2
应用于子查询里的子表。这里要注意,内表的字段数量必须和外表过滤的字段数量一致。
克隆表 t1 结构
  1. mysql-(ytt/3305)->create table t2 like t1;

  2. Query OK, 0 rows affected (0.02 sec)

克隆表 t1 数据
  1. mysql-(ytt/3305)->insert into t2 table t1;

  2. Query OK, 10 rows affected (0.00 sec)

  3. Records: 10 Duplicates: 0 Warnings: 0

table t1 被当做内表,表 t1 有两个字段,必须同时满足 t2 检索时过滤的字段也是两个。
  1. mysql-(ytt/3305)->select * from t2 where (r1,r2) in (table t1);

  2. +------+------+

  3. | r1 | r2 |

  4. +------+------+

  5. | 1 | 1 |

  6. | 2 | 9 |

  7. | 3 | 9 |

  8. | 4 | 17 |

  9. | 5 | 17 |

  10. | 6 | 16 |

  11. | 7 | 6 |

  12. | 8 | 1 |

  13. | 9 | 10 |

  14. | 10 | 3 |

  15. +------+------+

  16. 10 rows in set (0.00 sec)

注意:这里如果过滤的字段数量和子表数量不一致,则会报错。

2.2 VALUES 语句

具体语法:
  1. VALUES row_constructor_list

  2. [ORDER BY column_designator]

  3. [LIMIT BY number] row_constructor_list:

  4. ROW(value_list)[, ROW(value_list)][, ...]

  5. value_list:

  6. value[, value][, ...]

  7. column_designator:

  8. column_index

VALUES 语句,用做功能展示或者快速造数据场景,结果列名字以 COLUMN_0 开头,以此类推,举个简单例子。
单条 VALUES 语句
  1. mysql-(ytt/3305)->values row(1,2,3);

  2. +----------+----------+----------+

  3. | column_0 | column_1 | column_2 |

  4. +----------+----------+----------+

  5. | 1| 2| 3|

  6. +----------+----------+----------+

  7. 1 row inset(0.00 sec)

多条 VALUES 语句
  1. mysql-(ytt/3305)->values row(1,2,3),row(10,9,8);

  2. +----------+----------+----------+

  3. | column_0 | column_1 | column_2 |

  4. +----------+----------+----------+

  5. | 1 | 2 | 3 |

  6. | 10 | 9 | 8 |

  7. +----------+----------+----------+

  8. 2 rows in set (0.00 sec)

多条 VALUES 联合 UNION ALL
  1. mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values \

  2. row(-1,-2,0),row(10,29,30),row(100,20,-9);

  3. +----------+----------+----------+

  4. | column_0 | column_1 | column_2 |

  5. +----------+----------+----------+

  6. | 1 | 2 | 3 |

  7. | 10 | 9 | 8 |

  8. | -1 | -2 | 0 |

  9. | 10 | 29 | 30 |

  10. | 100 | 20 | -9 |

  11. +----------+----------+----------+

  12. 5 rows in set (0.00 sec)

根据字段下标排序,从 1 开始
  1. mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values \

  2. row(-1,-2,0),row(10,29,30),row(100,20,-9) order by 1 desc ;

  3. +----------+----------+----------+

  4. | column_0 | column_1 | column_2 |

  5. +----------+----------+----------+

  6. | 100 | 20 | -9 |

  7. | 10 | 9 | 8 |

  8. | 10 | 29 | 30 |

  9. | 1 | 2 | 3 |

  10. | -1 | -2 | 0 |

  11. +----------+----------+----------+

  12. 5 rows in set (0.00 sec)

类型可以任意组合:bit,json,datetime,int,decimal 等
  1. mysql-(ytt/3305)->values row(100,200,300),\

  2. row('2020-03-10 12:14:15','mysql','test'), \

  3. row(16.22,TRUE,b'1'), \

  4. row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));

  5. +---------------------+----------------------------+--------------------+

  6. | column_0 | column_1 | column_2 |

  7. +---------------------+----------------------------+--------------------+

  8. | 100 | 200 | 0x333030 |

  9. | 2020-03-10 12:14:15 | mysql | 0x74657374 |

  10. | 16.22 | 1 | 0x01 |

  11. | c86fd1a7 | {"name":"lucy","age":"28"} | 0x3634363236433635 |

  12. +---------------------+----------------------------+--------------------+

  13. 4 rows in set (0.00 sec)

新建表 t3,把刚才这些记录写进去
  1. mysql-(ytt/3305)->create table t3 (r1 varchar(100),r2 varchar(100),r3 varchar(100));

  2. Query OK, 0 rows affected (0.02 sec)

写入到表 t3
  1. mysql-(ytt/3305)->insert into t3 values row(100,200,300), \

  2. row('2020-03-10 12:14:15','mysql','test'), \

  3. row(16.22,TRUE,b'1'),\

  4. row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));

  5. Query OK, 4 rows affected (0.00 sec)

  6. Records: 4 Duplicates: 0 Warnings: 0

总结

这里介绍了 MySQL 8.0.19 里发布后新增的两条 DML 语句 TABLE 和 VALUES,希望对大家有帮助。

分类: 8.0 新特性