MySQL 8.0 版本新特性

最新推荐文章于 2023-03-20 23:22:02 发布

qq_15654157

最新推荐文章于 2023-03-20 23:22:02 发布

阅读量258

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_15654157/article/details/106609028

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、账户与安全

用户创建和授权

MySQL8.0创建用户和用户搜全的命令需要分开执行

-- MySQL 8.0 创建用户 和授权
create user 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%';

-- MySQL 5.7 创建用户和授权， 可以是一条语句，也可以分开执行
grant all privileges on *.* to 'root'@'%' identified by 'root';

认证插件更新

MySQL8.0 中默认的身份认证插件是caching_sha2_password , 替代了之前的 mysql_native_password。如果想使用之前的，可以修改my.ini ， linux 修改 /etc/mysql.cnf ： default_authentication_plugin=mysql_native_password

mysql> SHOW VARIABLES LIKE 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)

密码管理

MySQL8.0开始允许限制重复使用以前的密码。

# 这样执行sql设置的变量，不仅当前进程有效，重启之后也有效
mysql> set persist password_history=6;

# password_history=3  # 密码历史记录在mysql.password_history 表
# password_reuse_interval=90   # 密码重复间隔
# password_require_current=ON   # 修改当前用户密码需要输入密码

alter user user() identified by 'root123' replace 'root'; # 修改密码需要输入密码

角色管理

create role 'write_role';  # 创建角色

grant select,insert,update,delete on test.* to 'write_role'; # 授权权限给角色

create user 'user1' identified by 'user1'; # 创建用户

grant 'write_role' to 'user1';  # 授权角色给用户

show grant for 'user1' for 'user1'; # 查询角色

show grant for 'user1' using 'write_role'; # 查询角色

revoke insert,update on test.* from 'write_role'; # 撤销权限

二、优化器索引

隐藏索引

隐藏索引不会被优化器使用，但仍然需要进行维护。

应用场景：软删除、灰度发布

create table t1 (i int,j int);
create index idx_i on t1(i);
create index idx_j on t1(j) invisible;

当使用 explain select * from t1 where j=1 查询时，是不会使用索引的

如果想要使用索引查询。可以打开优化器开关：将use_invisible_indexed 设置成on

降序索引

MySQL8.0 开始真正支持降序索引
只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引
MYSQL8.0 不再对GROUP BY 操作进行隐式排序

mysql> create table t2 (c1 int,c2 int,index idx_1(c1 asc, c2 desc));
Query OK, 0 rows affected (1.21 sec)

mysql> desc t2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| c1    | int  | YES  | MUL | NULL    |       |
| c2    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  KEY `idx_1` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

函数索引

MySQL8.0.13开始支持在索引中使用函数的值。
支持降序索引，支持JSON数据的索引。
函数索引基于虚拟列功能实现

