封面来源：由博主个人绘制，如需使用请联系博主。

本文参考：MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！

1. MySQL 的卸载

1.1 查看是否安装过 MySQL

如果使用 rpm 安装，执行以下命令查看是否存在 mysql-libs 的旧版本包：

1
rpm -qa | grep -i mysql

还可以检查 MySQL 服务的状态：

1
systemctl status mysqld.service

1.2 卸载 MySQL

关闭 MySQL 服务：

1
systemctl stop mysqld.service

查看安装过的 mysql-libs 版本包：

1
2
3
rpm -qa | grep -i mysql 
# 或 
yum list installed | grep mysql

依次卸载已安装的版本包：

1
yum remove 版本包名称

查找 MySQL 的相关文件：

1
find / -name mysql

依次删除查询出的文件：

1
rm -rf 查询出的文件

删除 my.cnf：

1
rm -rf /etc/my.cnf

2. MySQL 8.0 的安装

2.1 MySQL 的版本介绍

MySQL 有四个版本，分别是：

  1. MySQL Community Server：社区版本，开源免费，自由下载。
  2. MySQL Enterprise Edition：企业版本，需付费，不能在线下载。
  3. MySQL Cluster：集群版，开源免费，用于架设集群服务器。
  4. MySQL Cluster CGE：高级集群版，需付费。

本节将对 MySQL Community Server 的版本进行安装。

2.2 Linux 下安装 MySQL 8.0

安装包的获取

Linux 发行版采用 CentOS 7，CentOS 源于 Red Hat Enterprise Linux（RHEL）依照开放源代码规定释出的源码所编译而成。

点击 MySQL :: Download MySQL Community Server 进入 MySQL Community Downloads 下载选择，选择对应的操作系统与操作版本。比如 CentOS 7 源于 RHEL，因此有：

选择MySQL所在的操作系统和版本

下方的 Download Packages 选择 RPM Bundle，可以先在当前环境下下载压缩包，然后利用 FTP 将压缩包上传到 Linux 环境下，也可以复制压缩包名称，在 Linux 环境下使用 wget 命令下载。

推荐在 opt 目录下新建 mysql 目录，并切换到该目录下：

1
2
mkdir /opt/mysql
cd /opt/mysql

然后使用 wget 下载压缩包：

1
wget http://dev.mysql.com/get/mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar

其中的 mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar 即为压缩包名称。

安装前的检查

MySQL 安装过程中会在 /tmp 目录下创建 tmp_db 文件，因此 /tmp 目录需要较大的权限：

1
chmod -R 777 /tmp

安装 MySQL 时需要使用到 libaio 和 net-tools，检查这两个依赖是否存在：

1
2
rpm -qa | grep libaio
rpm -qa | grep net-tools

如果有任意一个不存在，则需要安装：

1
2
yum install libaio
yum install net-tools

某些 CentOS 版本可能会自带 MariaDB，它会与 MySQL 冲突，因此需要将其删除。

先查询下：

1
rpm -qa | grep maria

如果查询内容不为空，执行以下删除命令：

1
rpm -e --nodeps mariadb-libs

安装过程

解压安装包：

1
tar -xvf mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar

解压后，依次执行以下命令安装：

1
2
3
4
5
6
rpm -ivh mysql-community-common-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.32-1.el7.x86_64.rpm

不同版本之间需要安装或依赖的软件包可能不一样，如果发生错误，会给出详细的提示，根据提示操作即可。

所有软件包安装完成后，执行以下命令，查看安装的 MySQL 信息：

1
2
3
mysql --version
# 或
mysqladmin --version

出现以下信息，证明安装成功：

[root@mofan mysql]# mysql --version
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

还可以执行以下命令查看安装的软件包：

1
rpm -qa | grep -i mysql
[root@mofan mysql]# rpm -qa | grep -i mysql
mysql-community-common-8.0.32-1.el7.x86_64
mysql-community-icu-data-files-8.0.32-1.el7.x86_64
mysql-community-client-plugins-8.0.32-1.el7.x86_64
mysql-community-libs-8.0.32-1.el7.x86_64
mysql-community-server-8.0.32-1.el7.x86_64
mysql-community-client-8.0.32-1.el7.x86_64

