mysql精讲高级篇

第01章_Linux下MySQL的安装与使用

1. 安装前说明

1.1 Linux系统及工具的准备

• 安装并启动好两台虚拟机CentOS7

  • 掌握克隆虚拟机的操作

    • mac地址

    • 主机名

      • 虚拟机centos7修改主机名

      • 第一种方法

      执行命令：hostnamectl set-hostname newname，newname就是你想改的名字

      • 第二种方法

      准备工作
      
      首先，打开终端
      
      输入 su root 进入根用户账号并输入密码
      
      输入 hostname 可查看当前主机名
      
      修改 /etc/sysconfig/network 文件
      
      输入 vi /etc/sysconfig/network 
      
      进入vi界面 输入i进入编辑模式，输入以下命令(HOSTNAME后面为想改的主机名)：
      
      NETWORKING=yes
      
      HOSTNAME=hadoopmaster
      
      按esc退出编辑模式，然后输入 :wq 保存并退出
      
      修改/etc/hosts 文件
      
      输入命令：
      
      vi /etc/hosts
      
      进入vi界面 输入i进入编辑模式，修改主机名
      
      将文件中原本的主机名（与前面hostname命令查看的一致）修改为自己的主机名
      
      按esc退出编辑模式，然后输入 :wq 保存并退出
      
      修改hostname文件
      
      输入命令 vi /etc/hostname 进入文件，将里面的主机名改为修改后的主机名
      
      reboot重启虚拟机
      
      

    • ip地址

    • UUID

• 安装有Xshell 和Xftp等访问CentOS系统的工具

• CentOS6和CentOS7在MySQL使用的区别

  1.防火墙:6是iptables,7是firewalld

  2.启动服务命令：6是service，7是systemctl

1.2查看是否安装过MySQL

• 如果你是用rpm安装，检查一下rpm package

  rpm -qa | grep -i mysql # -i忽略大小写
  

• 检查mysql service：

  systemctl status mysqld.service
  

• 如果存在mysql-libs的旧包，显示如下

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eXpc6VGX-1670972022128)(image/1669435783697.png)]

• 如果不存在mysql-lib的版本，显示如下：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s0Iw6maX-1670972022129)(image/1669435885900.png)]

1.3 MySQL的卸载

1.关闭mysql服务

systemctl stop mysqld.service

2.查看当前mysql安装情况

rpm -qa | grep -i mysql
# 或
yum list installied | grep mysql

3.卸载上述命令查询出的已安装程序

yum remove mysql-xxx mysql xxx mysql -xxx

务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留

4.删除MySQL相关文件

• 查找相关文件

  find / -name mysql

• 删除上述命令查出的相关文件

  rm -rf xxx

5.删除my.cnf

rm -rf /etc/my.cnf

2.MySQL的Linux版安装

2.1MySQL的4大版本

• MySQL community server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
• MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以使用30天。提供了更多的功能和完善的技术支持，更适合于数据库的功能和可靠性要求较高的企业客户。
• MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个server。需要在社区版或企业版的基础上使用。
• MySQL Cluster CGE 高级集群版，需付费。

• 截至目前，官方的最新版本为8.0.31。此前，8.0.0在2016.9.12日就发布了。
• 本课程主要使用8.0.25版本。同时为了更好说明MySQL8.0新特性，还安装MySQL5.7版本,作为对比。

此外，官方 还提供了MySQL WorkBench（GUITOOL）一款专门为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。MySQLWorkBench又分为两个版本，分别是社区版（MySQL WorkBench OSS）、商用版（MySQL WorkBenchSE）。

2.2下载MySQL指定版本

1.下载地址：

官网：https://www.mysql.com

2.打开官网，点击downloads

然后，点击MySQL community（GPL）DownLoads

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fc5WCkXY-1670972022130)(image/1669443300681.png)]

3.点击MySQL community Server

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nwhcALBw-1670972022130)(image/1669443381788.png)]

4.在General Avaliability(GA) Releases 中选择合适的版本

