您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL8.0新特性

时间:2019-09-24 10:29:22  来源:  作者:

MySQL从5.7一跃直接到8.0,本次的版本更新,在功能上主要有以下6点:

1,账户与安全

2,优化器索引

3,通用表表达式

4,窗口函数

5,InnoDB 增强

6,JSON 增强

一、账户与安全

1.用户的创建与授权
  在MySQL5.7的版本:
  > grant all privileges on *.* to '用户名'@'主机' identified by '密码';
  在MySQL8.0需要分开执行:
  >create user '用户名'@'主机' identified by '密码';
  >grant all privileges on *.* to '用户名'@'主机';
  用以前的一条命令在8.0里面创建用户,会出现sql语法错误
2.认证插件更新
  MySQL5.7默认身份插件是mysql_native_password
  MySQL8.0默认的身份插件是caching_sha2_password
  查看身份认证插件命令:show variables like 'default_authentication_plugin%';
  身份认证插件可以通过以下2中方式改变:
  
  1)系统变量default_authentication_plugin去改变,在my.ini文件的[mysqld]下面设置default_authentication_plugin=mysql_native_password即可
  2)如果希望只是某一个用户通过mysql_native_password的方式认证,可以修改数据库mysql下面的user表的字段,执行以下命令:
  >alter user '用户名'@'主机' identified width mysql_native_password by '密码';
3.密码管理
  MySQL8.0的密码管理策略有3个变量
  password_history 修改密码不允许与最近几次使用或的密码重复,默认是0,即不限制
  
  password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复,默认是0,即不限制
  password_require_current 修改密码是否需要提供当前的登录密码,默认是OFF,即不需要;如果需要,则设置成ON
  查询当前MySQL密码管理策略相关变量,使用以下命令:
  
  >show variables like 'password%';
  1)设置全局的密码管理策略,在my.ini配置文件中,设置以上3个变量的值这种设置方式,需要重启mysql服务器;某些生产环境不允许重启,MySQL8.0提供了关键字persist,持久化,执行以下命令:
  >set persist password_history=6;
  这条命令会在数据目录下生成新的配置文件(/var/lib/mysql/mysqld-auto.cnf),下次服务器重启的时候除了读取全局配置文件,还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的
  2)针对某一个用户单独设置密码管理策略
  >alter user '用户名'@'主机' password history 5;
  这样,这个用户的password_history 就被设置成了5,查看一下:
  >show user,host,Password_reuse_history from user;
  
  查看某一张的字段的所有字段,使用以下命令:
  >desc 表名;
4.角色管理
  
  角色:一组权限的集合
  
  一组权限赋予某个角色,再把某个角色赋予某个用户,那用户就拥有角色对应的权限
  
  1)创建一个角色
  >create role '角色1';
  
  2)为这个角色赋予相应权限
  >grant insert,update on *.* to '角色1';
  
  3)创建一个用户
  >create user '用户1' identified by '用户1的密码';
  
  4)为这个用户赋予角色的权限
  >grant '角色1' on *.* to '用户1';
  执行完上面4步,用户1就拥有了插入与更新的权限
  
  5)再创建1个用户
  >create user '用户2' identified by '用户2的密码';
  
  6)为这个用户赋予同样的角色
  >grant '角色1' on *.* to '用户2';
  
  执行完上面2步,用户2也用了角色1的权限,即插入与更新
  查看用户权限,执行以下命令:
  >show grants for '用户名';
  7)启用角色,设置了角色,如果不启用,用户登录的时候,依旧没有该角色的权限
  >set default role '角色名' to '用户名';
  8)如果一个用户有多个角色,使用以下命令
  >set default role all to '用户名';
  MySQL中与用户角色相关的表:mysql.default_roles、mysql.role_edges,有兴趣的朋友可以进去查看下。
  9)撤销权限
  >revoke insert,update on *.* from '角色名';

二、优化器索引