服务的初始化

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

1
mysqld --initialize --user=mysql

--initialize 选项默认以安全模式进行初始化，会为 root 用户生成一个密码并将该密码标记为过期，登录后需要设置一个新的密码。

生成的临时密码会被记录在日志文件中，执行以下命令查看临时密码：

1
grep "password" /var/log/mysqld.log

暂时保存下这个密码，后续首次登录 MySQL 时需要使用。

MySQL 服务的相关操作：

1
2
3
4
5
6
7
8
# 启动
systemctl start mysqld.service
# 关闭 
systemctl stop mysqld.service
# 重启 
systemctl restart mysqld.service
# 查看状态
systemctl status mysqld.service

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

查看 MySQL 进程信息：

1
ps -ef | grep -i mysql

服务的自启动

查看 MySQL 服务是否是自启动：

1
systemctl list-unit-files | grep mysqld.service

默认为 enabled，为自启动。

可以进行如下设置，设置服务是否自启动：

1
2
3
4
# 服务自启动
systemctl enable mysqld.service
# 关闭自启动
systemctl disable mysqld.service

2.3 登录 MySQL

执行以下命令以 root 用户登录 MySQL：

1
mysql -hlocalhost -P3306 -uroot -p

之后在 Enter password: 后输入前面保存的临时密码。

临时密码是会过期的，登录成功后首先修改密码：

1
2
# 将 root 用户的密码修改为 new_password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

密码要稍微复杂点，太简单将报错。

当然也可以修改密码验证策略，使其接受简单的密码：

1
set global validate_password_policy=0;

最后执行以下命令使密码生效：

1
flush privileges;

2.4 使用 Navicat 连接

本文并未在虚拟机上安装 MySQL，而是在阿里云 ECS 上进行安装，因此使用 Navicat 连接 MySQL 相对简单，无需进行确认网络、关闭防火墙、开放端口等操作。

使用 Navicat 连接阿里云 ECS 上的 MySQL 有两种方式：

  1. 直连
  2. 使用 SSH 通道

直连

首先需要进入阿里云 ECS 的安全组配置列表，在 入方向 的安全组规则中开放 3306 端口，作为外部访问 MySQL 的端口。

在 Linux 终端以 root 用户登录 MySQL 后，查看 user 表中的信息：

1
2
use user;
select Host, User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

可以看到 root 用户对应的 Host 为 localhost，也就说 root 用户只能通过本机客户端去访问，其他 IP 无法访问。

Host 列指定了允许用户登录所使用的 IP。比如对于 user=root, Host=192.168.1.1 来说，root 用户只能通过 IP 为 192.168.1.1 的客户端去访问。Host 列支持 % 通配符，比如 192.168.1.% 表示 IP 地址前为 192.168.1. 的客户端都可以连接。如果 Host=% ，那么所有 IP 都有连接权限。

在生产环境下不能为了将 Host 设置为 %，这会存在安全问题，具体的设置要根据生产环境的 IP 进行设置。

在学习过程中直接将 Host 设置为 %，方便 Navicat 的连接：

1
update user set host = '%' where user ='root';

最后执行 flush privileges; 使配置生效。

最后在 Navicat 上按照以下方式连接：

直连阿里云ECS上的MySQL

使用 SSH 通道

直连云服务器上的 MySQL 时，不仅要开放服务器的 3306 端口，还要设置 MySQL 中 root 用户的主机配置信息，这对服务器带来了安全隐患。使用 SSH 通道连接 MySQL，对服务器上的 MySQL 就相当于是本地连接。

首先配置 SSH 通道：

配置Navicat连接MySQL时使用的SSH通道

此处的【用户名】和【密码】不是登录 MySQL 使用的用户名和密码，而是连接 SSH 使用的用户名和密码！

然后通过 本地 连接 MySQL：

Navicat以本地方式连接MySQL

