MySQL 8.0 使用注意事项、新特性

group by 不再隐式排序

• MySQL 8.0 中 GROUP BY col_name ASC/DESC 语法废弃，且默认 GROUP BY 不排序
• MYSQL 5.7 中支持 GROUP BY col_name ASC/DESC 语法，且默认GROUP BY增序排列
• 如果需要分组并对结果进行排序，改为 GROUP BY col_name ORDER BY col_name [ASC/DESC]（默认增序）
• 如果仅分组，不对结果排序，直接使用 GROUP BY col_name
• 支持函数在GROUP BY子句中的使用：SELECT YEAR(date_col), COUNT(*) FROM table1 GROUP BY YEAR(date_col);

新增大量保留关键字

• 在做 DML 操作时，SQL语句中需要用 ``（反引号）括起来，否则将报语法错误
• 具体关键字可在网上查阅

INSERT 语句中插入 NULL 值替换 \N

• MySQL 8.0废弃将\N解析为NULL，若SQL语句中使用\N，将报语法错误
• 插入NULL值使用NULL，而不是\N
• 示例：insert into test_null values(1,\N); 改为 insert into test_null values(1,NULL);

LIMIT 语句优化

• 支持OFFSET子句中使用参数：SELECT * FROM table1 LIMIT 10 OFFSET ?;
• 支持FETCH语法：SELECT * FROM table1 ORDER BY id FETCH FIRST 10 ROWS ONLY OFFSET 20;
• 支持使用变量作为LIMIT和OFFSET的值：

SET @limit = 10;
SET @offset = 20;
SELECT * FROM table1 LIMIT @limit OFFSET @offset;

RENAME USER 语句优化

• 支持更改用户名和主机名（MySQL 5.7只支持更改用户名）：
• 支持更改默认角色：
• 支持更改密码：

RENAME USER 'user1'@'localhost' TO 'user2'@'127.0.0.1';
RENAME USER 'user1'@'localhost' TO 'user1'@'localhost' WITH DEFAULT ROLE 'role1';
RENAME USER 'user1'@'localhost' IDENTIFIED BY 'new_password';

新特性

隐藏索引

• 在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
• 从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

# 修改索引不可见
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
# 修改索引可见
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

CTE（Common Table Expression）

• CTE 是派生表(derived table)的替代，在一定程度上，CTE简化了复杂的join查询和子查询，另外CTE可以很方便地实现递归查询，提高了SQL的可读性和执行性能。是一种在查询中创建临时表的SQL语法。
• 查询语句的可读性更好
• 能够有效地替代视图
• 能够提高SQL执行性能
• 能够链接多个CTE
• 能够创建递归查询
• 在一个查询中，可以被引用多次

# 查询所有大于平均年龄的人的姓名和年龄
# 子查询实现
SELECT name, age FROM table1 WHERE age > (SELECT AVG(age) FROM table1);
# CTE 实现
WITH cte AS (SELECT AVG(age) as avg_age FROM table1) SELECT name, age FROM table1, cte WHERE age > cte.avg_age;

降序排序

• 从语法上，MySQL 4就支持了，但正如官方文档所言，“they are parsed but ignored”，实际创建的还是升序索引。
• 如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引。

# 可在 5.7 和 8.0 进行对比
create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
show create table slowtech.t1\G

https://cloud.tencent.com/developer/article/1810298

GIS（地理）数据类型

• GIS Functions是一组处理地理信息数据的函数，可以用于计算距离、面积、交集、并集等

JSON 查询增强

• MySQL 5.7中的JSON数据类型只支持存储和查询JSON文本，而不支持索引和查询JSON对象中的属性。MySQL 8.0可实现更高级的JSON操作

SELECT data->>'$.name' as name, data->>'$.age' as age FROM table1;

参考：https://www.modb.pro/db/619618