MySQL 8.0中新增的功能（七）

EXPLAIN ANALYZE 语句

在MySQL 8.0.18中引入了一种新形式的EXPLAIN语句，即EXPLAIN ANALYZE，它提供了关于SELECT语句执行的扩展信息，以TREE格式显示查询过程中每个迭代器的执行计划，并可以比较查询的预计成本与实际成本。这些信息包括启动成本、总成本、该迭代器返回的行数以及执行的循环次数。
在MySQL 8.0.21及更高版本中，该语句还支持FORMAT=TREE说明符。TREE是唯一支持的格式。

查询类型转换注入

在MySQL 8.0.18及更高版本中，MySQL将类型转换操作插入到表达式和条件内的查询项目树中，当参数的数据类型与预期的数据类型不匹配时。这对查询结果或执行速度没有影响，但使得执行的查询等效于符合SQL标准的查询，同时保持与之前MySQL版本的向后兼容性。

在MySQL 8.0.18及更高版本中，现在在使用任何标准的数值比较运算符（=、>=、>、<、<=、<>/!=或<=>）进行比较时，会在时间类型（DATE、DATETIME、TIMESTAMP、TIME）和数值类型（SMALLINT、TINYINT、MEDIUMINT、INT/INTEGER、BIGINT、DECIMAL/NUMERIC、FLOAT、DOUBLE、REAL、BIT）之间执行隐式转换。在这种情况下，任何不是DOUBLE的值都被强制转换为DOUBLE类型。还会在DATE或TIME值与DATETIME值之间的比较中进行转换注入，其中根据需要将参数转换为DATETIME类型。

从MySQL 8.0.21开始，还会在将字符串类型与其他类型进行比较时执行此类转换。需要进行转换的字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。当将字符串类型的值与数值类型或YEAR进行比较时，字符串被转换为DOUBLE；如果另一个参数的类型不是FLOAT、DOUBLE或REAL，则它也被转换为DOUBLE。当将字符串类型与DATETIME或TIMESTAMP值进行比较时，字符串被转换为DATETIME；当将字符串类型与DATE进行比较时，字符串被转换为DATE。

可以通过查看EXPLAIN ANALYZE，EXPLAIN FORMAT=JSON或EXPLAIN FORMAT=TREE的输出，来了解何时在给定的查询中进行了类型转换注入。

mysql> CREATE TABLE d (dt DATETIME, d DATE, t TIME);
Query OK, 0 rows affected (0.62 sec)
mysql> CREATE TABLE n (i INT, d DECIMAL, f FLOAT, dc DECIMAL);
Query OK, 0 rows affected (0.51 sec)
mysql> CREATE TABLE s (c CHAR(25), vc VARCHAR(25),
 -> bn BINARY(50), vb VARBINARY(50), b BLOB, t TEXT,
 -> e ENUM('a', 'b', 'c'), se SET('x' ,'y', 'z'));
Query OK, 0 rows affected (0.50 sec)
mysql> EXPLAIN FORMAT=TREE SELECT * from d JOIN n ON d.dt = n.i\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (cast(d.dt as double) = cast(n.i as double))
(cost=0.70 rows=1)
 -> Table scan on n (cost=0.35 rows=1)
 -> Hash
 -> Table scan on d (cost=0.35 rows=1)
mysql> EXPLAIN FORMAT=TREE SELECT * from s JOIN d ON d.dt = s.c\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (d.dt = cast(s.c as datetime(6))) (cost=0.72 rows=1)
 -> Table scan on d (cost=0.37 rows=1)
 -> Hash
 -> Table scan on s (cost=0.35 rows=1)
1 row in set (0.01 sec)
mysql> EXPLAIN FORMAT=TREE SELECT * from n JOIN s ON n.d = s.c\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (cast(n.d as double) = cast(s.c as double)) (cost=0.70 rows=1)
 -> Table scan on s (cost=0.35 rows=1)
 -> Hash
 -> Table scan on n (cost=0.35 rows=1)
1 row in set (0.00 sec)

通过执行EXPLAIN [FORMAT=TRADITIONAL]语句，也可以看到这样的类型转换。在这种情况下，还需要在执行EXPLAIN语句之后执行SHOW WARNINGS语句。

TIMESTAMP和DATETIME类型的时间区域支持

从MySQL 8.0.19版本开始，服务器接受在插入日期时间（TIMESTAMP和DATETIME）值时使用的时区偏移量。这个偏移量的格式与设置time_zone系统变量时使用的格式相同，只是当偏移量的小时部分小于10时，需要前导零，并且不允许使用“-00:00”。包含时区偏移量的日期时间字面值的示例包括'2019-12-11 10:40:30-05:00'、'2003-04-14 03:30:00+10:00'和'2020-01-01 15:35:45+05:30'。

当选择日期时间值时，不会显示时区偏移量。

包含时区偏移量的日期时间字面值可以用作预处理语句的参数值。

作为这项工作的一部分，用于设置time_zone系统变量的值现在也被限制在-13:59到+14:00的范围内（包括边界）。（仍然可以将名称值分配给time_zone变量，例如'EST'，'Posix/Australia/Brisbane'和'Europe/Stockholm'，前提是加载了MySQL时区表。

JSON模式CHECK约束失败的详细信息

在MySQL 8.0.19及更高版本中，当使用JSON_SCHEMA_VALID()来指定CHECK约束时，提供了关于此类约束失败原因的精确信息。

使用ON DUPLICATE KEY UPDATE时的行和列别名