此处的【主机】填写 localhost 即可，因为是通过 SSH 通道连接的，对于 MySQL 来说就相当于是本地连接。

可能存在的问题

如果在测试连接时出现：

错误号码2058
Plugin caching_sha2_password could not be loaded:

这是因为 MySQL 密码加密方法变了。

在终端执行以下命令然后重新连接即可：

1
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root用户对应的密码';

3. 字符集

3.1 修改字符集

MySQL 8.0 之前，默认字符集为 latin1，utf8 字符集指向的是 utf8mb3，其中 mb3 表示 maximun of 3 bytes per multibyte character，即每个字符最多 3 个字节。标准 UTF-8 最多可以使用 4 个字节为字符编码，因此使用这种“残缺的 utf8”在表示某些字符时就可能出现乱码，因此也常常说 MySQL 的 utf8 编码是假的 UTF-8 编码。

从 MySQL 8.0 开始，数据库的默认编码将改为 utf8mb4，从而避免上述乱码的问题。

查看默认字符集：

1
2
3
show variables like 'character%';
# 或
show variables like '%char%';

在 MySQL 8.0 下是：

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

而在 MySQL 5.7 下，character_set_database 和 character_set_server 对应的值是 latin1，这种编码不支持中文。

因此在使用 MySQL 5.7 及其以下版本时，需要修改 MySQL 服务使用的编码是 utf8，尽管它也不完整，但好歹支持中文。

修改 my.cnf 文件：

1
vim /etc/my.cnf

在文件最后加上中文字符集配置：

1
character_set_server=utf8

保存修改并退出后，重启 MySQL 服务：

1
systemctl restart mysqld

如果在修改前，MySQL 中已经存在若干数据库和数据表，那么这些库表的编码并不会改变，只会影响修改后新建的库表。

针对这些库表可以使用下述方式进行修改：

1
2
alter database 数据库名称 character set 'utf8';
alter table 数据表名称 convert to character set 'utf8';

如果数据表中还有数据，并且还使用了非 utf8 编码，这些数据的编码也不会随着库表的编码的变化而变化。针对这部分数据，只能先导出或删除，然后再重新插入。

3.2 各级别的字符集

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

  • 服务器级别

  • 数据库级别

  • 表级别

  • 列级别

前文使用的 show variables like 'character%'; 语句就可以查看这些级别的字符集：

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

  • character_set_server：服务器级别的字符集

  • character_set_database：数据库的字符集

  • character_set_client：服务器解码请求时使用的字符集

  • character_set_connection：服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection

  • character_set_results：服务器向客户端返回数据时使用的字符集

服务器级别

可以在启动服务器程序时通过启动选项或在服务器程序运行过程中使用 SET 语句修改服务器级别的字符集和比较规则。比如：

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

数据库级别

可以在创建、修改数据库时指定数据库的字符集和比较规则：

1
2
3
4
5
6
7
CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

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

表级别

可以在创建、修改表时指定表的字符集和比较规则:

1
2
3
4
5
6
7
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

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

如果创建、修改表时没有指定，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。在创建、修改列定义时指定该列的字符集和比较规则：

1
2
3
4
5
6
CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

如果创建、修改列时没有指定，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

注意： 在转换列的字符集时，如果转换前列中存储的数据不能用转换后的字符集进行表示，那么转换时会发生错误。比如原先使用的字符集是 utf8，列中存储了一些汉字，然后把列的字符集转换为 ascii 时就会出错，因为 ascii 字符集不能表示汉字字符。

了解字符集和比较规则后，可以根据列的类型推断出存储数据时每个列的实际数据占用的存储空间大小。比如某列先使用 gbk 编码，每个汉字占用两个字节，那么“默烦”两个字共占用四个字节，当编码修改为 utf8 之后，“默烦”两个字将占用六个字节。

3.3 比较规则

比较规则用 COLLATE 表示，比如 utf8_general_ci 就是一种比较规则，表示通用比较规则。

比较规则的 _ 后缀表示该比较规则是否区分重音、大小写，具体如下：