• 如果在windows系统下MySQL，推荐下载MSI安装程序；点击Go to Download Page进行下载即可

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C5rUkHJY-1670972022130)(assets/1669443617716.png)]

  • windows下的MySQL安装 有两种安装程序

    • mysql-installer-web-community-8.0.25.msi下载程序大小：2.4M；安装时需要联网安装组件；
    • mysql-installer-community-8.0.25.msi下载程序大小：435.7M；安装时离线安装即可。推荐

  • Linux下的MySQL安装（CentOS7）

    • 选择Red Hat Enterprise Linux/Oracle Linux，选择对应相应的系统对应的文件

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sudrimXS-1670972022131)(image/1669443992011.png)]

    • 选择第一个离线安装即可（RPM Bundle） 大小在：864.1M

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fZPau1wo-1670972022131)(image/1669444075120.png)]

5.Linux系统安装MySQL的几种方式

5.1 Linux系统安装软件的常用三种方式：

方式一：rpm命令

使用rpm命令安装扩展名为.rpm的软件包。

.rpm包的一般格式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SInlhLWl-1670972022131)(image/1669444300447.png)]

方式二 ：yum命令

需联网，从互联网获取的yum源，直接使用yum命令安装。

方式三: 编译安装源码包

针对tar.gz这样的压缩格式，要用tar命令来解压；如果是其他压缩格式，就是用其他命令。

5.2 Linux系统下安装MySQL，官方给出多种安装方式

安装方式	特点
rpm	安装简单，灵活性差，无法灵活选择版本、升级
rpm repository	安装包极小，版本安装简单灵活，升级方便，需要联网安装
通用二进制包	安装比较复杂，灵活性高，平台通用性好
源码包	安装最复杂，时间长，参数设置灵活，性能好

• 这里不能直接选择CentOS 7 系统的版本，所以选择与之对应的Red Hat Enterprise Linux
• https://downloads.mysql.com/archives/community/直接点download下载RPM Bundle全量包。包括了所有下面的组件。不需要一个一下载了。

6.下载的tar包，用压缩工具打开

• 解压后rpm安装包（红框为抽出来的安装包）

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cOQVuZDJ-1670972022132)(image/1669444986820.png)]

2.3 CentOS 7 下检查MySQL依赖

1.检查/tmp临时目录权限（必不可少）

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行：

chmod -R 777 /tmp

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jCYgVZU2-1670972022132)(image/1669445175157.png)]

2.安装前，检查依赖

rpm -qa | grep libaio

• 如果存在llibaio包如下

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BUk2GdjF-1670972022133)(image/1669445326630.png)]

• 如果存在net-tools包如下：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CYMumAiw-1670972022133)(image/1669445365759.png)]

rpm -qa | grep net-tools

• 如果不存在需要到CentOS 安装盘里面进行rpm安装。安装Linux如果带图形化界面，这些都是安装好的。

2.4CentOS 7 下MySQL安装过程

1.将安装程序拷贝到/opt目录下

在mysql的安装文件目录下执行：（必须按照顺序执行）；

rpm -ivh mysql-community-common-8..25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8..25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.8.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.e17.x86_64.rpm

• 注意：如果在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错
• rpm安装Redhat package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
• -i，–install 安装软件包
• -v,–verbose提供更多的详细信息输出
• -h，–hash 软件包安装的时候列出哈希标记（和-v一起使用效果更好），展示进度条

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ybNfaMkt-1670972022133)(image/1669446376225.png)]

2安装过程截图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cENslKUz-1670972022134)(image/1669446401017.png)]

安装过程中可能的报错信息：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lrScPfhF-1670972022134)(image/1669446455487.png)]

一个命令：yum remove mysql-libs解决，清除之前安装过的依赖即可

执行如下命令，如果成功表示安装 mysql成功。类似Java -version如果打出版本等信息

mysql --version
#或
mysqladmin --version

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U6NUtKZk-1670972022134)(image/1669446634416.png)]

执行如下命令，查看是否安装成功。需要增加 -i不用区分大小写，否则搜索不到。

rpm -qa | grep -i mysql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-reeq0ygl-1670972022135)(assets/1669446734598.png)]

4.服务的初始化

为了保证数据库目录与文件的所有者为mysql登录用户，如果你是以root身份运行mysql服务，需要执行下面的命令初始化

mysqld --initialize --user=mysql

说明：–initialize选项默认以‘安装’模式来初始化，则会为root用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码。生成的临时密码往日志中记录一份。

查看密码：

cat /var/log/mysqld.log

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XSL5IzCJ-1670972022135)(image/1669447113883.png)]

root@localhost:后面就是初始化的密码。

