MySQL 中的 sql_mode 选项以及配置

sql_mode 是 MySQL 数据库中的一个环境变量，用于定义 MySQL 支持的 SQL 语法、数据校验等。MySQL 5.6 和 MySQL5.7 默认的 sql_mode 参数值是不一样的。MySQL 5.6 的 sql_mode 是NO_ENGINE_SUBSTITUTION，其实是一个空值，相当于没有什么模式设置，可以理解为宽松模式。MySQL 5.7的 sql_mode 是 STRICT_TRANS_TABLES，也就是严格模式。

如果设置为宽松模式，那么在插入数据时，即便是给了一个非法的数据（有几种原因可以使一个值为非法。例如：数据类型错误，不适合列，或超出范围。），也可能会被接受，并且不报错。例如：一个表表中有一个字段为 name，给 name 设置的字段类型为 char(10)，如果在插入数据时，如果 name 字段对应的数据的长度超过了 10，例如数据为：‘1234567890abc’，则不会报错，并且取前 10 个字符保存到表中。即：数据被保为 ‘1234567890’。

在开发、测试、生产等环境中，应该采用严格模式。MySQL 5.7 版本将 sql_mode 默认值改为了严格模式。

一、查询与设置 sql_mode

1、查询 sql_mode

-- （1）查看当前会话的 sql_mode 取值：使用 select @@SESSION.sql_mode;
mysql> select @@SESSION.sql_mode;
+-----------------------------------------------------------------------+
| @@SESSION.sql_mode                                                    |
+-----------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.01 sec)

-- （2）查看全局的 sql_mode 取值：使用 select @@GLOBAL.sql_mode;
mysql> select @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                     |
+-----------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

-- （3）使用 show variables like 'sql_mode';
mysql> show variables like 'sql_mode';
+-----------------------------------------------------------------------+
| Variable_name | Value                                                 |
+-----------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.07 sec)

2、设置 sql_mode

（1）通过命令设置

使用命令设置 sql_mode 之后，如果重启 MySQL 服务则又会恢复为初始状态。

-- 使用 SET GLOBAL sql_mode = '具体模式';
-- 此方法只在当前会话中生效，关闭当前会话之后失效。
mysql> SET GLOBAL sql_mode = 'ANSI';
Query OK, 0 rows affected, 1 warning (0.02 sec)

-- 使用 SET GLOBAL sql_mode = '具体模式';
-- 此方法在当前服务中生效，重新启动 MySQL 服务后失效。
mysql> SET SESSION sql_mode = 'ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 查看 sql_mode 的设置结果
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------+
| Variable_name | Value                                                                          |
+---------------+-----------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+---------------+-----------------------------------------------------------+
1 row in set (0.01 sec)

（2）在配置文件中设置

此方法永久生效，即使重启 MySQL 服务仍然有效。修改 /etc/my.cnf，加入如下内容：

[root@localhost home]# vi /etc/my.cnf
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

# 重新启动服务
[root@localhost home]# systemctl restart mysqld

查看 sql_mode 的设置结果：

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------+
| Variable_name | Value                                                           |
+---------------+-----------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER |
+---------------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

二、sql_mode 支持的模式

MySQL 支持三种 sql_mode 模式：ANSI 模式、TRADITIONAL 模式与 STRICT_TRANS_TABLES 模式。

1、ANSI 模式（宽松模式）

等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ONLY_FULL_GROUP_BY。对插入的数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报 warning 警告。具体设置内容如下：

-- 设置 sql_mode 为 'ANSI模式';
mysql> SET SESSION sql_mode = 'ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 查看 sql_mode 的设置结果
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------+
| Variable_name | Value                                                           |
+---------------+-----------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+---------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

2、TRADITIONAL模式（严格模式）

等同于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION。当向 MySQL 数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报 error 错误。用于事务时，会进行事务的回滚。具体设置内容如下：

-- 设置 sql_mode 为 'TRADITIONAL模式';
mysql> SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 查看 sql_mode 的设置结果
mysql> show variables like 'sql_mode';
+---------------+------------------------------------------------------------------+
| Variable_name | Value                                                             |
+---------------+------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

3、STRICT_TRANS_TABLES、STRICT_ALL_TABLES 模式（严格模式）

进行数据的严格校验，错误数据不能插入，报 error 错误。具体设置内容如下：

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES 模式';
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

-- 查看 sql_mode 的设置结果
mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.01 sec)

三、sql_mode 常用取值的含义

1、STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES

这两个选项对于 Innodb 表作用相同，对于单个 insert 操作，无论插入单行或是多行，只要插入数据与字段类型不兼容，则 insert 操作失败并回滚。

对于 Myisam 表是作用不同：

（1）STRICT_TRANS_TABLES：对于单个 insert 操作，插入单行数据与字段类型不兼容，则 insert 操作失败并回滚；插入多行数据，如果插入数据的第一行内容与字段类型不兼容，则 insert 操作失败并回滚，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据会转换成符合字段类型的格式再插入，不会中断和回滚。

（2）STRICT_ALL_TABLES：与STRICT_TRANS_TABLES不同的是，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据则会报错并终止 insert 操作。

这两个选项一般与 NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO 三个选项共同设置。

2、NO_ZERO_DATE

设置该值，MySQL 数据库不允许插入零日期。

3、NO_ZERO_IN_DATE

在严格模式下，不允许日期和月份为零。

4、ERROR_FOR_DIVISION_BY_ZERO