后缀 英文释义 含义
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

比较规则的相关操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 查看 gbk 字符集的比较规则
SHOW COLLATION LIKE 'gbk%';

# 查看 utf8 字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

# 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';

# 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';

# 查看具体数据库的字符集
SHOW CREATE DATABASE 数据库名;

# 修改具体数据库的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

#查看表的字符集
show create table 数据表名;

#查看表的比较规则
show table status from 数据表名 like 'employees';

# 修改表的字符集和比较规则
ALTER TABLE 数据表名 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

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

utf8_general_ci 校对速度快，但准确度较差；utf8_unicode_ci 校准度高，但校对速度稍慢。

一般情况下使用 utf8_general_ci 即可，但如果存储了德语、法语或俄语，务必使用 utf8_unicode_ci。

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

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

这三个值在 MySQL 8.0 中的默认值都是 utf8mb4。

为了体现出字符集在请求处理过程中的变化，将 character_set_connection 的值修改为 gbk：

1
set character_set_connection = gbk;

假设在客户端中需要查询 t 表中 col 列等于 我 的所有信息：

1
SELECT * FROM t WHERE col = '我';

请求从发送到结果返回过程中字符集的变化

  1. 客户端发送请求所使用的字符集：一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样。类 Unix 系统使用的是 utf8，Windows 系统使用的是 gbk。如果使用的是 Navicat 等可视化工具，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集。当客户端使用的是 utf8 字符集，字符 我 在发送给服务器的请求中的字节形式是 0xE68891。

  2. 服务器接收到客户端发送来的请求是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client，然后把这串字节转换为 character_set_connection 字符集编码的字符。

    假设当前计算机上 character_set_client 的值是 utf8，那么首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串 我，然后按照 character_set_connection 代表的字符集，即 gbk 进行编码，得到结果字节串 0xCED2。

  3. 表 t 的列 col 采用 gbk 字符集，与 character_set_connection 一致，直接到列中查找字节值为 0xCED2 的记录，最后找到了一条记录。如果某个列使用的字符集和 character_set_connection 代表的字符集不一致，则还需进行一次字符集转换。

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

  5. 由于客户端使用的字符集是 utf8，所以可以顺利地将 0xE68891 解释成字符 我，从而显示到显示器上。

请求从发送到结果返回过程中字符集的变化

从上述分析中可知：

  • 服务器认为客户端发过来的请求是用 character_set_client 编码的。假设客户端采用的是字符集和 character_set_client 不一致时，就会出现识别不准确的情况。比如客户端使用 utf8，而 character_set_client 的值设置为 ascii 就会出现识别不准确。
  • 服务器将把得到的结果集使用 character_set_results 编码后发送给客户端。如果客户端采用的字符集和 character_set_results 不一致时，客户端可能无法解码结果集，导致出现乱码。
  • character_set_connection 只是服务器在将请求的字节串从 character_set_client 转换为 character_set_connection 时使用。注意， 该字符集包含的字符范围一定涵盖请求中的字符，否则会导致有的字符无法使用 character_set_connection 代表的字符集进行编码。

开发中的经验

实际开发中一般会把这三个系统变量设置成和客户端使用的字符集一样，MySQL 提供了一条简洁的语句完成这三个系统变量的设置：

1
SET NAMES 字符集名;

比如：

1
SET NAMES utf8;

执行这条语句的效果和执行下面三条语句的效果一致：

1
2
3
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

除此之外，可以在启动客户端时把这三个系统变量的值设置成一样，需要在配置文件中指定 default-character-set 的启动选项：

1
2
[client]
default-character-set=utf8

4. SQL 大小写规范

4.1 Windows 和 Linux平台区别

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

在不同的操作系统中，对大小写的敏感程度是不一样的。比如 Windows 系统默认大小写不敏感，而 Linux 系统下是大小写敏感的。

是否大小写敏感可以通过下列语句查看：

1
SHOW VARIABLES LIKE '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

lower_case_table_names 参数值的设置：

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

MySQL 在 Linux 下数据库名、表名、列名、别名的大小写规则：

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