5.启动MySQL，查看状态

#加不加,service后缀都可以
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：Systemctl restart mysqld.service
查看状态：systemctl status mysqld.service

msyqld这个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ikJaiSkc-1670972022135)(image/1669447701302.png)]

查看进程：

ps -ef | grep -i mysql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VqLja68w-1670972022136)(image/1669447749958.png)]

6.查看MySQL服务是否启动

systemctl list-unit-files | grep mysqld.service

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CqcAtxEo-1670972022136)(image/1669447828629.png)]

默认是enabled

• 如果不是enabled可以运行如下设置自启动

  systemctl enabled mysqld.service

• 如果不希望自启动，运行如下命令设置

  systemctl disable mysqld.service

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uooGE9eq-1670972022136)(assets/1669447956847.png)]

3.MySQL登录

1.首次登录

通过mysql -hlocalhost -P3306 -p 进行登录。在Enter password:录入初始化密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TnwVn47k-1670972022137)(image/1669448430889.png)]

2.修改密码

• 因为初始化密码默认是过期的,所以查看数据库会报错

• 修改密码

  Alter user ‘root’@‘localhost’ identified by ‘new_password’;

• 5.7版本之后(不含5.7), mysql加入了全新的密码安全机制。设置新密码太简单会报错。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h6SxO5Qf-1670972022137)(image/1669448632818.png)]

• 改为更复杂的密码规则之后,设置成功,可以正常使用数据库了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PWlk6uMQ-1670972022138)(image/1669448673638.png)]

3.设置远程登录

3.1当前问题：

在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息,这是由于Mysql配置了不支持远程连接引起的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CQCXD1D5-1670972022138)(image/1669448745056.png)]

3.2确认网络

1.在远程机器上使用ping ip地址保证网络畅通

2.在远程机器上使用telnet命令保证端口号开放访问

telnet ip 地址 端口号

拓展：telnet命令开启

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jdz9Kv6r-1670972022138)(image/1669448920606.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aZWOeO9T-1670972022138)(image/1669448946108.png)]

3.3.关闭防火墙或开发端口

方式一：关闭防火墙

• CentOS 6 ：

  service iptables stop

• CentOS 7 :

  systemctl start firewalld.service

  systemctl status firewalld.service

  systemctl stop firewalld.service

  #设置开启启动防火墙

  Systemctl enable firewalld.service

  systemctl disable firewalld.service

方式二：开放端口：

• 查看开放的端口号

  firewall-cmd --list-all

• 设置开放的端口号

  firewall-cmd --add-service=http --permanent

  firewall-cmd --add-port=3306/tcp --permanent

• 重启防火墙

  firewall-cmd --reload

4.Linux下修改配置

在Linux系统MySQL下测试：

use mysql
select host,user from user;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5UwTWTPy-1670972022139)(image/1669449451550.png)]

可以看到root用户的当前主机配置信息为localhost

• 修改Host为通配符%

  Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1这里的意思就是说root用户只 能通过192.168.1.1的客户端去访问。user=rootHost=localhost,表示只能通过本机客户端去访问。而 % 是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连 接。如果Host=% ，表示所有IP都有连接权限。
  注意:在生产环境下不能为了省事将hst设置为%，这样做会存在安全问题，具体的设置可以根据生产 环境的IP进行设置.

  update user set host = ‘%’ where user = ‘root’;

  Host 设置了‘%’后便可以允许远程访问。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MV4IXsml-1670972022139)(image/1669449695738.png)]

host修改完后，记得执行flush privileges 使配置立即生效

flush privileges

3.5测试

• 如果是 MySQL5.7 版本，接下来就可以使用SQLyog或者Navicat成功连接至MySQL了

• 如果是 MySQL8 版本，连接时还会出现如下问题:

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UjzWXDXu-1670972022139)(image/1669449842243.png)]

  配置新连接报错:错误号码2058,分析是mysql密码加密方法变了。

  解决方法 :

  Linux下mysql -u root-p登录你的mysql数据库,然后执行这条SQL:

  alter user ‘root’@‘%’ identified with mysql_native_password by ‘root’;

4.MySQL的密码强度评估

4.1 MySQL不同版本设置密码（可能出现）

• mysql5.7中：成功

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5h1iHpP6-1670972022140)(image/1669450982276.png)]