mysql> create index fun_idx on t3 ((UPPER(a2)));
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a1` int DEFAULT NULL,
  `a2` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `fun_idx` ((upper(`a2`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

针对JSON的函数索引

mysql> create table emp(data json,index((CAST(data->>'$.name' as char(30)))));
Query OK, 0 rows affected (1.23 sec)

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `data` json DEFAULT NULL,
  KEY `functional_index` ((cast(json_unquote(json_extract(`data`,_utf8mb4'$.name')) as char(30) charset utf8mb4)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> explain select * from emp where CAST(data->>'$.name' as char(30))='abc';
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | functional_index | functional_index | 123     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

三、通用表表达式(CTE)

非递归CTE

MySQL8.0开始支持通用表表达式，即WITH子句。

递归CTE

递归CTE在查询中引用自己的定义，使用RECURSIVE表示

mysql> WITH RECURSIVE cte (n) AS
    -> (
    ->     SELECT 1
    ->     UNION ALL
    ->     SELECT n+1 FROM cte WHERE n<5
    -> ) SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

递归限制

# 这个变量设置的是cte默认执行的次数
mysql> show variables like 'cte_max%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000  |
+-------------------------+-------+
1 row in set, 1 warning (0.00 sec)


# cte的最大执行时间  ，这两个参数是为了防止递归进入死循环
mysql> show variables like 'max_exec%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

生成裴波那契数列

WITH recursive cte (id, curr, pre) AS 
(SELECT 
  1 AS id,
  1 AS curr,
  1 AS pre 
UNION
ALL 
SELECT 
  id + 1,
  IF(id < 2, 1, curr + pre),
  curr 
FROM
  cte 
WHERE id < 10) 
SELECT 
  id AS n,curr AS f 
FROM
  cte ;

四、窗口函数

MySQL8.0支持窗口函数（Window Function）,也称分析函数。窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。聚合窗口函数：SUM/AVG/COUNT/MAX/MIN等等

专用窗口函数

ROW_NUMBER() 、 RANK() 、 DENSE_RANK() 、 PERCENT_RANK()

FIRST_VALUE() 、 LAST_VALUE() 、LEAD() 、LAG()

CUME_DIST() 、NTH_VALUE() 、NTILE()

# ROW_NUMBER() 显示行号
mysql> SELECT val,row_number() OVER (order by val) as 'row_number' from numbers;
+------+------------+
| val  | row_number |
+------+------------+
|    1 |          1 |
|    1 |          2 |
|    2 |          3 |
|    2 |          4 |
|    3 |          5 |
|    4 |          6 |
|    5 |          7 |
|    6 |          8 |
|    8 |          9 |
|    9 |         10 |
+------+------------+
10 rows in set (0.00 sec)

# first_value() 第一个值
# lead 往前移位
mysql> select val,first_value(val) over (order by val) as 'first',
    -> lead(val,1) over (order by val) as 'lead'
    -> from numbers;
+------+-------+------+
| val  | first | lead |
+------+-------+------+
|    1 |     1 |    1 |
|    1 |     1 |    2 |
|    2 |     1 |    2 |
|    2 |     1 |    3 |
|    3 |     1 |    4 |
|    4 |     1 |    5 |
|    5 |     1 |    6 |
|    6 |     1 |    8 |
|    8 |     1 |    9 |
|    9 |     1 | NULL |
+------+-------+------+
10 rows in set (0.00 sec)

# NTILE(group_num)将所有记录分成group_num个组，每组序号一样
mysql> select val,ntile(4) over (order by val) as 'ntile4' from numbers;
+------+--------+
| val  | ntile4 |
+------+--------+
|    1 |      1 |
|    1 |      1 |
|    2 |      1 |
|    2 |      2 |
|    3 |      2 |
|    4 |      2 |
|    5 |      3 |
|    6 |      3 |
|    8 |      4 |
|    9 |      4 |
+------+--------+
10 rows in set (0.00 sec)

# rank 函数会把要求排序的值相同的归为一组且每组序号一样，排序不会连续
mysql> select rank() over (order by val desc) as row_num,val from numbers;
+---------+------+
| row_num | val  |
+---------+------+
|       1 |    9 |
|       2 |    8 |
|       3 |    6 |
|       4 |    5 |
|       5 |    4 |
|       6 |    3 |
|       7 |    2 |
|       7 |    2 |
|       9 |    1 |
|       9 |    1 |
+---------+------+
10 rows in set (0.00 sec)

# dense_rank排序是连续的，也会把相同的值分为一组且每组排序号一样
mysql> select dense_rank() over(order by val desc) as 'row_num' ,val from numbers;
+---------+------+
| row_num | val  |
+---------+------+
|       1 |    9 |
|       2 |    8 |
|       3 |    6 |
|       4 |    5 |
|       5 |    4 |
|       6 |    3 |
|       7 |    2 |
|       7 |    2 |
|       8 |    1 |
|       8 |    1 |
+---------+------+
10 rows in set (0.00 sec)

窗口定义：

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

mysql> SELECT year, country, product, profit,
       SUM(profit) OVER() AS total_profit,
       SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

窗口定义

五、InnoDB增强

集成数据字典

MySQL8.0删除了之前版本的元数据文件，例如.frm，.opt等；
将系统表mysql和数据字典表全部给成InnoDB存储引擎。支持原子DDL语句。
简化了IMFORMATION_SCHEMA的实现，提高了访问性能。
提供了序列化字典信息（SDI）的支持，以及ibd2sdi工具
数据字典使用上的差异，例如innodb_read_only影响所有的存储引擎；数据字典表不可见，不能直接查询和修改。

原子DDL操作

MySQL8.0开始支持原子DDL操作，其中与表相关的原子DDL只支持InnoDB存储引擎
一个原子DDL操作内容包括：更新数据字典，存储引擎层的操作，在binlog中记录DDL操作。
支持与表相关的DDL:数据库、表空间、表、索引的create、alter、drop、以及truncate table。
支持其他DDL:存储程序、触发器、视图、UDF的create、drop以及alter语句。
支持账户管理相关的DDL：用户和角色的create、alter、drop以及使用的rename,以及grant和revoke语句。

自增列持久化

MySQL8.0每次变化时将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表。

死锁检查控制

MySQL8.0(MySQL5.7.15) 增加了一个新的动态变量，用于控制系统是否执行innodb死锁检查：innodb_deadlock_detect

#死锁检查
mysql> show variables like 'innodb_deadlock_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)


#死锁超时时间
mysql> show variables like 'innodb_lock_wait%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

锁定语句选项

select * from xxx where x=1 for share 和 select * from xxx where x=1 for update中支持NOWAIT、SKIP LOCKED选项。
对于NOAWIT, 如果请求的行被其他驶入锁定时，语句立即返回。
对于SKIP LOCKED，从返回的结果集中移除被锁定的行。

其他改进功能

支持部分快速DDL, alter table ... ALGORITHM=INSTANT；
innoDB临时表使用共享的临时表空间 ibtmp1。
新增静态变量innodb_dedicated_server, 自动配置InnoDB内存参数：innodb_buffer_pool_size/innodb_log_file_size等。
新增表INFORMATION_SCHEMA.INNODB_CACHED_INDEXED,显示每个索引缓存在innodb缓冲池中的索引页数。
新增视图INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF,为innodb表空间提供相关元数据信息。
默认创建2个undo表空间，不再使用系统表空间。
支持ALTER TABLESPACE ... RENAME TO 重命名通用表空间。
支持使用innodb_directorries选项在服务器停止时将表空间文件移动到新的位置。
innodb表空间加密特性支持重做日志和撤销日志。

六、JSON增强

内联路径操作符

MySQL8.0增加了JSON操作符column->>path ，等价于：

JSON_UNQUOTE(column -> path)

JSON_UNQUOTE(JSON_EXTRACT(column,path)

# 5.7的写法
mysql> with doc(data) as (select json_object('id','3','name','TOM'))
    -> select json_unquote(data->'$.name') from doc;
+------------------------------+
| json_unquote(data->'$.name') |
+------------------------------+
| TOM                          |
+------------------------------+
1 row in set (0.00 sec)


mysql> with doc(data) as (select json_object('id','3','name','TOM'))
    -> select json_unquote(json_extract(data,'$.name')) from doc;
+-------------------------------------------+
| json_unquote(json_extract(data,'$.name')) |
+-------------------------------------------+
| TOM                                       |
+-------------------------------------------+
1 row in set (0.00 sec)

# MySQL8.0写法
mysql> with doc(data) as (select json_object('id','3','name','TOM')) select data->>'$.name' from doc;
+-----------------+
| data->>'$.name' |
+-----------------+
| TOM             |
+-----------------+
1 row in set (0.00 sec)

# 支持范围查找
mysql> select json_extract('["a","b","c","d","e"]','$[last-2 to last]');
+-----------------------------------------------------------+
| json_extract('["a","b","c","d","e"]','$[last-2 to last]') |
+-----------------------------------------------------------+
| ["c", "d", "e"]                                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

JSON聚合函数

MySQL8.0(MySQL5.7.22)增加了2个聚合函数：

JSON_ARRAYAGG(), 用于生产JSON数组

JSON_OBJECTAGG(), 用于生产JSON对象

mysql> select * from t;
+------+--------+--------+
| o_id | attr   | val    |
+------+--------+--------+
|    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(attr) as attrs from t group by o_id;
+------+---------------------+
| o_id | attrs               |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> select o_id ,json_objectagg(attr,val) as attrs from t group by o_id;
+------+---------------------------------------+
| o_id | attrs                                 |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

JSON实用函数

MySQL8.0（MySQL5.7.22）增加了JSON_PRETTY()
MySQL8.0（MySQL5.7.22）增加了JSON_STORAGE_SIZE()

MySQL8.0 增加了JSON_STORAGE_FREE()

mysql> select json_object('id','3','name','TOM');
+------------------------------------+
| json_object('id','3','name','TOM') |
+------------------------------------+
| {"id": "3", "name": "TOM"}         |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select json_pretty(json_object('id','3','name','TOM'));
+-------------------------------------------------+
| json_pretty(json_object('id','3','name','TOM')) |
+-------------------------------------------------+
| {
  "id": "3",
  "name": "TOM"
}                |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from jtable;
+--------------------------------------------+
| jcol                                       |
+--------------------------------------------+
| {"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"} |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select jcol,json_storage_size(jcol) from jtable;
+--------------------------------------------+-------------------------+
| jcol                                       | json_storage_size(jcol) |
+--------------------------------------------+-------------------------+
| {"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"} |                      44 |
+--------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select jcol,json_storage_free(jcol) from jtable;
+--------------------------------------------+-------------------------+
| jcol                                       | json_storage_free(jcol) |
+--------------------------------------------+-------------------------+
| {"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"} |                       0 |
+--------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> update jtable set jcol = json_set(jcol,"$.a",10,"$.b","wx","$.c",1);
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select jcol,json_storage_free(jcol) from jtable;
+------------------------------+-------------------------+
| jcol                         | json_storage_free(jcol) |
+------------------------------+-------------------------+
| {"a": 10, "b": "wx", "c": 1} |                      12 |
+------------------------------+-------------------------+
1 row in set (0.00 sec)

JSON合并函数

MySQL8.0(MySQL5.7.22) 增加了JSON_MERGE_PATCH() 、 JSON_MERGE_PRESERV()

MySQL8.0废弃了JSON_MERGE

mysql> select json_merge_patch('{"a":1,"b":2}','{"a":3,"c":4}');
+---------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}','{"a":3,"c":4}') |
+---------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                          |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('{"a":1,"b":2}','{"a":3,"c":4}');
+------------------------------------------------------+
| json_merge_preserve('{"a":1,"b":2}','{"a":3,"c":4}') |
+------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": 4}                        |
+------------------------------------------------------+
1 row in set (0.00 sec)

JSON表函数

MySQL8.0增加了JSON_TABLE()，将JSON数据转换为关系表。

可以将该函数的返回结果当作一个普通的表，使用SQL进行查询

SELECT
	* 
FROM
	json_table (
		'[{"a":"3"},{"a":"4"},{"b":"1"},{"a":"0"},{"a":[1,2]}]',
		"$[*]" COLUMNS (
			rowid FOR ordinality,
			ac VARCHAR ( 100 ) path "$.a" DEFAULT '999' ON error DEFAULT '111' ON empty,
			aj json path "$.a" DEFAULT '{"x":333}' ON empty,
			bx INT EXISTS path "$.b" 
		) 
	) AS tt;

qq_15654157

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8.0 版本新特性

一、账户与安全用户创建和授权MySQL8.0创建用户和用户搜全的命令需要分开执行-- MySQL 8.0 创建用户和授权create user 'root'@'%' identified by 'root';grant all privileges on *.* to 'root'@'%';-- MySQL 5.7 创建用户和授权，可以是一条语句，也可以分开执行grant all privileges on *.* to 'root'@'%' identified by 'roo
复制链接

扫一扫