MySQL 在 Windows 环境下全都不区分大小写。

4.2 Linux 下大小写规则设置

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

注意： 在重启数据库实例之前需要将原来的数据库和表转换为小写，否则将找不到数据库名。假设原本大小写敏感，存在名为 Emp 的表，磁盘上也以 Emp 的形式进行存储，当修改为大小写不敏感时，将会把 SQL 语句转换为小写然后进行查询，比如对于 Emp 则会转换为 emp，而磁盘上存储的 Emp 显然无法与 emp 匹配成功。

除此之外，lower_case_table_names 参数只适用于 MySQL 5.7，在 MySQL 8.0 下禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。

如果非要将 MySQL 8.0 设置为大小写不敏感，可以：

  1. 停止 MySQL 服务；
  2. 删除数据目录，即删除 /var/lib/mysql 目录（该目录下存放了数据库文件，删除后相当于删库）；
  3. 在 MySQL 配置文件 /etc/my.cnf 中添加 lower_case_table_names=1；
  4. 启动 MySQL 服务。

注意： 在进行数据库参数设置前，需要知晓该参数带来的影响，切忌盲目修改。 总结下来就是不要修改！

4.3 SQL 编写建议

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

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

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

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

5. sql_mode 的合理设置

5.1 简介

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

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

MySQL 5.6 和 MySQL 5.7 下 sql_mode 的默认参数是不一样的：

  • MySQL 5.6 下的默认值为空，即 NO_ENGINE_SUBSTITUTION，相当于没有任何模式设置，可以理解为 宽松模式。在这种模式下允许一些非法操作，比如允许非法数据的插入。
  • MySQL 5.7 下的默认值是 STRICT_TRANS_TABLES，也就是 严格模式。用于进行数据的严格校验，非法数据不能插入，直接报错，然后回滚事务。

5.2 宽松模式 VS 严格模式

宽松模式

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

比如：在创建一个表时，该表中有一个字段为 name，给 name 设置的字段类型时 char(10)，如果在插入数据时，name 字段对应的某条数据的长度超过了 10，比如 1234567890abc，超过了设定的字段长度 10。在宽松模式下不会报错，并且取前 10 个字符进行存储，也就是说该数据被存为 1234567890，而末尾的 abc 被截取了。

插入的数据是非法的，因为超过了规定的字段长度，但并没有报错，并且 MySQL 自行处理并接受了，这就是宽松模式的效果。

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

严格模式

当插入非法数据时，应该报错才对，所以 MySQL 5.7 版本就将 sql_mode 的默认值修改为严格模式。在生产环境中，必须采用严格模式，进而开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。即便用的 MySQL 5.6，也应该自行将其改为严格模式。

开发经验： MySQL 等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验。但在实际开发中，工程师应该在程序级别完成数据校验，虽然增加了一些编码步骤，但这样做之后，在进行数据库迁移或项目迁移时，就会方便许多。

严格模式下也可能存在一些问题：若设置模式中包含了 NO_ZERO_DATE，那么 MySQL 数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中 TIMESTAMP 类型的列在未声明为 NULL 或显式设置 DEFAULT 子句时将被自动分配为 DEFAULT '0000-00-00 00:00:00'（零时间戳），这显然是不满足 sql_mode 中的 NO_ZERO_DATE 进而报错。

5.3 模式查看和设置

查看当前的 sql_mode：

1
2
3
4
select @@session.sql_mode; # 当前会话
select @@global.sql_mode; # 全局
# 或者
show variables like 'sql_mode';

MySQL 8.0 下 sql_mode 的默认值是：

1
2
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

临时设置 sql_mode

1
2
SET GLOBAL sql_mode = '模式值'; # 全局
SET SESSION sql_mode = '模式值'; # 当前会话

sql_mode 常用值可参考 【5.5 sql_mode 的常用值】。

比如：