• MySQL8.0中：失败

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2GO4tXyi-1670972022140)(image/1669451013167.png)]

4.2 MySQL8之前的安全策略

在MySQL 8.0之前，MySQL使用的是validate_password插件检测、验证账号密码强度，保障账号的安全性

安装/启用插件方式1: 在参数文件my.cnf中添加参数

[mysqld]
plugin-load-add=validate_password.so
\#ON/0FF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用)
validate-password=FORCE_PLUS_PERMANENT

说明1:plugin library中的validate_password文件名的后缀名根据平台不同有所差异。对于Unix和Unix-like系统而言,它的文件后缀名是.so,对于Windows系统而言,它的文件后缀名是.dll。

说明2:修改参数后必须重启MySQL服务才能生效。

说明3:参数FORCE_PLUS_PERMANENT是为了防止插件在MySQL运行时的时候被卸载。当你卸载插件时就会报错。如下所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-olierROh-1670972022140)(image/1669451403837.png)]

安装/启用插件方式2:运行时命令安装(推荐)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6orLmGIM-1670972022140)(image/1669451591005.png)]

此方法也会注册到元数据,也就是mysql.plugin表中,所以不用担心MySQL重启后插件会失效。

4.3MySQL安全策略

1.validate_password说明

MySQL 8.0, 引入了服务器组件(Components)这个特性,validate_password插件已用服务器组件重新实现。8.0.25版本的数据库中,默认自动安装validate password组件。

未安装插件前,执行如下两个指令,执行效果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hOffe0Ap-1670972022141)(image/1670367991885.png)]

安装插件后,执行如下两个指令,执行效果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZVH1jvD3-1670972022141)(image/1669451846422.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vq8H0d98-1670972022141)(image/1669451912829.png)]

关于validate_password组件对应的系统变量说明：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jf4FOIz3-1670972022142)(image/1669451940065.png)]

提示:

组件和插件的默认值可能有所不同。例如, MySQL 5.7. validate_password_check_user_name的默认值为OFF。

2.修改安全策略

修改密码验证安全强度

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_policy=MEDIUM;
SET GLOBAL validate_password_policy=STRONG;
SET GLOBAL validate_password_policy=0;  # For LOW
SET GLOBAL validate_password_policy=1;  # For MEDIUM
SET GLOBAL validate_password_policy=2;  # For HIGH
#注意,如果是插件的话,SQL为set global validate_password_policy=LOW

此外，还可以修改密码中字符的长度

set global validate_password_length=1;

3.密码强度测试

如果你创建密码是遇到"Your password does not satisfy the current policy requirements",可以通过函数组件去检测密码是否满足条件:0-100。当评估在100时就是说明使用上了最基本的规则:大写+小写+特殊字符+数字组成的8 位以上密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dkICEDpe-1670972022142)(image/1669452339688.png)]

注意:如果没有安装validate_password组件或插件的话,那么这个函数永远都返回0。关于密码复杂度对应的密码复杂度策略。如下表格所示:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zU5HVmJc-1670972022143)(image/1669452591194.png)]

4.4 卸载插件、组件

卸载插件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zUkJrC1o-1670972022143)(image/1669452707455.png)]

删除组件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NKY7ygPT-1670972022143)(image/1669452824105.png)]

5.字符集的相关操作

5.1修改MySQL5.7字符集

1.修改步骤

在MySQL 8.0版本之前,默认字符集为1atin1,utf8字符集指向的是utf8mb3。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码,就会出现乱码的问题。从MySQL 8.0开始,数据库的默认编码将改为utf8mb4,从而避免上述乱码的问题。

操作1：查看默认使用的字符集

show variables like 'character_%';
#或者
show variables like '%char%';

