玩转Mysql8

发表于 更新于 分类于 阅读次数： 本文字数： 5.9k 阅读时长 ≈ 5 分钟

mysql5.7和mysql8.0区别

背景

  1. 以往的职业生涯当中，大多数都是使用mysql5.7版本，听闻mysql8.0性能不错，特意来玩玩！

  2. 总体来说，各个业务表存储引擎为InnoDB的mysql 5.7在使用语法上和mysql 8.0差别不大，官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，还带来了大量的改进和更快的性能。

新增功能

  1. 性能：
    MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争(”hot spot”热点竞争问题)工作负载。

  2. NoSQL：
    MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

  3. 窗口函数(Window Functions)：
    从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY。

  4. 隐藏索引：
    在 MySQL 8.0 中，索引可以被“隐藏”和“显示”。当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。

  5. 降序索引：
    MySQL 8.0 为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序。

  6. 通用表表达式(Common Table Expressions CTE)：
    在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰。

  7. UTF-8 编码：
    从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。

  8. JSON：
    MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

  9. 可靠性：
    InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中。

  10. 高可用性(High Availability)：
    InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。

使用区别

  1. 身份验证：
    caching_sha2_password是MySQL 8.0中的默认身份验证插件，替换了mysql 5.7的mysql_native_password，身份验证安全性能提升。

  2. 授权：
    与帐户管理相关的授权语法略有差异： MySQL5.7创建用户和用户授权命令可以同时执行
    MySQL8.0创建用户和用户授权的命令需要分开执行

    1
    grant all privileges on . to 'canal'@'%' identified by '123456'

  3. 创建用户

    1
    create user 'canal'@'%' identified by '123456';

  4. 用户授权【给予所有权限】

    1
    grant all privileges on *.* to 'canal'@'%';

  5. 语法层面
    语法使用区别总体变化很小，但是部分因为索引排序的变化，可能mysql5.7和mysql8.0查询结果的顺序不一致。
    json相关函数在mysql8.0有部分方法名有差异。如JSON_MERGE替换为JSON_MERGE_PRESERVE

  6. UTF-8 编码：
    从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。

CentOS7安装mysql8

安装前清理工作

  1. 清理原有的mysql数据库安装的mysql软件包和依赖包：

    1
    rpm -pa | grep mysql

    结果如下：

    1
    2
    3
    4
    5
    6
    7
    8
    mysql80-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-

  2. 删除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方式)

  1. 安装Mysql8.0 的yum资源库：

    1
    2
    3
    4
    cd /usr/local/
    mkdir mysql
    cd mysql
    wget https://repo.mysql.com/mysql80-community-release-el7.rpm

  2. yum repo文件并更新 yum 缓存

    1
    rpm -ivh mysql80-community-release-el7.rpm

    执行结果：
    会在/etc/yum.repos.d/目录下生成repo文件mysql-community.repo mysql-community-source.repo
    yum repo文件

    更新 yum 命令

    1
    2
    yum clean all
    yum makecache

  3. 使用 yum安装mysql
    查看mysql yum仓库中mysql版本，使用如下命令

    1
    yum repolist all | grep mysql

    查看mysql yum仓库中mysql版本

    或者可以编辑 mysql repo文件

    1
    2
    3
    vim /etc/yum.repos.d/mysql-community.repo
    cat /etc/yum.repos.d/mysql-community.repo 
    #将相应版本下的enabled改成 1 即可；

  4. 安装mysql 命令如下

    1
    yum install mysql-community-server

    mysql8的安装

  5. 开启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

  6. 获取初始密码登录mysql
    mysql在安装后会创建一个root@locahost账户，并且把初始的密码放到了/var/log/mysqld.log文件中；

    1
    cat /var/log/mysqld.log | grep password

  7. 使用初始密码登录mysql

    1
    2
    3
    mysql -u root -p  #会提示输入密码
    #修改初始密码：
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; #注意位数和种类至少大+写+小写+符号+数字

  8. 将mysql 服务加入开机启动项，并启动mysql进程

    1
    2
    systemctl enable mysqld.service
    systemctl start mysqld.service

  9. 修改加密规则及密码

    caching_sha2_password是MySQL 8.0中的默认身份验证插件，替换了mysql 5.7的mysql_native_password，身份验证安全性能提升。
    身份验证
    修改以上问题可执行以下命令：

    1
    2
    3
    4
    5
    6
    select 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; #刷新权限数据

  10. 重启mysql

    1
    2
    3
    service mysqld start #启动服务
    service mysqld stop #停止服务
    service mysqld restart #重启服务

常用的一些sql集锦

  1. 进入mysql常用管理命令

    1
    2
    3
    4
    mysql -u root -p
    SHOW DATABASES;
    USE <数据库名>;
    SHOW TABLES;

  2. 创建用户并授权

    1
    2
    3
    4
    create 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';

  3. 操作库

    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;

  4. 操作表

    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 表名 ;

  5. 增删改

    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 列名=值;

  6. 多表关联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 );

  7. 给某表的字段增加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'
    );