MySQL系列之MySQL8.0新特性

johnny233

已于 2022-11-11 16:19:34 修改

阅读量666

点赞数 1

分类专栏：数据库文章标签： mysql

于 2021-09-27 22:09:32 首次发布

本文链接：https://blog.csdn.net/lonelymanontheway/article/details/120008389

版权

数据库专栏收录该内容

43 篇文章 3 订阅

订阅专栏

概述

本文非原创，整理网络资源学习备用。

MySQL 8.0 正式版 8.0.11 已发布，官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，还带来了大量的改进和更快的性能！

注意：从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级，不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升级之前对数据进行备份。

新特性

隐藏索引

隐藏索引的特性对于性能调试非常有用。在 8.0 中，索引可以被隐藏和显示。当一个索引隐藏时，它不会被查询优化器所使用。
即，可以隐藏一个索引然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其恢复显示即可；如果数据库性能看不出变化，说明索引是多余的，可删除。
隐藏索引的语法：ALTER TABLE t ALTER INDEX i INVISIBLE;
恢复显示该索引的语法：ALTER TABLE t ALTER INDEX i VISIBLE;

当一个索引被隐藏时，可从show index命令输出中看到，该索引Visible 属性值为 NO。当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。如果你长期隐藏一个索引，那还不如干脆删掉，因为索引的存在会影响插入、更新和删除的性能。

CTE

通用表表达式，Common Table Expressions，复杂的查询会使用嵌入式表：

SELECT t1.*, t2.* FROM 
  (SELECT col1 FROM table1) t1,
  (SELECT col2 FROM table2) t2;

基于CTE的简化写法：

WITH
  t1 AS (SELECT col1 FROM table1),
  t2 AS (SELECT col2 FROM table2)SELECT t1.*, t2.* 
FROM t1, t2;

这样看上去层次和区域都更加分明，改起来也更清晰的知道要改哪一部分。
CTE官方文档

递归查询

在企业应用开发中，经常会遇到层级表设计业务。参考- MySQL+Java实现父子级联查询
在MySQL 8以下版本，对于这种层级查询，只能通过设置@用户变量来实现，或在业务层代码（如Java）实现。

MySQL 8版本通过CTE支持递归查询，即递归CTE。

递归公用表表达式(CTE)是一个具有引用CTE名称本身的子查询的CTE。语法：

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

递归CTE由三个主要部分组成：

形成CTE结构的基本结果集的初始查询(initial_query)，初始查询部分被称为锚成员。
递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连。
终止条件是当递归成员没有返回任何行时，确保递归停止。

递归CTE的执行顺序：

将成员分为两个：锚点和递归成员
执行锚成员形成基本结果集(R0)，并使用该基本结果集进行下一次迭代
将Ri结果集作为输入执行递归成员，并将Ri+1作为输出
重复第三步，直到递归成员返回一个空结果集，满足终止条件
使用UNION ALL运算符将结果集从R0到Rn组合

递归成员不能包含以下结构：

聚合函数，如MAX，MIN，SUM，AVG，COUNT等
GROUP BY子句
ORDER BY子句
LIMIT子句
DISTINCT

上述约束不适用于锚定成员。只有在使用UNION运算符时，要禁止DISTINCT才适用。如果使用UNION DISTINCT运算符，则允许使用DISTINCT。
递归成员只能在其子句中引用CTE名称，而不是引用任何子查询。

参考：官方文档

窗口函数

Window Functions，也叫开窗函数，MySQL 8 以前缺少rank()等开窗函数，当需要在查询当中实现排名时，必须手写 @ 变量。

推荐阅读：
SQL开窗函数
 Hive系列之开窗函数

窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。即，窗口函数是不需要 GROUP BY 的。

窗口函数官方文档

Instant Add Column

ALGORITHM=INSTANT 目前对6种ddl有效：

Adding a column. This feature is referred to as Instant Add Column . 添加列
Adding or dropping a virtual column. 添加或删除virtual 列
Adding or dropping a column default value. 添加或删除列默认值
Modifying the definition of an ENUM. 修改 ENUM 定义
Changing the index type. 修改索引类型
Renaming a table. 重命名表

调整

UTF-8编码

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，包含所有 emoji 字符。

设置持久化

MySQL设置可以在运行时通过SET GLOBAL命令来来时生效，下次启动时数据库又会从配置文件中读取。
新增SET PERSIST 命令：SET PERSIST max_connections = 500;
MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

优化

JSON

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/json.html
https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

MySQL 5.7开始提供NoSQL存储的功能。
MySQL 8 大幅改进对 JSON 的支持，添加基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

概述

不允许为null
JSON格式定义与LONGBLOB or LONGTEXT类似，最大长度受max_allowed_packet控制
查看JSON字段所占用空间大小的函数JSON_STORAGE_SIZE(xxx)
除普通的JSON操作，额外支持GeoJSON (基于几何图形的针对地理空间数据交换格式)一些相关操作
对JSON栏位支持索引（结合Mysql8.0新特性，函数index）
一个可以支持部分的，原地更新Json Column 的可选优化项加入MySql8.0
可以使用的函数有JSON_SET(), JSON_REPLACE() ,JSON_REMOVE()
使用时，有一些约束，但是会有更加的性能

新增

JSON_PRETTY：接受JSON数据类型或JSON的字符串表示形式，并以新的行和缩进方式以人类可读的方式返回JSON格式的字符串。
JSON_STORAGE_SIZE：

实例

-- JSON_ARRAY定义JSON数组
SELECT JSON_ARRAY(1, 'abc', NULL, TRUE, CURTIME());
-- [1, "abc", null, true, "11:30:24.000000"]

-- JSON_OBJECT定义JSON对象
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
-- {"id": 87, "name": "carrot"}

-- 数组与对象嵌套的场景
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

-- 日期/时间类型定义
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

-- JSON_QUOTE 将JSON对象转义成String, 将内部的符号进行转义，并整体包裹上双引号；
JSON_QUOTE(' "null" ');
-- "\"null\""

-- 将JSON内容美化并输出
JSON_PRETTY();

-- 可以将JSON/JSON内部的元素转化为其他数据类型;
-- 将JSON jdoc 中的id元素转化为 unsigned int;
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED);