玩转Mysql8
mysql5.7和mysql8.0区别
背景
-
以往的职业生涯当中,大多数都是使用mysql5.7版本,听闻mysql8.0性能不错,特意来玩玩!
-
总体来说,各个业务表存储引擎为InnoDB的mysql 5.7在使用语法上和mysql 8.0差别不大,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能。
新增功能
-
性能:
MySQL 8.0 在以下方面带来了更好的性能:读/写工作负载、IO 密集型工作负载、以及高竞争(”hot spot”热点竞争问题)工作负载。 -
NoSQL:
MySQL 从 5.7 版本开始提供 NoSQL 存储功能,目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求,而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。 -
窗口函数(Window Functions):
从 MySQL 8.0 开始,新增了一个叫窗口函数的概念,它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似,但它不会将多行查询结果合并为一行,而是将结果放回多行当中。即窗口函数不需要 GROUP BY。 -
隐藏索引:
在 MySQL 8.0 中,索引可以被“隐藏”和“显示”。当对索引进行隐藏时,它不会被查询优化器所使用。我们可以使用这个特性用于性能调试,例如我们先隐藏一个索引,然后观察其对数据库的影响。如果数据库性能有所下降,说明这个索引是有用的,然后将其“恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多余的,可以考虑删掉。 -
降序索引:
MySQL 8.0 为索引提供按降序方式进行排序的支持,在这种索引中的值也会按降序的方式进行排序。 -
通用表表达式(Common Table Expressions CTE):
在复杂的查询中使用嵌入式表时,使用 CTE 使得查询语句更清晰。 -
UTF-8 编码:
从 MySQL 8 开始,使用 utf8mb4 作为 MySQL 的默认字符集。 -
JSON:
MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。 -
可靠性:
InnoDB 现在支持表 DDL 的原子性,也就是 InnoDB 表上的 DDL 也可以实现事务完整性,要么失败回滚,要么成功提交,不至于出现 DDL 时部分成功的问题,此外还支持 crash-safe 特性,元数据存储在单个事务数据字典中。 -
高可用性(High Availability):
InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。
使用区别
-
身份验证:
caching_sha2_password是MySQL 8.0中的默认身份验证插件,替换了mysql 5.7的mysql_native_password,身份验证安全性能提升。 -
授权:
与帐户管理相关的授权语法略有差异: MySQL5.7创建用户和用户授权命令可以同时执行
MySQL8.0创建用户和用户授权的命令需要分开执行1
grant all privileges on . to 'canal'@'%' identified by '123456'
-
创建用户
1
create user 'canal'@'%' identified by '123456';
-
用户授权【给予所有权限】
1
grant all privileges on *.* to 'canal'@'%';
-
语法层面
语法使用区别总体变化很小,但是部分因为索引排序的变化,可能mysql5.7和mysql8.0查询结果的顺序不一致。
json相关函数在mysql8.0有部分方法名有差异。如JSON_MERGE替换为JSON_MERGE_PRESERVE -
UTF-8 编码:
从 MySQL 8 开始,使用 utf8mb4 作为 MySQL 的默认字符集。
CentOS7安装mysql8
安装前清理工作
-
清理原有的mysql数据库安装的mysql软件包和依赖包:
1
rpm -pa | grep mysql
结果如下:
1
2
3
4
5
6
7
8mysql80-community-release-el7-1.noarch
mysql-community-server-8.0.11-1.el7.x86_64
mysql-community-common-8.0.11-1.el7.x86_64
mysql-community-libs-8.0.11-1.el7.x86_64
mysql-community-client-8.0.11-1.el7.x86_64
#使用以下命令依次删除上面的程序
yum remove mysql-xxx-xxx- -
删除mysql的配置文件,卸载不会自动删除配置文件
首先使用如下命令查找出所用的配置文件;
1
find / -name mysql
可能的显示结果如下:
1
2
3
4
5
6
7
8/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/etc/selinux/targeted/tmp/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql
/usr/local/mysql根据需求使用以下命令 依次 对配置文件进行删除
1
rm -rf /var/lib/mysql
Mysql8.0安装 (YUM方式)
-
安装Mysql8.0 的yum资源库:
1
2
3
4cd /usr/local/
mkdir mysql
cd mysql
wget https://repo.mysql.com/mysql80-community-release-el7.rpm -
yum repo文件并更新 yum 缓存
1
rpm -ivh mysql80-community-release-el7.rpm
执行结果:
会在/etc/yum.repos.d/目录下生成repo文件mysql-community.repo mysql-community-source.repo
更新 yum 命令
1
2yum clean all
yum makecache -
使用 yum安装mysql
查看mysql yum仓库中mysql版本,使用如下命令1
yum repolist all | grep mysql
或者可以编辑 mysql repo文件
1
2
3vim /etc/yum.repos.d/mysql-community.repo
cat /etc/yum.repos.d/mysql-community.repo
#将相应版本下的enabled改成 1 即可; -
安装mysql 命令如下
1
yum install mysql-community-server
-
开启mysql 服务
在开启前最最重要的一步,防止数据库运行后,产生数据库大小写敏感无法更改的问题!1
2#(使用repo安装的mysql,生成的文件为my.cnfreoNew,修改为my.cnf即可)
vi /etc/my.cnf在[mysqlId]下增加配置
1
lower_case_table_names=1
然后ESC退出,:wq退出并保存,然后在启动服务
1
systemctl start mysqld.service
-
获取初始密码登录mysql
mysql在安装后会创建一个root@locahost账户,并且把初始的密码放到了/var/log/mysqld.log文件中;1
cat /var/log/mysqld.log | grep password
-
使用初始密码登录mysql
1
2
3mysql -u root -p #会提示输入密码
#修改初始密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; #注意位数和种类至少大+写+小写+符号+数字 -
将mysql 服务加入开机启动项,并启动mysql进程
1
2systemctl enable mysqld.service
systemctl start mysqld.service -
修改加密规则及密码
caching_sha2_password是MySQL 8.0中的默认身份验证插件,替换了mysql 5.7的mysql_native_password,身份验证安全性能提升。
修改以上问题可执行以下命令:1
2
3
4
5
6select version(); #mysql版本
show global variables like 'port'; #查看服务端口
select host,user,authentication_string from mysql.user; #查看用户表
ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; #修改密码
FLUSH PRIVILEGES; #刷新权限数据 -
重启mysql
1
2
3service mysqld start #启动服务
service mysqld stop #停止服务
service mysqld restart #重启服务
常用的一些sql集锦
-
进入mysql常用管理命令
1
2
3
4mysql -u root -p
SHOW DATABASES;
USE <数据库名>;
SHOW TABLES; -
创建用户并授权
1
2
3
4create user 'canal'@'%' identified by '123456'; #创建用户
grant all privileges on *.* to 'canal'@'%'; #授权
ALTER USER 'canal'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; #修改密码1
2
3其中有报异常:ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this
查阅了一下官方文档,原因是由于root用户没有SYSTEM_USER权限,把权限加入后即可解决:
grant system_user on *.* to 'root'; -
操作库
1
2
3
4
5
6
7
8
9
10
11
12-- 创建库
create database db1;
-- 创建库是否存在,不存在则创建
create database if not exists db1;
-- 查看所有数据库
show databases;
-- 查看某个数据库的定义信息
show create database db1;
-- 修改数据库字符信息
alter database db1 character set utf8;
-- 删除数据库
drop database db1; -
操作表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23--创建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
-- 查看表结构
desc 表名;
-- 查看创建表的SQL语句
show create table 表名;
-- 修改表名
alter table 表名 rename to 新的表名;
-- 添加一列
alter table 表名 add 列名 数据类型;
-- 删除列
alter table 表名 drop 列名;
-- 删除表
drop table 表名;
drop table if exists 表名 ; -
增删改
1
2
3
4
5
6-- 写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
-- 不写列名(所有列全部添加)
insert into 表名 values(值1,值2,...值n);
-- 插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);1
2
3
4
5
6-- 删除表中数据
delete from 表名 where 列名 = 值;
-- 删除表中所有数据
delete from 表名;
-- 删除表中所有数据(高效 先删除表,然后再创建一张一样的表。)
truncate table 表名;1
2
3
4-- 不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;
-- 带条件的修改
update 表名 set 列名 = 值 where 列名=值; -
多表关联update
1
2
3
4
5
6
7
8
9
10
11
12
13
14# 通过 INNER JOIN
UPDATE test_a a
INNER JOIN test_b b ON a.user_id = b.user_id
SET b.dept_id = a.dept_id;
#通过 LEFT JOIN
UPDATE test_a a
LEFT JOIN test_b b ON a.user_id = b.user_id
SET b.dept_id = a.dept_id;
#通过子查询
UPDATE test_b b
SET dept_id = ( SELECT dept_id FROM test_a WHERE user_id = b.user_id ); -
给某表的字段增加1s,方便触发表数据修改
1
2
3
4
5
6#加1秒
UPDATE promotion_product set CREATE_TIME=date_add(CREATE_TIME, interval 1 second)
WHERE PROMOTION_ID in (
'020512b9a0df49a48d05f92c097e2223',
'4a8053ee194d4020ab56f904863fdd0f'
);