在 INSERT 或 UPDATE 过程中，如果数据被零除，则产生错误。如果未设置该模式，那么数据被零除时 MySQL 返回NULL。

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES'，出现一个警告，提示该模式需要与
-- NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO 合并使用
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

-- 插入除数为 0 的数据，则把 NULL 插入该列。
mysql> insert into t2 values(1,'tom','2000-1-1',20000/0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+------+------------+--------+
| id | name | birth      | salary |
+----+------+------------+--------+
|  1 | tom  | 2000-01-01 |   NULL |
+----+------+------------+--------+
1 row in set (0.00 sec)

-- 插入各种格式的空日期
mysql> insert into t2 values(2,'jack','0000-00-00',2520);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(3,'rose','1998-00-00',3000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(4,'mark','1998-10-00',4000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(5,'jerry','0000-10-25',4000);
Query OK, 1 row affected (0.00 sec)

-- 插入过长的字符串
mysql> insert into t2 values(10,'tom123456789123456789','2000-1-1',20000);
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> select * from t2;
+----+-------+------------+---------+
| id | name  | birth      | salary  |
+----+-------+------------+---------+
|  1 | tom   | 2000-01-01 |    NULL |
|  2 | jack  | 0000-00-00 | 2520.00 |
|  3 | rose  | 1998-00-00 | 3000.00 |
|  4 | mark  | 1998-10-00 | 4000.00 |
|  5 | jerry | 0000-10-25 | 4000.00 |
+----+-------+------------+---------+
5 rows in set (0.00 sec)

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------+
| Variable_name | Value                                                   |
+---------------+---------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+---------------------------------------------------------+
1 row in set (0.00 sec)

-- 插入除数为 0 的数据，则把 NULL 插入该列。
mysql> insert into t2 values(1,'tom','2000-1-1',20000/0);
ERROR 1365 (22012): Division by 0

-- 插入各种格式的空日期
mysql> insert into t2 values(2,'jack','0000-00-00',2520);
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'birth' at row 1
mysql> insert into t2 values(3,'rose','1998-00-00',3000);
ERROR 1292 (22007): Incorrect date value: '1998-00-00' for column 'birth' at row 1
mysql> insert into t2 values(4,'mark','1998-10-00',4000);
ERROR 1292 (22007): Incorrect date value: '1998-10-00' for column 'birth' at row 1
mysql> insert into t2 values(5,'jerry','0000-10-25',4000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+----+-------+------------+---------+
| id | name  | birth      | salary  |
+----+-------+------------+---------+
|  5 | jerry | 0000-10-25 | 4000.00 |
+----+-------+------------+---------+
1 row in set (0.00 sec)

-- 插入过长的字符串
mysql> insert into t2 values(10,'tom123456789123456789','2000-1-1',20000);
ERROR 1406 (22001): Data too long for column 'name' at row 1

5、ONLY_FULL_GROUP_BY

对于 GROUP BY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，则这个 SQL 是不合法的。

-- sql_mode 没有设置 ONLY_FULL_GROUP_BY 选项
mysql> select * from emp group by dept_id;
+----+------+---------+
| id | name | dept_id |
+----+------+---------+
|  1 | tom  |      11 |
|  3 | mark |      12 |
+----+------+---------+
2 rows in set (0.00 sec)

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------+
| Variable_name | Value                                                              |
+---------------+--------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+--------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from emp group by dept_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

6、NO_AUTO_VALUE_ON_ZERO

该值将影响自增列的数据插入。默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，则这个选项就有用了。

-- sql_mode 没有设置 NO_AUTO_VALUE_ON_ZERO 选项
mysql> insert into t1 values(0,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null,'tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | tom  |
+----+------+
2 rows in set (0.00 sec)

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO'
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------+
| Variable_name | Value                                                        |
+---------------+-------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(0,'jerry');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null,'jerry');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  0 | jerry |
|  1 | tom   |
|  2 | tom   |
|  3 | jerry |
+----+-------+
4 rows in set (0.00 sec)

7、ANSI_QUOTES

启用 ANSI_QUOTES后，不能用双引号来引用字符串。

mysql> select * from t1 where name="tom";
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | tom  |
+----+------+
2 rows in set (0.00 sec)

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,ANSI_QUOTES'
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| sql_mode      | ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1 where name="tom";
ERROR 1054 (42S22): Unknown column 'tom' in 'where clause'

8、NO_AUTO_CREATE_USER:

禁止 GRANT 创建密码为空的用户。

mysql> grant all on *.* to wang@localhost;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> grant all on *.* to wang@localhost identified by '';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> grant all on *.* to wang@localhost identified by 'abc';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> grant all on *.* to wang@localhost identified by 'Wgx123456.';
Query OK, 0 rows affected, 1 warning (0.03 sec)

9、PIPES_AS_CONCAT

将 “||” 视为字符串的连接操作符而非或运算符，这和 Oracle 数据库是一样是，也和字符串的拼接函数 concat 类似。

mysql> select 'a'||'b';
+----------+
| 'a'||'b' |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

-- 设置 sql_mode 为 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,ANSI_QUOTES,PIPES_AS_CONCAT'
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,ANSI_QUOTES,PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+---------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'a'||'b',concat('a','b');
+----------+-----------------+
| 'a'||'b' | concat('a','b') |
+----------+-----------------+
| ab       | ab              |
+----------+-----------------+
1 row in set (0.00 sec)