1.隐藏索引(invisible index)
  隐藏索引不会被优化器使用,但仍需要维护
  应用场景:
  1)软删除
    删除索引,在线上,如果删除错了索引,只能通过创建索引的方式将其添加回来,对于一些大的数据库而言,是比较耗性能的;为了避免删错,可以先将其设置为不可见,优化器这时候就不会使用它,但是后台仍然在维护,确定后,再删除。
  2)灰度发布
    与软删除差不多,如果想要测试一些索引的功能或者随后可能会使用到这个索引,可以先将其设置为隐藏索引,对于现有的查询不会产生影响,测试后,确定需要该索引,可以将其设置成可见索引。
  创建隐藏索引,执行如下命令(如果是不隐藏,则不需要后面的invisible关键字):
  >create index 索引名称 on 表名(字段名) invisible;
  查询某一张表的索引,执行如下命令:
  >show index from 表名;
  使用explain语句查看查询优化器对索引的使用情况
  >explain select * from 表名 where 条件;
  查询优化器有很多开关,有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用),将其设置成on,即可使用隐藏索引。查看当前查询优化器的所有开关变脸,执行如下命令:
  >select @@optimizer_switch;
  设置已经存在的索引为可见或者隐藏,执行如下命令:
  >alter table 表名 alter index 索引名 visible;
  >alter table 表名 alter index 索引名 invisible;
  主键不可以设置为隐藏所以。
2.降序索引(descending index)
  MySQL8.0开始真正支持降序索引,只有InnoDB引擎支持降序所以,且必须是BTREE降序索引,MySQL8.0不在对group by操作进行隐式排序。
3.函数索引
  索引中使用函数表达式
  
  支持JSON数据节点的索引
  函数索引是基于虚拟列的功能实现的
假设用户表(tb_user)的的用户登录账号(username)不需要区分大小写,则可以创建一个函数索引
>create index username_upper_index on tb_user((upper(username)));
这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。
上面的函数索引,也可以通过MySQL5.7已有的虚拟计算列来模拟,为用户表(tb_user)创建新的一列(new_column),这一列是计算列,不需要赋值,它的值就是username的大写。
>alter tbale tb_user add column new_column varchar(10) generated always as (upper(username));
然后给new_column创建一个索引,可以达到模拟MySQL8.0中的函数索引的效果。

三、通用表表达式

1.非递归 CTE
  派生表:select * from (select 1) as dt;
  通用表表达式:with cte as (select 1) select * from cte;
  
         with cte1(id) as (select 1),cte2 as (select id+1 from cte1) select * from cte1 join cte2;
2.递归 CTE

四、窗口函数

五、InnoDB增强

1.集成数据字段
2.原子ddl操作
  MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在,t2不存在,会提示t2表不存在,但是t1表仍然会被删除。
  MySQL8.0执行同样的drop命令,会提示t2表不存在,而且t1表不会被删除,保证了原子性。
  ddl操作(针对表)的原子性前提是该表使用的存储引擎是InnoDB
3.自增列持久化
  解决了之前的版本,主键重复的问题。
  MySQL5.7及其以前的版本,MySQL服务器重启,会重新扫描表的主键最大值,如果之前已经删除过id=100的数据,但是表中当前记录的最大值如果是99,那么经过扫描,下一条记录的id是100,而不是101。
  
  MySQL8.0则是每次在变化的时候,都会将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。
4.死锁检查控制
5.锁定语句选项

六、JSON增强

1.内联路径操作符 
  
column->>path
等价于之前的:
JSON_UNQUOTE(column -> path)
JSON_UNQUOTE(JSON_EXTRACT(column,path))
2.JSON聚合函数
MySQL8.0和MySQL5.7.22增加了2个聚合函数
  1)JSON_ARRAYAGG(),将多行数据组合成json数组
MySQL8.0新特性

 

  示例:select o_id,json_arrayagg(attribute) as attributes from t group by o_id;
   
