MYSQL 8.0-新特性汇总（一）

数据字典

数据字典可以通过INFORMATION_SCHEMA表和SHOW语句进行访问

数据字典和系统表的区别

• 数据字典包含执行SQL所需要的元数据
• 系统表包含时区和帮助信息等辅助数据

使用数据字典的好处

• 集中式数据字典模式的简单性，统一存储字典数据。

• 移除基于文件的元数据存储

在8.0之前的版本中，字典数据部分存储在元数据文件中。这就避免了操作文件时，操作的复杂性和资源占用，还有就是方便了新功能和添加元数据
具体删除的文件有以下几个：
.frm,.par,.TRN,.TRG,.isl等文件

• 事务性的，防崩溃的字典数据存储

数据字典表创建了一个名叫mysql.ibd的INNODB表空间，保存在在MYSQL数据目录下

• 统一和几种缓存字典对象

• 对某些INFORMATION_SCHEMA表的更简单和改进的实现

• 原子DDL

原子DDL

原子DDL语句，在执行DDL语句的时候，要么全部执行，要么都回滚。DDL语句的操作相关的数据字典更新，存储引擎操作和二进制日志写入等都是原子操作。
目前只有INNODB存储引擎支持原子DDL，执行日志存储在mysql.innodb_ddl_log表中

但是要注意的一点是：原子DDL并不是事务DDL。这是什么意思呢？
这意味着DDL只能是单独一个SQL执行，并不能像事务DDL一样有在开启事务和提交事务操作中间有多种SQL操作

原子DDL支持的语句

• 支持的表相关的DDL不仅包含数据库，表空间，表索引的CREATE,ALTER,DROP语句，还支持TRUNCATE表的语句
• 对于存储过程，试图和函数等CREATE,DROP,ALTER语句也是支持的
• 对于MYSQL账户的GRANT授权，REVOKE撤销授权，以及账户及角色的RENAME操作语句也是允许的

DDL语句执行的变动

• create table ....select在MYSQL8.0.21版本之前是作为两个事务的，一个事务是新建表的，另一个是插入数据。而之后的版本是一个操作

字符集的支持

默认的字符集从latin1变成了utf8mb4，同时增加了几种新的字符集的排序规则和区别属性

字符名称	字节数量	备注
utf8mb4	一个字符4个字节
utf8mb3	一个字符3个字节	8.0版本已经被废弃
utf8		之前是utf8mb3的别名，后面会指utf8mb4的别名
ucs2	2字节	MySQL 8.0.28中已弃用
utf16	2个或4个字节一个字符

JSON 函数的增强

增加了很多函数和操作符号
具体详细内容在另一篇博客进行增加，会在后续使用中进行补充：https://www.cnblogs.com/perfectLi/p/16016644.html

JSON 函数增加的内容有以下部分

1.增加了行路径操作符->>

首先我们先了解一下JSON_EXRACT函数

JSON_EXTRACT(json_doc, path[, path] ...)
json_doc是json数据，path是要获取json串中那个位置的数据，可以有多个参数
返回的是获取到的数据类型，如果多个参数，返回的是一个包含查询数据的数组

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+

• JSON_EXTRACT的别名->（MYSQL5.7）就已经存在

这种用法不止可以用在select语句，也可以用到

mysql>  select
    >      JSON_EXTRACT(tb.c,'$[1]') ,
    >      tb.c->"$[1]"
    >   from ( select '[10, 20, [30, 40]]' as c )as tb;
+-----------------------------------------------+-----------------------------------------------+
| JSON_EXTRACT(tb.c,'$[1]')                     |   tb.c->"$[1]"                                | 
+-----------------------------------------------+-----------------------------------------------+
| 20                                            |      20                                       | 
+-----------------------------------------------+-----------------------------------------------+

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+

另一个函数JSON_UNIQUE

取消引号，并返回一个utf8mb4的字符串

mysql> SET @j = '"abc"';
mysql> SELECT @j, JSON_UNQUOTE(@j);
+-------+------------------+
| @j    | JSON_UNQUOTE(@j) |
+-------+------------------+
| "abc" | abc              |
+-------+------------------+

->>操作符就是对JSON_UNQUOTE( JSON_EXTRACT(column, path) )写法的简化

2.增加JSON聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()

JSON_ARRAYAGG(col_or_expr) [over_clause]

根据Group by 聚合条件，将需要聚合列聚合成一个数组，如果不存在聚合项则返回null

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

JSON_OBJECTAGG(key, value) [over_clause]

两个参数，第一个参数是key,第二个参数是value。返回的结构包含key-value的结构
如果参数不是两个会报错

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
    -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

不允许出现多个相同key,如果同一个key列有多行，则value值取最后一行数据
如果要多个相同key,多行展示，那么就要结合窗口函数了

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+

3.增加了一个JSON工具函数JSON_PRETTY

可以让JSON更加好阅读

4.增加了对JSON的数据进行编辑

8.0版本只要要对JSON进行操作，一般无法直接修改JSON数据，只能进行应用层操作后对数据库的JSON数据进行替换

JSON_SET(), JSON_REPLACE(), or JSON_REMOVE()

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
+-------------------------------------------------+
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+

5.新增JSON工具函数``` JSON_STORAGE_SIZE() ````和```JSON_STORAGE_FREE()```

JSON_STORAGE_SIZE()返回的是JSON的字节数量
JSON_STORAGE_FREE()默认是0，返回的是 JSON_SET() or JSON_REPLACE()使用后释放的字节数量

6.增加范围查询表达式

增加了 [startIndex to endIndex]的表达式

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

增加last关键字，表示最右边的元素

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

6.JSON合并函数JSON_MERGE_PRESAVE

用于将多个JSON数据合并成一个

7.JSON对象串中如果有多个相同key，则只有最右边的会被保存

8.增加JSON_TABLE函数，作用是将一个JSON串转成一个关系型表

语法结构如下

JSON_TABLE(
    expr, //JSON串
    path COLUMNS (column_list) // 定义的映射列
)   [AS] alias // 转关系表后的别名

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty: // 数据缺失的时设置值
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error: // 数据报错时设置值
    {NULL | DEFAULT json_string | ERROR} ON ERROR

举个栗子

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)

数据类型的支持

可以给TXT,BLOB,JSON等类型通过表达式设置默认值
需要注意的是：表达式默认值不能包含对生成的列或具有表达式默认值的列的前向引用

- 这种形式的就没有问题
CREATE TABLE t2 (b BLOB DEFAULT ('abc'));

- 这样就会报无法定义的错误
CREATE TABLE t2 (b BLOB DEFAULT 'abc');

官方文档地址： https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html