• MySQL8.0中执行

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-An8EwsJm-1670972022144)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450228721.png)]

  • MySQL5.7中执行

    MySQL 5.7默认的客户端和服务器都用了latin1,不支持中文,保存中文会报错。MySQL5.7截图如下:

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1Y7b1K55-1670972022144)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450302338.png)]

    在MySQL5.7中添加中文数据时,报错:

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-olAYK7C7-1670972022144)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450330250.png)]

    因为默认情况下，创建使用的是latin1。如下：

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JkRW9soZ-1670972022144)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450385532.png)]

    操作2：修改字符集

    vim /etc/my.cnf

    在MySQL5.7或之前的版本中，在文件最后加上中文字符配置：

    character_set_server=utf8

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BzsnwYB9-1670972022145)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450502365.png)]

  操作3：重启MySQL服务

  systemctl restart mysqld

  但是原库、原表的设定不会发生变化,参数修改只对新建的数据库生效。

  2.已有库&表字符集的变更

  修改已创建数据库的字符集

  alter database datest1 character set ‘utf8’；

  修改已创建的数据表字符集

  alter table emp convert to character set ‘utf8’;

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kzlTOYSC-1670972022145)(…/…/…/%E5%B0%9A%E7%A1%85%E8%B0%B7MySQL%E5%85%A5%E9%97%A8%E5%88%B0%E9%AB%98%E7%BA%A7-%E5%AE%8B%E7%BA%A2%E5%BA%B7%E7%89%88/%E9%AB%98%E7%BA%A7%E7%AF%87/%E8%AF%BE%E4%BB%B6/image/1669450765154.png)]

  **注意:**但是原有的数据如果是用非’utf8’编码的话,数据本身编码不会发生改变。已有数据需要导出或删除,然后重新插入。

5.2 各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：

• 服务器级别
• 数据库级别
• 表级别
• 列级别

执行如下SQL语句：

show variables like ‘character_%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5HpUXJpY-1670972022145)(image/1669453773835.png)]

• character_set server:服务器级别的字符集
• character set database:当前数据库的字符集
• character_set_client: 服务器解码请求时使用的字符集
• character_set_connection:服务器处理请求时会把请求字符串从character_set_client转为character_set_connection:character_set_connection
• character_set_results: 服务器向客户端返回数据时使用的字符集

服务器级别

• character_set_server:服务器级别的字符集。

  我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值。比如我们可以在配置文件中这样写:

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

数据库级别

character_set_database :当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则,具体语法如下:

CREATE DATABASE 据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];
	
ALTER DATABASE数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

其中的DEFAULT可以省略,并不影响语句的语义。比如:

mysql> CREATE DATABASE charset_demo_db CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)

数据库的创建语句中也可以不指定字符集和比较规则,比如这样:

create database 数据库名；

这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则,语法如下:

CREATE TABLE 表名(列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE比较规则名称]]


ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

比方说我们在刚刚创建的charset_demo_db数据库中创建一个名为t的表,并指定这个表的字符集和比较规则:

mysql> CREATE TABLE t(col VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, O rows affected (0.03 sec)

如果创建和修改表的语句中没有指明字符集和比较规则,将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

对于存储字符串的列,同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则,语法如下:

CREATE TABLE 表名(
		列名 字符串类型[CHARACTER SET 字符集名称][COLLATE 比较规则名称],
    	    其他列..
	);
ALTER TABLE 表名 MODIFY 列名 字符串类型[CHARACTER SET 字符集名称][COLLATE 比较规则名称];

比如我们修改一下表t中列col的字符集和比较规则可以这么写:

mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, O rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

对于某个列来说,如果在创建和修改的语句中没有指明字符集和比较规则,将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

提示:

​ 在转换列的字符集时需要注意,如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8,列中存储了一些汉字,现在把列的字符集转换为ascii的话就会出错,因为ascii字符集并不能表示汉字字符。

小结：

我们介绍的这4个级别字符集和比较规则的联系如下：

• 如果 创建活修改列 时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则
• 如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则
• 如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据 这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 t 中插入一 条记录:

mysg1> INSERT INTO t(co1) VALUES(我们);
Query ok，1 row affected (0.00 sec)
mysq1> SELECT * FROM t;
+--------+
s
+--------+
| 我们
+--------+
1 row in set (0.00 sec)

首先列 o1使用的字符集是 cbk ，一个字符我”在 bk 中的编码为 OXCED2 ，占用两个字节，两个字符的实际数据就占用4个字节.如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用6个字节

5.3字符集与比较规则

1.utf8 与utf8mb4

utf8字符集表示一个字符需要使用1_{4个字节,但是我们常用的一些字符使用1}3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度,在某些方面会影响系统的存储和性能,所以设计MySQL的设计者偷偷的定义了两个概念:

• utf8mb3 :阉割过的utf8字符集,只使用1~3个字节表示字符。
• utf8mb4 :正宗的utf8 字符集,使用1~4个字节表示字符。

在MySQL中utf8 是utf8mb3 的别名,所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码一个字符的情况,比如存储一些emoji表情,那请使用utf8mb4。

此外,通过如下指令可以查看MySQL支持的字符集:

show charset;
#或
show character set；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4AZbcioG-1670972022146)(image/1669455833718.png)]