MySQL8.0新特性

 

  2)JSON_OBJECTAGG(),用于生成json对象
    
  示例:select o_id json_objectagg(attribute,value) as attributes from t group by o_id;
  
MySQL8.0新特性

 

注意:json的聚合函数针对重复key,会使用最后的覆盖前面已有的值,如果下面的o_id=3,它的color有2个值,一个green,一个yellow,使用生成json的聚合函数的时候,前面的green会被覆盖掉。
MySQL8.0新特性

 


MySQL8.0新特性

 

3.JSON实用函数
  1)JSON_PRETTY() 输出json数据的时候,格式化。
  select json_object('id',3,'name','Barney');
MySQL8.0新特性

 

  select json_pretty(json_object('id',3,'name','Barney'));
  
MySQL8.0新特性

 

  2)JSON_STORAGE_SIZE() json数据所占用的存储空间(单位:字节)
  3)JSON_STORAGE_FREE() json数据更新后所释放的空间(单位:字节)
4.JSON合并函数
MySQL8.0废弃了JSON_MERGE()函数,推荐使用以下两个函数合并JSON数据
  1)JSON_MERGE_PATCH()
  2)JSON_MERGE_PRESERV()
上面两个函数都是JSON数据合并,最大的区别就是前者遇到相同key的时候会用后面的覆盖前面的,后者会都保留,看下面的截图:
MySQL8.0新特性

 

5.JSON表函数
  
  MySQL8.0新增了JSON_TABLE()函数,将JSON数据转换成关系表,可以将该函数的返回结果当做一个普通的临时表进行sql查询


Tags:MySQL8.0   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
前言:很早就听说 MySQL8.0 支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下 MySQL8.0 快速加列到底要如何操作...【详细内容】
2020-09-11  Tags: MySQL8.0  点击:(120)  评论:(0)  加入收藏
最近升级了MySQL8.0.20版本发现一个很神奇的现象: 字段只要是integer类型的,不管你设置成多少,保存之后都会变成0...这就很诡异了,工具问题?然后去mysql中直接修改,也是如此。没办...【详细内容】
2020-07-17  Tags: MySQL8.0  点击:(1223)  评论:(0)  加入收藏
前言:不知不觉,MySQL8.0已经有好多个GA小版本了。目前互联网上也有很多关于MySQL8.0的内容了,MySQL8.0版本基本已到稳定期,相信很多小伙伴已经在接触8.0了。本篇文章主要介绍从5...【详细内容】
2020-05-22  Tags: MySQL8.0  点击:(60)  评论:(0)  加入收藏
操作系统环境:CentOS Linux release 7.7.1908 (Core) 基础环境配置关闭SeLinux及防火墙sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/configsetenforce 0...【详细内容】
2020-03-28  Tags: MySQL8.0  点击:(67)  评论:(0)  加入收藏
双mysql,只是在节约成本的前提下,考虑使用的,更多的是在开发测试环境使用,若是生产环境,尽量不建议如此使用。若是生产环境,森哥不建议双MySQL共存。而且,尽量建议使用提供商提供的...【详细内容】
2020-03-08  Tags: MySQL8.0  点击:(87)  评论:(0)  加入收藏
MySQL从5.7一跃直接到8.0,本次的版本更新,在功能上主要有以下6点:1,账户与安全2,优化器索引3,通用表表达式4,窗口函数5,InnoDB 增强6,JSON 增强一、账户与安全1.用户的创建与授权  ...【详细内容】
2019-09-24  Tags: MySQL8.0  点击:(293)  评论:(0)  加入收藏
一、安装前准备1、在官网下载MySQL安装包(注意下载的安装包类型) 2、查看是否安装mariadb# rpm -qa | grep mariadb 3、卸载mariadb# rpm -e --nodeps mariadb-libs-5.5.6...【详细内容】
2019-06-26  Tags: MySQL8.0  点击:(407)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条