MySQL 8.0 21 个开发相关的新特性

这些新特性涉及的范围包括：字符集、语法、索引、JSON、从 MySQL 5.7 升级到 MySQL 8.0 需要注意的不兼容项、参数、Hint等。

1. 字符集

默认字符集由 latin1 调整为 utf8mb4。

在 MySQL 8.0 中，utf8mb4 默认的校对集是 utf8mb4_0900_ai_ci，在 MySQL 5.7 中则是 utf8mb4_general_ci。

utf8mb4_0900_ai_ci 中的 0900 指的是 Unicode 9.0 规范，ai 是 accent insensitivity 的缩写，指的是不区分音调，ci 是 case insensitivity 的缩写，指的是不区分大小写。

2. 公用表表达式（CTE）

CTE，简单来说，就是一个命名的临时结果集。

只需定义一次，即可多次使用。

使用 CTE 不仅让 SQL 语句变得简洁，同时也提升了 SQL 语句的可读性。

# 普通的公用表表达式
WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

# 递归公用表表达式
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

3. 窗口函数

窗口函数，也称为分析函数，可针对一组行进行计算，并为每行返回一个结果。

这一点与聚合函数不同。聚合函数只能为每个分组返回一个结果。

窗口函数中的 OVER 子句定义了所要计算行的行窗口。

看下面这个示例，都是为了实现行号，只不过在 MySQL 8.0 之前，需借助于自定义变量，而在 MySQL 8.0 中，可以直接使用 ROW_NUMBER()。

# MySQL 5.7
SET @row_number = 0;
SELECT dept_no, dept_name,  
    (@row_number:=@row_number + 1) AS row_num  
FROM departments ORDER BY dept_no;

# MySQL 8.0
SELECT dept_no, dept_name, 
    ROW_NUMBER() OVER (ORDER BY dept_no) AS row_num 
FROM departments;

4. 支持将表达式作为默认值

下面是官方文档中的一个示例。

CREATE TABLE t1 (
  # 文本默认值
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  # 表达式默认值
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

注意，表达式默认值必须放到括号内。

从 MySQL 8.0.13 开始，BLOB, TEXT, GEOMETRY 和 JSON 字段允许设置表达式默认值。例如，

CREATE TABLE t2 (b BLOB DEFAULT ('abc'));

5. 支持 CHECK 约束

看下面这个示例。

CREATE TABLE t1
(
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)),
  CONSTRAINT `t1_chk_2` CHECK ((`c3` < 100)),
  CONSTRAINT `t1_chk_3` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

6. 隐藏列

隐藏列是 MySQL 8.0.23 引入的新特性。对于隐藏列，只有显式指定才能访问。无论是在查询，还是 DML 语句中。

如果是通过 SELECT * 查询，则不会返回隐藏列的内容。