2.比较规则

上表中,MySQL版本一共支持41种字符集,其中的Default collation列表示这种字符集中一种默认的比较规则,里面包含着该比较规则主要作用于哪种语言,比如utf8_polish_ci表示以波兰语的规则比较,utf8_spanish_ci是以西班牙语的规则比较, utf8_general_ci是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8mauMmV9-1670972022146)(image/1669456036704.png)]

常用操作

#查看GBK字符集的比较规则
SHOW COLLATION LIKE'gbk%'
#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%'

常用操作2

#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

说明1：

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

utf8_general_ci 校对速度快,但准确度稍差。

utf8_unicode_ci准确度高,但校对速度稍慢。

一般情况,用utf8_general_ci就够了,但如果你的应用有德语、法语或者俄语,请一定使用utf8_unicode_ci。

说明2：

修改了数据库的默认字符集和比较规则后,原来已经创建的表格的字符集和比较规则并不会改变,如果需要,那么需单独修改。

常用操作3

#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from atguigudb like 'employees';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

3.请求到响应过程中字符集的变化

我们知道从客户端发往服务器的请求本质上就是一个字符串,服务器向客户端返回的结果本质上也是一个字符串,而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的,从发送请求到返回结果这个过程中伴随着多次字符集的转换,在这个过程中会用到3个系统变量,我们先把它们写出来看一下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nGQJgCNf-1670972022146)(image/1669456464850.png)]

这几个系统变量在我的计算机上的默认值如下(不同操作系统的默认值可能不同)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WixpXfRp-1670972022146)(image/1669456547816.png)]

为了体现出字符集在请求处理过程中的变化,我们这里特意修改一个系统变量的值:

mysql> set character_set_connection=gbk;
Query OK, O rows affected (0.00 sec)

现在假设我们客户端发送的请求是下边这个字符串:

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程,我们只分析字符’我’在这个过程中字符集的转换。

现在看一下在请求从发送到结果返回过程中字符集的变化:

1. 客户端发送请求所使用的字符集

   一般情况下客户端所使用的字符集和当前操作系统一致,不同操作系统使用的字符集可能不一样,如下:

   • 类Unix系统使用的是utf8

   • Windows 使用的是gbk

     当客户端使用的是utf8字符集,字符’我’在发送给服务器的请求中的字节形式就是:0xE68891

     提示如果你使用的是可视化工具,比如navicat之类的,这些工具可能会使用自定义的字符集来编码发送到服务器的字符串,而不采用操作系统默认的字符集(所以在学习的时候还是尽量用命令行窗口)

2. 服务器接收到客户端发送来的请求其实是一串二进制的字节,它会认为这串字节采用的字符集是character_set_client,然后把这串字节转换为character_set_connection字符集编码的字符。由于我的计算机上character_set_client的值是utf8,首先会按照utf8字符集对字节串0xE68891进行解码,得到的字符串就是’我’,然后按照character_set_connection代表的字符集,也就是gbk进行编码,得到的结果就是字节串OxCED2。

3. 因为表t的列col采用的是gbk字符集,与character_set_connection一致,所以直接到列中找字节值为OxCED2的记录,最后找到了一条记录。

   提示：

   如果某个列使用的字符集和character_set_connection代表的字符集不一致的话,还需要进行一次字符集转换。

4. 上一步骤找到的记录中的col列其实是一个字节串OxCED2,col列是采用gbk进行编码的,所以首先会将这个字节串使用gbk进行解码,得到字符串’我’,然后再把这个字符串使用character_set_results代表的字符集,也就是utf8进行编码,得到了新的字节串: 0xE68891,然后发送给客户端。

5. 由于客户端是用的字符集是utf8,所以可以顺利的将0xE68891解释成字符我,从而显示到我们的显示器上,所以我们人类也读懂了返回的结果。

总结图示如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCFweECh-1670972022147)(image/1669457217579.png)]

从这个分析中我们可以得出这么几点需要注意的地方:

• 服务器认为客户端发送过来的请求是用character_set_client编码的。假设你的客户端采用的字符集和character_set_client不一样的话,这就会出现识别不准确的情况。比如我的客户端使用的是utf8字符集,如果把系统变量character_set_client的值设置为ascii的话,服务器可能无法理解我们发送的请求,更别谈处理这个请求了,
• 服务器将把得到的结果集使用character_set_results编码后发送给客户端。假设你的客户端采用的字符集和character_set_results不一样的话,这就可能会出现客户端无法解码结果集的情况,结果就是在你的屏幕上出现乱码。比如我的客户端使用的是utf8字符集,如果把系统变量character_set_results的值设置为ascii的话,可能会产生乱码。
• character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection 时使用,一定要注意,该字符集包含的字符范围一定涵盖请求中的字符,要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。

经验

开发中通常把character_set_client、character_set_connection, character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况,这样减少了很多无谓的字符集转换。为了方便我们设置, MySQL提供了一条非常简便的语句:

set names 字符集名；

这一条语句产生的效果和我们执行这3条的效果是一样的:

SET character_set_client = 字符集名;

SET character_set_connection = 字符集名;

SET character_set_results = 字符集名；

比方说我的客户端使用的是utf8字符集,所以需要把这几个系统变量的值都设置为utf8:

SET NAMES utf8;

另外,如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results 这三个系统变量的值设置成一样的,那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项,比如在配置文件里可以这么写:

[client] 
default-character-set=utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的,都会将那三个系统变量的值设置成utf8。

6.SQL 大小写规范

6.1windows和Linux平台区别

在SQL中，关键字和函数名是不用区分大小写的，比如SELECT，WHERE，ORDER,GROUP BY等关键字，以及ABS、MOD，ROUND，MAX等函数名。

不过在SQL中，你还是要确定大小写的规范，因为在Linux和Windows环境下，你可能会遇到不同的大小写问题，Windows系统默认大小写不敏感，但是Linux系统是大小写敏感的通过如下命令查看：

SHOW VARIABLES LIKE 'lower_case_table_names';

Windows系统下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tzEMMJ3K-1670972022147)(image/1669459698373.png)]

Linux系统下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cwiOXXn0-1670972022147)(image/1669459816243.png)]

• lower_case_table_names参数值的设置：

  • 默认为0，大小写敏感
  • 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于SQL语句都是转换为小写对表和数据库进行查找。
  • 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

• 两个平台上SQL大小写的区别具体来说：

  MySQL 在LInux下数据库名、表明、列明、别名大小写规则是这样的：

  • 数据库名、表名、表的别名、变量名都是严格区分大小写的；
  • 关键字、函数名称在SQL中不区分大小写；
  • 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

  MySQL在Windows的环境下全部不区分大小写

6.2Linux下大小写规则设置

当想设置为大小写不敏感时，要在my.cnf这个配置文件[mysqld]中加入lower_case-table-names=1然后重启服务器。

• 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。
• 此参数适用于MySQL5.7。在MySQL8下禁止在重启MySQL服务时将lower_case_table_names设置成不同于初始化MySQL服务时设置的lower_case_table_names值。如果非要将MySQL设置为大小写不敏感，具体步骤为：
  • 1.停止MySQL服务
  • 2.删除数据目录，即删除/var/lib/mysql目录
  • 3.在MySQL配置文件(/etc/my.cnf)中添加lower_case_table_names=1
  • 4.启动MySQL服务

注意：在进行数据库参数设置之前，需要掌握参数带来的影响，切不可盲目设置。

6.3 SQL编写的建议

如果你的变量名命令规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

1. 关键字和函数名称全部大写；
2. 数据库名、表名。表别名、字段别名等全部小写；
3. SQL语句必须以分号结束；

数据库名、表名和字段名在Linux MySQL环境下是区分大小写的，因此建议你统一这些字段的命名规范，比如全部采用小写的方式。

虽然关键字和函数名称在SQL中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

7.sql_mode的合理设置

7.1 介绍

sql_mode 会影响MySQL支持SQL语法以及它执行的数据验证检查。通过设置sql_mode ,可以完成不同严格程度的数据校验，有效地保障数据准确性。

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的：

