使用MySQL这么久,你了解sql_mode吗？
使用MySQL这么久,你了解sql_mode吗？
一 前言
前面一篇文章《案例| +1s导致的故障》介绍了因为开发同学对datetime值+1s的操作导致的问题。我们在复盘的时候讨论设置sql_mode为严格模式可行性。于是有了此文。
其实接触MySQL 好多年，自己并没有深入的非常详细的了解 sql_mode 值代表的含义。本文算是查缺补漏地学习 sql_mode 的不同的值对数据库操作的数据的影响。
二 实践
2.1 SQL MODE 是什么
SQL MODE 定义了MySQL支持何种SQL语法 以及 数据校验方式。通过设置SQL_MODE可以方便的在不同环境中使用MySQL,也方便数据从其他关系型数据库迁移到MySQL中。sql_mode 设置为严格模式才比较方便数据在不同环境，不同数据库系统中流转。
通过 SET [GLOBAL|SESSION] sql_mode='modes' 命令，SQL_MODE 支持全局和会话级别设置。
SQL MODE 在不同的版本有不同的默认值，5.6 版本默认为 NO_ENGINE_SUBSTITUTION。
MySQL 5.7 版本中默认为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。
MySQL 8.0版本中默认为 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION。
其实不同的小版本有不同的变化。大家可以认为MySQL随着版本迭代对sql_mode 默认设置越来越严格。
2.2 比较重要的SQL MODE 值
首先说一下严格模式的定义。严格模式是 指将 SQL_MODE 变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一个或者两个同时设置。
STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 对于事务表的影响是一样的。区别在于非事务表涉及到多行记录变更时的表现。下面我们通过例子来阐述两者之间的差异。
STRICT_TRANS_TABLES
在该模式下对INSERT ,UPDATE 的影响和变更的记录条数有关。
当第一个行数据非法，事务表和非事务表都直接报错，不会有记录插入成功。
mysql>insert into tx(a) values ('abcde'),('ab'),('abc');
ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql>insert into notx(a) values ('abcde'),('ab'),('abc');
ERROR 1406 (22001): Data too long for column 'a' at row 1
当第二条记录以及之后数据非法时，事务表直接报错，非事务表可以继续插入但是报warning，数据被截断。(如果是其他类型的值MySQL则会转换会最接近的默认值)。
mysql>insert into tx(a) values('ab'),('abc'),('abcde');
ERROR 1406 (22001): Data too long for column 'a' at row 3
mysql>insert into notx(a) values('ab'),('abc'),('abcde');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql>select * from tx;
Empty set (0.00 sec)
mysql>select * from notx;
+------+
| a |
+------+
| ab |
| abc |
| abc |
+------+
3 rows in set (0.00 sec)
单独设置 sql_mode 为
STRICT_TRANS_TABLES会提示warning，NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZEROsql modes should be used with strict mode. They will be merged with strict mode in a future release. 建议和严格模式一起设置，这样数据才会更安全。
STRICT_ALL_TABLES
当第一个行数据非法，事务表和非事务表都直接报错，不会有记录插入成功。
mysql> insert into tx(a) values ('abcde'),('ab'),('abc');
ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> insert into notx(a) values ('abcde'),('ab'),('abc');
ERROR 1406 (22001): Data too long for column 'a' at row 1
当第二条记录以及之后数据非法时，事务表和非事务表都直接报错，但是非事务表是有部分记录插入成功的。非法的数据被丢弃而不是截断或者存储其他默认值。
mysql> insert into tx(a) values('ab'),('abc'),('abcde');
ERROR 1406 (22001): Data too long for column 'a' at row 3
mysql> insert into notx(a) values('ab'),('abc'),('abcde');
ERROR 1406 (22001): Data too long for column 'a' at row 3
mysql>
mysql> select * from tx;
Empty set (0.00 sec)
mysql> select * from notx;
+------+
| a |
+------+
| ab |
| abc |
+------+
2 rows in set (0.00 sec)
NO_ENGINE_SUBSTITUTION
MySQL 创建表的时候通过指定 engine 的值来选择存储引擎。如果指定的存储引擎不存在呢(比如某些存储引擎没有编译)
如果 sql_mode 设置NO_ENGINE_SUBSTITUTION ，指定不存在的存储引擎会直接报错。
mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
mysql> create table tx3( a varchar(3)) engine=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
如果 sql_mode 没有设置 NO_ENGINE_SUBSTITUTION ，指定不存在的存储引擎会被替换为默认的存储引擎。
mysql> create table tx2( a varchar(3)) engine=FEDERATED;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATED' |
| Warning | 1266 | Using storage engine InnoDB for table 'tx2' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table tx2 \G
Table: tx2
Create Table: CREATE TABLE `tx2` (
`a` varchar(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
测试数据库不支持 FEDERATED 存储引擎，则自动替换为默认的 InnoDB 存储引擎。
该模式相当于宽松模式，不涉及表里面数据的校验方式和sql语法控制。
ONLY_FULL_GROUP_BY
设置该模式时，对于 GROUP BY 聚合操作，如果 GROUP BY的列没有在 select 中出现，那么这个 SQL 是不合法的。
mysql> create table t(id int,v int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(1,1),(1,2),(2,3),(2,4),(3,3),(3,5);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t group by id ;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.v' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select id,sum(v) from t group by id;
+------+--------+
| id | sum(v) |
+------+--------+
| 1 | 3 |
| 2 | 7 |
| 3 | 8 |
+------+--------+
3 rows in set (0.00 sec)
NO_AUTO_VALUE_ON_ZERO
该模式影响自增值，不设置该模式，通过向自增字段插入 null或者0 值时，自增字段会产生一个自增值。设置该模式之后，只能通过插入 null 值产生自增或者不插入对应的值。插入0值存储的还是0 而非下一个自增值。
不设置该模式
mysql> select * from x;
Empty set (0.00 sec)
mysql> insert into x(id,a) values(null,1),(0,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+---+
| id | a |
+----+---+
| 1 | 1 |
| 2 | 2 | ###自增自动对应的记录插入自增值 2
+----+---+
2 rows in set (0.00 sec)
设置该模式
mysql> show variables like 'sql_mode';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| sql_mode | NO_AUTO_VALUE_ON_ZERO |
+---------------+-----------------------+
1 row in set (0.00 sec)
mysql> insert into x(id,a) values(null,1),(0,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+---+
| id | a |
+----+---+
| 0 | 2 | ###自增自动对应的记录直接插入0
| 1 | 1 |
+----+---+
2 rows in set (0.00 sec)
该模式对于已经存在0值的自增列比较有用，当然通常的数据库开发规范中自增属性的基本都是主键。主键存放0值并不是一个非常好的选择。
NO_ZERO_IN_DATE
该模式决定了date类型的值(年月日)中，年不是0值，日期和月份是否可以是0值的情况。比如 2020-00-23 ，2020-00-00 这样的值是否可以成为合法的日期值。该模式的对数据合法性的影响依然和是否设置了 严格模式有关。
如果没有设置该模式，则允许设置日期和月份为0的值而且insert的时候 不产生warning。
mysql> show create table dt \G
******** 1. row ********
Table: dt
Create Table: CREATE TABLE `dt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`b` date NOT NULL DEFAULT '2020-01-01',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> insert into dt(b) values('2020-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> select id,b from dt;
+----+------------+
| id | b |
+----+------------+
| 1 | 2020-00-00 |
+----+------------+
1 row in set (0.00 sec)
如果设置该模式，则不允许设置日期和月份为0的值而且insert的时候产生warning，存储的记录为'0000-00-00'。
mysql> show variables like 'sql_mode';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| sql_mode | NO_ZERO_IN_DATE |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> insert into dt(b) values('2020-00-00'),('2020-00-01');
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'b' at row 1 |
| Warning | 1264 | Out of range value for column 'b' at row 2 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql> select id,b from dt;
+----+------------+
| id | b |
+----+------------+
| 1 | 2020-00-00 |
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)
当同时设置严格模式和NO_ZERO_IN_DATE时，写入类似2020-00-23 2020-00-00的值会导致报错。
在严格模式下，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。
NO_ZERO_DATE
该模式决定了 '0000-00-00' 是否可以成为合法的日期。它的作用和是否开启 严格模式有关。
如果没有设置 NO_ZERO_DATE，'0000-00-00' 可以当做合法的日期而且insert时不会有warning。
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> insert into dt(b) values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
如果设置了NO_ZERO_DATE，'0000-00-00' 可以当做合法的日期而且insert时会有warning。
mysql> show variables like 'sql_mode';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| sql_mode | NO_ZERO_DATE |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> insert into dt(b) values('2020-00-00'),('2020-00-01');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into dt(b) values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'b' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select id,b from dt;
+----+------------+
| id | b |
+----+------------+
| 1 | 2020-00-00 |
| 2 | 2020-00-01 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)
同时设置严格模式和NO_ZERO_DATE，'0000-00-00' 是一个非法的日期值。插入时会直接报错。但是如果用IGNORE选项可以插入零日期，但是会生成警告。
严格模式下，使用insert 插入'0000-00-00' 直接报错。
mysql> insert into dt(b) values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'b' at row 1
严格模式下，使用insert ignore 插入'0000-00-00'则可以插入成功，但是提示warning。
mysql> insert ignore into dt(b) values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select id,b from dt;
+----+------------+
| id | b |
+----+------------+
| 1 | 0000-00-00 |
+----+------------+
1 row in set (0.00 sec)
NO_ZERO_IN_DATE和NO_ZERO_DATE在未来版本中会被移除。
ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未设置该模式，数据被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE 中，MySQL生成被零除警告，但操作结果为NULL。
ANSI_QUOTES
默认情况下，MySQL的关键词以及表名等都可以有反引号 ` 引起来。启用ANSI_QUOTES后，便可以用双引号代替反引号。当然原来的反引号依然可用。但是引发的一个副作用是：不能用双引号来引用字符串，因为它将被解释为识别符。解决方法就是使用单引号来表示字符串。示例如下:
mysql> show variables like 'sql_mode';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| sql_mode | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)
mysql> insert into s select "ssss";
ERROR 1054 (42S22): Unknown column 'ssss' in 'field list'
mysql> insert into s select 'ssss';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
NO_AUTO_CREATE_USER
该模式决定了是否可以通过 grant 赋权语句创建密码为空的账号。如果设置该模式，则不能通过grant 赋权语句自动的创建密码为空用户，如果指定了密码则可以创建账户。
mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | NO_AUTO_CREATE_USER |
+---------------+---------------------+
1 row in set (0.01 sec)
mysql> grant select on test.* to yanyidba@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant select on test.* to yanyidba@'%' identified by 'youzandba';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings; ### 简化一下输出。
Using GRANT for creating new user is deprecated
and will be removed in future release.
Create new user with CREATE USER statement.
MySQL 8.0.13 以及之后的版本去掉了
NO_AUTO_CREATE_USER，推荐大家使用 create user ，alter user 等标准语法创建维护账户信息。
2.3 IGNORE 和严格模式
当DML语句主要是 insert 带上了 IGNORE 关键字 ，此时SQL MODE 又是 严格模式的情况下，数据库是如何表现的呢？官方给的对比图。
举个例子
mysql>insert ignore into tx(a) values('ab'),('abc'),('abcde');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql>show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql>select * from tx;
+------+
| a |
+------+
| ab |
| abc |
| abc |
+------+
3 rows in set (0.00 sec)
官方的例子
mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'
严格模式下使用ignore关键字，数据可以写入并且提示warning。实际上写入一条记录。
mysql> INSERT IGNORE INTO t (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 1 Warnings: 1
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't.PRIMARY' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
三 小结
本文列举了经常使用的 SQL MODE ，还有其他很多不常见的，大家可以移步官方文档去探索。
之前的文章做了个小调查，600多阅读量，只有45个人填写了调查问卷。(大家参与度不高呀，是因为没有奖品吗？)。
设置严格模式和非严格模式的比例 42:58 ，还是非严格模式的多一点。
其实从自身的角度来看这个结果有一定历史原因，从5.6 升级到5.7 版本 或者升级到8.0版本，默认宽松模式下直接到严格模式下会导致正常的程序代码报错，停止执行，影响业务。导致相当比例的DBA会选择默认宽松模式。
看完本文，希望DBA或者开发同学对 sql_mode 有个进一步的认识，建议对于新申请的数据库选择使用支持事务的存储引擎并且设置为严格模式。
hava fun with the right SQL MODE ^_^
推荐阅读
官方文档对sql_mode 讲解的还是比较详细的。大家可以去看看。
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
https://dev.mysql.com/doc/refman/8.0/en/faqs-sql-modes.html
-The End-
本公众号长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。