mysql> CREATE TABLE t1 (c1 INT, c2 INT INVISIBLE);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 (c1, c2) VALUES(1, 2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+
| c1   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT c1, c2 FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    3 | NULL |
+------+------+
2 rows in set (0.00 sec)

7. 不可见索引

对于冗余索引，在执行删除操作之前，可以先将它设置为不可见，然后观察一段时间，确定对业务没有影响再执行删除操作。

# 创建不可见索引
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; 
# 将索引设置为不可见
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; 
# 将索引设置为可见
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

优化器默认不会使用不可见索引。如果要使用，可设置 optimizer_switch。

SET SESSION optimizer_switch='use_invisible_indexes=on'; 
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ * FROM t1 WHERE k = 1;

8. 降序索引

对于涉及到多列，但排序顺序又不一致的排序操作，可以通过降序索引来优化。

如 ORDER BY c1 ASC, c2 DESC 这个排序操作就可以通过下面这个索引来优化。

ALTER TABLE t add INDEX idx_1 (c1 ASC, c2 DESC);

9. 函数索引

函数索引允许对表达式创建索引，在此之前，只能对列或列的前缀创建索引。

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
# 对查询列使用相同的函数，可以使用索引
mysql> EXPLAIN SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | ref  | idx1          | idx1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

10. VALUES

VALUES 是 MySQL 8.0.19 开始支持的语法，它会以表的形式返回一行或多行数据。

行数据通过 ROW() 函数来构造。函数中的元素既可以是标量值，也可以是表达式。

列名是 column_x，其中，x 是序号，从 0 开始递增。

mysql> VALUES ROW(1,now()), ROW(2,now());
+----------+---------------------+
| column_0 | column_1            |
+----------+---------------------+
|        1 | 2022-12-13 11:01:40 |
|        2 | 2022-12-13 11:01:40 |
+----------+---------------------+
2 rows in set (0.00 sec)

11. INTERSECT 和 EXCEPT

MySQL 8.0.31 开始支持 INTERSECT 和 EXCEPT，分别用来取两个集合的交集和差集。

mysql> VALUES ROW(1,2), ROW(3,4) INTERSECT VALUES ROW(1,2);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
+----------+----------+
1 row in set (0.00 sec)

mysql> VALUES ROW(1,2), ROW(3,4) EXCEPT VALUES ROW(1,2);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 |        4 |
+----------+----------+
1 row in set (0.00 sec)

12. 唯一键冲突

唯一键冲突的报错信息会输出表名，在 MySQL 5.7 中，只会输出唯一键名。

# MySQL 8.0
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'
# MySQL 5.7
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

13. 查询改写插件

查询改写插件在 MySQL 8.0.12 之前只支持 SELECT 语句，从 MySQL 8.0.12 开始支持 INSERT，REPLACE，UPDATE 和 DELETE 语句。

14. JSON

JSON 字段支持部分更新，极大提升了 JSON 字段的处理性能。

看下面这个测试结果。

JSON 的具体用法可参考：一文说透 MySQL JSON 数据类型

15. NOWAIT 和 SKIP LOCKED

SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE 语句中引入 NOWAIT 和 SKIP LOCKED 选项，用来解决电商场景热点行问题。

session1> CREATE TABLE t(id INT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

session1> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

session1> BEGIN;
Query OK, 0 rows affected (0.00 sec)

session1> SELECT * FROM t WHERE id=2 FOR UPDATE;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

# 如果需要加锁的行被其它事务锁定，指定 NOWAIT 会立即报错，不会等到锁超时
session2> SELECT * FROM t WHERE id = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

# 指定 SKIP LOCKED 则会跳过锁定行
session2> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

16. 分组排序

不再支持 GROUP BY ASC/DESC 语法。例如，

GROUP BY dept_no ASC;

如果要对分组列进行排序，需显式指定排序列，例如，

GROUP BY dept_no ORDER BY dept_no;

17. 分区表

通用的分区接口（Handler）已从代码层移除。

在 MySQL 8.0 中，如果要使用分区表，只能使用 InnoDB 存储引擎。

18. 正则表达式

在 MySQL 8.0 中，正则表达式底层库由 Henry Spencer 调整为了 International Components for Unicode (ICU)。Spencer 库的部分语法不再支持。

两者用法上的具体差异可参考：不可不知的 MySQL 升级利器及 5.7 升级到 8.0 的注意事项

19. 表上必须存在主键

引入了 sql_require_primary_key 参数可强制要求表上必须存在主键。默认为 OFF。

mysql> CREATE TABLE slowtech.t1(id INT);
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

20. 隐式主键

在 MySQL 8.0.30 中，引入了 sql_generate_invisible_primary_key 参数可为没有显式设置主键的表创建一个隐式主键。默认为 OFF。

看下面这个示例。

mysql> CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

21. Hint

引入了多个 Hint。

在 MySQL 5.7 中，Hint 只有 13 个。在 MySQL 8.0 中，则新增到了 37 个。

新增的 Hint 中，其中一个是 SET_VAR，可在语句级别调整参数的会话值。例如，

SELECT /*+ SET_VAR(max_execution_time = 1000) */ * FROM employees.employees;

mysql> CREATE TABLE t_parent (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t_child (id INT PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES t_parent(ID));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t_child VALUES(1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`slowtech`.`t_child`, CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `t_parent` (`id`))

mysql> INSERT /*+ SET_VAR(foreign_key_checks = OFF) */ INTO t_child VALUES(1,1);
Query OK, 1 row affected (0.01 sec)

收录于合集 #MySQL

原文：https://mp.weixin.qq.com/s/AZEJJE3YN2GRWADF2X7rMA

upan / cheat-sheet