1
2
3
4
# 改为严格模式。此方法只在当前会话中生效，关闭当前会话后失效。
set SESSION sql_mode='STRICT_TRANS_TABLES';
# 改为严格模式。此方法在当前服务中生效，重启 MySQL 服务后失效。
set GLOBAL sql_mode='STRICT_TRANS_TABLES';  

永久设置 sql_mode

永久设置 sql_mode 需要在 /etc/my.cnf 配置文件中配置 sql_mode。

在 my.cnf 文件（Windows 环境下是 my.ini 文件），新增：

1
2
3
[mysqld]
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 服务，也会永久生效。

5.4 宽松模式的使用

运行以下语句，初始化数据：

1
2
3
4
5
6
7
CREATE TABLE mytbl2(id INT, NAME VARCHAR(16), age INT, dept INT);

INSERT INTO mytbl2 VALUES(1, 'zhang3', 33, 10);
INSERT INTO mytbl2 VALUES(2, 'li4', 34, 101);
INSERT INTO mytbl2 VALUES(3, 'wang5', 34, 102);
INSERT INTO mytbl2 VALUES(4, 'zhao6', 34, 102);
INSERT INTO mytbl2 VALUES(5, 'tian7', 36, 102);

查询每个部门年龄最大的人：

1
SELECT NAME, dept, MAX(age) FROM mytbl2 GROUP BY dept;

运行查询语句时会报错，因为 GROUP BY 的使用原则是 SELECT 后面只能放函数和 GROUP BY 后面的字段。

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'dbtest1.mytbl2.NAME' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

正确的查询：

1
2
3
4
SELECT m.*
FROM mytbl2 m
INNER JOIN(SELECT dept, MAX(age) maxage FROM mytbl2 GROUP BY dept) as ab
ON ab.dept = m.dept AND m.age = ab.maxage;
+------+--------+------+------+
| id   | NAME   | age  | dept |
+------+--------+------+------+
|    1 | zhang3 |   33 |   10 |
|    2 | li4    |   34 |  101 |
|    5 | tian7  |   36 |  102 |
+------+--------+------+------+

或者修改当前会话的 sql_mode 为 宽松模式：

1
SET SESSION sql_mode = '';

执行以下语句不会报错：

1
SELECT NAME, dept, MAX(age) FROM mytbl2 GROUP BY dept;
+--------+------+----------+
| NAME   | dept | MAX(age) |
+--------+------+----------+
| zhang3 |   10 |       33 |
| li4    |  101 |       34 |
| wang5  |  102 |       36 |
+--------+------+----------+

再尝试插入非法的数据（age 列的类型是 INT）：

1
INSERT INTO mytbl2(id, NAME, age) VALUES(6, 'Tom', 'aaa');

同样不会报错，查看插入的数据：

1
SELECT * FROM mytbl2 WHERE id = 6;
+------+------+------+------+
| id   | NAME | age  | dept |
+------+------+------+------+
|    6 | Tom  |    0 | NULL |
+------+------+------+------+

age 列的值为 0。

5.5 sql_mode 的常用值

模式名称 模式说明
ONLY_FULL_GROUP_BY 对于 GROUP BY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，那么这个 SQL 是非法的，因为列不在 GROUP BY 从句中
NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，那么这个选项就有用了
STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制，只对支持事务的表启用严格模式
NO_ZERO_IN_DATE 在严格模式下，不允许日期和月份为零
NO_ZERO_DATE 在非严格模式下，可以插入形如 00-00-0000:00:00 的非法日期，MySQL 仅抛出一个警告，而启用该选项后，MySQL 不允许插入零日期，插入零日期会抛出错误而非警告
ERROR_FOR_DIVISION_BY_ZERO 在 INSERT 或 UPDATE 过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时 MySQL 返回 NULL
NO_AUTO_CREATE_USER 禁止 GRANT 创建密码为空的用户
PIPES_AS_CONCAT 将 `
ANSI_QUOTES 启用后，不能用双引号来引用字符串，因为它被解释为识别符
REAL_AS_FLOAT 将 real 视为 float 的同义词而不是 double 的同义词
STRICT_ALL_TABLES 对所有引擎的表都启用严格模式