从MySQL 8.0.19版本开始，可以使用别名引用要插入的行，以及可选地引用其列。考虑以下对具有列a和b的表t执行的INSERT语句：

INSERT INTO t SET a=9,b=5
 ON DUPLICATE KEY UPDATE a=VALUES(a)+VALUES(b);

使用别名"new"表示新行，并在某些情况下使用别名"m"和"n"表示该行的列，可以对INSERT语句进行多种不同的重写。以下是一些示例：

INSERT INTO t SET a=9,b=5 AS new
 ON DUPLICATE KEY UPDATE a=new.a+new.b;
INSERT INTO t VALUES(9,5) AS new
 ON DUPLICATE KEY UPDATE a=new.a+new.b;
INSERT INTO t SET a=9,b=5 AS new(m,n)
 ON DUPLICATE KEY UPDATE a=m+n;
INSERT INTO t VALUES(9,5) AS new(m,n)
 ON DUPLICATE KEY UPDATE a=m+n;

SQL标准中的显式表子句和表值构造器

根据SQL标准，MySQL 8.0.19引入了表值构造器（Table Value Constructor）和显式表子句（Explicit Table Clause）。这两个功能分别通过 ?TABLE?语句和 ?VALUES?语句进行实现。

1. ??TABLE?语句的格式为 ?TABLE table_name?，它等同于 ?SELECT * FROM table_name?。它支持 ?ORDER BY?和 ?LIMIT?子句（后者可选包括 ?OFFSET?），但不允许选择单个表列。?TABLE?语句可以在任何需要相应 ?SELECT?语句的地方使用，包括连接、联合查询、插入选择、替换、创建表选择语句和子查询。例如：

   • ??TABLE t1 UNION TABLE t2? 等同于 ?SELECT * FROM t1 UNION SELECT * FROM t2?
   • ??CREATE TABLE t2 TABLE t1? 等同于 ?CREATE TABLE t2 SELECT * FROM t1?
   • ??SELECT a FROM t1 WHERE b > ANY (TABLE t2)? 等同于 ?SELECT a FROM t1 WHERE b > ANY (SELECT * FROM t2)?

2. ??VALUES?语句用于向 ?INSERT?、?REPLACE?或 ?SELECT?语句提供一个表值，它由 ?VALUES?关键字后跟一系列行构造器（?ROW()?）组成，以逗号分隔。例如，语句 ?INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9)? 提供了与MySQL特定的 ?INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9)?等效的标准SQL。您还可以从 ?VALUES?表值构造器中进行选择，就像选择任何其他表一样，当这样做时，您必须提供一个表别名，并且可以像处理其他表一样使用这个 ?SELECT?语句，包括联接、联合和子查询。

FORCE INDEX和IGNORE INDEX的优化提示?

新的优化提示如下，并附有它们的FORCE INDEX或IGNORE INDEX等效提示：

• GROUP_INDEX: 等同于FORCE INDEX FOR GROUP BY
• NO_GROUP_INDEX: 等同于IGNORE INDEX FOR GROUP BY
• JOIN_INDEX: 等同于FORCE INDEX FOR JOIN
• NO_JOIN_INDEX: 等同于IGNORE INDEX FOR JOIN
• ORDER_INDEX: 等同于FORCE INDEX FOR ORDER BY
• NO_ORDER_INDEX: 等同于IGNORE INDEX FOR ORDER BY
• INDEX: 相当于GROUP_INDEX加上JOIN_INDEX和ORDER_INDEX；等同于没有修饰符的FORCE INDEX
• NO_INDEX: 相当于NO_GROUP_INDEX加上NO_JOIN_INDEX和NO_ORDER_INDEX；等同于没有修饰符的IGNORE INDEX

例如，以下两个查询是等价的：

SELECT a FROM t1 FORCE INDEX (i_a) FOR JOIN WHERE a=1 AND b=2;
SELECT /*+ JOIN_INDEX(t1 i_a) */ a FROM t1 WHERE a=1 AND b=2;

以前列出的优化提示遵循与现有索引级别优化提示相同的基本语法和用法规则。

这些优化提示旨在替代FORCE INDEX和IGNORE INDEX，我们计划在未来的MySQL版本中弃用它们，并随后从MySQL中删除它们。它们没有为USE INDEX提供单个完全等效的提示；而是可以使用NO_INDEX、NO_JOIN_INDEX、NO_GROUP_INDEX或NO_ORDER_INDEX中的一个或多个来实现相同的效果。

JSON_VALUE() 函数

MySQL 8.0.21引入了一个名为JSON_VALUE()的新函数，旨在简化对JSON列的索引。它的最基本形式是以一个JSON文档和一个指向该文档中单个值的JSON路径作为参数，还可以使用RETURNING关键字可选地指定返回类型。JSON_VALUE(json_doc, path RETURNING type) 等同于以下表达式：

CAST(
 JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
 AS type
);

您还可以类似于在JSON_TABLE()中使用的方式，指定ON EMPTY、ON ERROR或两者都指定的子句。

您可以使用JSON_VALUE()函数在JSON列上创建表达式索引，如下所示：

CREATE TABLE t1(
 j JSON,
 INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);
INSERT INTO t1 VALUES ROW('{"id": "123", "name": "shoes", "price": "49.95"}');

一个使用这个表达式的查询，就像下面展示的那样，可以利用这个索引：

SELECT name, price FROM t1
 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123;

在许多情况下，使用JSON_VALUE()函数直接在JSON列上创建索引会比先创建一个生成列然后再在这个生成列上创建索引更简单。