• 5.6 的sql_mode默认值为空(即：NO_ENGINE_SUBSTITUTION),其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。
• 5.7 的sql_mode是STRICT_TRANS_TABLES,也就是严格模式。用于进行数据的严格校验，错误数据不能插入，报error（错误），并且事务回滚。

7.2 宽松模式VS严格模式

宽松模式：

如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

举例：在创建一个表时，该表中有一个字段为name，给name设置的字段类型为char(10)，如果在插入数据的时候，其中name这个字段对应的有一条数据的长度超过了10，例如‘1234567890abc’，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了‘1234567890’，而‘abc’就没有了，这就是宽松模式的效果。

应用场景：通过设置sql_mode 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql进行较大的修改。

严格模式

出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在生产等环境中，我们必须采用的是严格模式，进而开发、测试环境的数据也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

开发经验：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦一些步骤，但是这样做后，我们在进行数据库迁移或者项目迁移时，就会方便很多。

改为严格模式后可能存在的问题：

若设置模式中包含了NO_ZERO_DATE，那么MySQL 数据库不允许插入零日期，出入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为null或显示DEFAULT子句）将自动分配DEFAULT ‘0000-00-00 00:00:00’(零时间戳)，这显然是不满足sql_mode的NO_ZERO_DATE而报错。

7.3宽松模式再举例

宽松模式举例1：

select * from employees group by department_id limit 10;

select sql_mode=ONLY_FULL_GROUP_BY;

select * from employees group by department_id limit 10;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZRLnrT5O-1670972022148)(image/1669469593184.png)]

宽松模式举例2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0HVMJZt9-1670972022148)(image/1669469693527.png)]

7.4模式查看和设置

• 查看当前的sql_mode

  select  @@session.sql_mode
  select @@global.sql_mode
  #或者
  show variables like 'sql_mode';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9L43pSKZ-1670972022148)(image/1669469853705.png)]

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1SX7LHnj-1670972022149)(image/1669469879867.png)]

• 临时设置方式：设置当前窗口中设置sql_mode

  set global sql_mode = 'modes ...';#全局
  
  set SESSION sql_mode = 'modes...'#当前会话
  

  举例：

  #改为严格模式。此方法只当会话生效，关闭当前会话就不生效了。

  set session sql_mode = ‘STRICT_TRANS_TABLES’;

  #改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。

  set global sql_mode = ‘STRICT_TRANS_TABLES’;

• 永久设置方式：在/ect/my.cnf中配置sql_mode

  在my.cnf文件（Windows系统是my.ini文件），新增：

  [mysql]
  sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  

  然后重启mysql

  当然生产环境上是禁止重启MySQL服务的，所以采用临时设置方式+永久设置方式来解决线上问题，那么即便是有一天真的重启了MySQL服务，也会永远生效了。

• 测试数据：

  运行结果报错：

  7.5 sql_mode常用值

  下面列出MySQL中最重要的3种模式：

…(img-ZRLnrT5O-1670972022148)]

宽松模式举例2

[外链图片转存中…(img-0HVMJZt9-1670972022148)]

7.4模式查看和设置

• 查看当前的sql_mode

  select  @@session.sql_mode
  select @@global.sql_mode
  #或者
  show variables like 'sql_mode';
  

  [外链图片转存中…(img-9L43pSKZ-1670972022148)]

  [外链图片转存中…(img-1SX7LHnj-1670972022149)]

• 临时设置方式：设置当前窗口中设置sql_mode

  set global sql_mode = 'modes ...';#全局
  
  set SESSION sql_mode = 'modes...'#当前会话
  

  举例：

  #改为严格模式。此方法只当会话生效，关闭当前会话就不生效了。

  set session sql_mode = ‘STRICT_TRANS_TABLES’;

  #改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。

  set global sql_mode = ‘STRICT_TRANS_TABLES’;

• 永久设置方式：在/ect/my.cnf中配置sql_mode

  在my.cnf文件（Windows系统是my.ini文件），新增：

  [mysql]
  sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  

  然后重启mysql

  当然生产环境上是禁止重启MySQL服务的，所以采用临时设置方式+永久设置方式来解决线上问题，那么即便是有一天真的重启了MySQL服务，也会永远生效了。

• 测试数据：

  运行结果报错：

  7.5 sql_mode常用值

  下面列出MySQL中最重要的3种模式：

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dsFP3Evd-1670972022149)(image/1669471367783.png)]