使用MySQL，SQL_MODE有哪些坑，你知道么？
SQL_MODE是MySQL中的一个系统变量（variable），可由多个MODE组成，每个MODE控制一种行为，如是否允许除数为0，日期中是否允许'0000-00-00'值。
为什么需要关注SQL_MODE呢？
首先，看三个简单的Demo（MySQL 5.6）。
1.
复制代码
mysql> create table t1(c1 datetime);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t1 values('2019-02-29');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1;
+---------------------+
| c1 |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
复制代码
实际存储值与插入值不符。
2.
复制代码
mysql> create table t2(c1 varchar(10));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t2 values('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+
| c1 |
+------+
| a |
| b |
| c |
+------+
3 rows in set (0.00 sec)
mysql> alter table t2 modify column c1 int;
Query OK, 3 rows affected, 3 warnings (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'c1' at row 1 |
| Warning | 1366 | Incorrect integer value: 'b' for column 'c1' at row 2 |
| Warning | 1366 | Incorrect integer value: 'c' for column 'c1' at row 3 |
+---------+------+-------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| c1 |
+------+
| 0 |
| 0 |
| 0 |
+------+
3 rows in set (0.00 sec)
复制代码
DDL导致原列内容丢失。
3.
复制代码
mysql> create table t3(id int not null,c1 varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t3 values(null,'a');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into t3(c1) values('a');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t3;
+----+------+
| id | c1 |
+----+------+
| 0 | a |
+----+------+
1 row in set (0.00 sec)
复制代码
显式指定列和不显式指定的处理逻辑竟然不一样。
为什么会这样呢？这个即与SQL_MODE有关。
在MySQL 5.6中， SQL_MODE的默认值为"NO_ENGINE_SUBSTITUTION"，非严格模式。
在这种模式下，在进行数据变更操作时，如果涉及的列中存在无效值（如日期不存在，数据类型不对，数据溢出），只会提示"Warning"，并不会报错。
如果要规避上述问题，需开启SQL_MODE的严格模式。
SQL_MODE的严格模式
所谓的严格模式，即SQL_MODE中开启了STRICT_ALL_TABLES或STRICT_TRANS_TAB LES。
还是上面的Demo，看看严格模式下，MySQL的处理逻辑。
复制代码
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('2019-02-29');
ERROR 1292 (22007): Incorrect datetime value: '2019-02-29' for column 'c1' at row 1
mysql> alter table t2 modify column c1 int;
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'c1' at row 1
mysql> insert into t3(c1) values('a');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
复制代码
同样的SQL，在严格模式下，直接提示"ERROR"，而不是"Warning"。
同是严格模式，下面看看STRICT_ALL_TABLES或STRICT_TRAN S_TABLES的区别。
STRICT_ALL_TABLES与STRICT_TRANS_TABLES的区别
STRICT_TRANS_TABLES只对事务表开启严格模式，STRICT_ALL_TABLES是对所有表开启严格模式，不仅仅是事务表，还包括非事务表。
看下面这个测试。
对myisam表插入3条数据，其中，第3条数据是空字符串，与定义的int类型不匹配。
复制代码
mysql> create table t (c1 int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values (1),(2),('');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'c1' at row 3 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| c1 |
+------+
| 1 |
| 2 |
| 0 |
+------+
3 rows in set (0.00 sec)
mysql> set session sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values (1),(2),('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'c1' at row 3
复制代码
可以看到，在表为myisam存储引擎的情况下，只有开启STRICT_ALL_TABLES才会报错。
不同版本默认的SQL_MODE
MySQL 5.5：空
MySQL 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
如何修改SQL_MODE
SQL_MODE既可在全局级别修改，又可在会话级别修改。可指定多个MODE，MODE之间用逗号隔开。
全局级别
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
会话级别
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
SQL_MODE的完整列表
ALLOW_INVALID_DATES
在严格模式下，对于日期的检测较为严格，其必须有效。若开启该MODE，对于month和day的检测会相对宽松。其中，month只需在1~12之间，day只需在1~31之间，而不管其是否有效，如下面的'2004-02-31'。
复制代码
mysql> create table t (c1 datetime);
Query OK, 0 rows affected (0.21 sec)
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values('2004-02-31');
ERROR 1292 (22007): Incorrect datetime value: '2004-02-31' for column 'c1' at row 1
mysql> set session sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values('2004-02-31');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+---------------------+
| c1 |
+---------------------+
| 2004-02-31 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
复制代码
注意，该MODE只适用于DATE和DATETIME，不适用于TIMESTAMP。
ANSI_QUOTES
在MySQL中，对于关键字和保留字，是不允许用做表名和字段名的。如果一定要使用，必须使用反引号（"`"）进行转义。
复制代码
mysql> create table order (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id int)' at line 1
mysql> create table `order` (id int);
Query OK, 0 rows affected (0.12 sec)
复制代码
若开启该MODE，则双引号，同反引号一样，可对关键字和保留字转义。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table "order" (c1 int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" (c1 int)' at line 1
mysql> set session sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> create table "order" (c1 int);
Query OK, 0 rows affected (0.17 sec)
复制代码
需要注意的是，在开启该MODE的情况下，不能再用双引号来引字符串。
ERROR_FOR_DIVISION_BY_ZERO
该MODE决定除数为0的处理逻辑，实际效果还取决于是否开启严格模式。
1. 开启严格模式，且开启该MODE，插入1/0，会直接报错。
复制代码
mysql> create table t (c1 double);
Query OK, 0 rows affected (0.04 sec)
mysql> set session sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values(1/0);
ERROR 1365 (22012): Division by 0
复制代码
2. 只开启严格模式，不开启该MODE，允许1/0的插入，且不提示warning，1/0最后会转化为NULL。
复制代码
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values(1/0);
Query OK, 1 row affected (0.07 sec)
mysql> select * from t;
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
复制代码
3. 不开启严格模式，只开启该MODE，允许1/0的插入，但提示warning。
4. 不开启严格模式，也不开启该MODE，允许1/0的插入，且不提示warning，同2一样。
HIGH_NOT_PRECEDENCE
默认情况下，NOT的优先级低于比较运算符。但在某些低版本中，NOT的优先级高于比较运算符。
看看两者的区别。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select not 1 < -1;
+------------+
| not 1 < -1 |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> set session sql_mode='HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)
mysql> select not 1 < -1;
+------------+
| not 1 < -1 |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
复制代码
在sql_mode为空的情况下， not 1 < -1相当于not (1 < -1)，如果设置了'HIGH_ NOT_PRECEDENCE'，则相当于(not 1) < -1。
IGNORE_SPACE
默认情况下，函数名和左括号（“(”）之间不允许存在空格。若开启该MODE，则允许。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select count (*) from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from t' at line 1
mysql> set session sql_mode='IGNORE_SPACE';
Query OK, 0 rows affected (0.01 sec)
mysql> select count (*) from t;
+-----------+
| count (*) |
+-----------+
| 2 |
+-----------+
1 row in set (0.01 sec)
复制代码
NO_AUTO_VALUE_ON_ZERO
默认情况下，在对自增主键插入NULL或0时，会自动生成下一个值。若开启该MODE，当插入0时，并不会自动生成下一个值。
如果表中自增主键列存在0值，在进行逻辑备份还原时，可能会导致数据不一致。所以mysqldump在生成备份数据之前，会自动开启该MODE，以避免数据不一致的情况。
复制代码
mysql> create table t (id int auto_increment primary key);
Query OK, 0 rows affected (0.11 sec)
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (0);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> set session sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values (0);
Query OK, 1 row affected (0.09 sec)
mysql> select * from t;
+----+
| id |
+----+
| 0 |
| 1 |
+----+
2 rows in set (0.00 sec)
复制代码
NO_BACKSLASH_ESCAPES
默认情况下，反斜杠“\”会作为转义符，若开启该MODE，则反斜杠“\”会作为一个普通字符，而不是转义符。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> select '\\t';
+----+
| \t |
+----+
| \t |
+----+
1 row in set (0.00 sec)
mysql> set session sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)
mysql> select '\\t';
+-----+
| \\t |
+-----+
| \\t |
+-----+
1 row in set (0.00 sec)
复制代码
NO_DIR_IN_CREATE
默认情况下，在创建表时，可以指定数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY），若开启该MODE，则会忽略这两个选项。在主从复制场景下，可在从库上开启该MODE。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> create table t (id int) data directory '/tmp/';
Query OK, 0 rows affected (0.15 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/tmp/'
1 row in set (0.00 sec)
mysql> set session sql_mode='NO_DIR_IN_CREATE';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.11 sec)
mysql> create table t (id int) data directory '/tmp/';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
复制代码
NO_ENGINE_SUBSTITUTION
在开启该MODE的情况下，在创建表时，如果指定的存储引擎不存在或不支持，则会直接提示“ERROR”。
若不开启，则只会提示“Warning”，且使用默认的存储引擎。
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (id int) engine=federated;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1286 | Unknown storage engine 'federated' |
| Warning | 1266 | Using storage engine InnoDB for table 't' |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.11 sec)
mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (id int) engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'
复制代码
NO_UNSIGNED_SUBTRACTION
两个整数相减，如果其中一个数是无符号位，默认情况下，会产生一个无符号位的值，如果该值为负数，则会提示“ERROR”。如，
复制代码
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned)-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
复制代码
若开启该MODE，则允许结果为负数。
复制代码
mysql> set session sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned)-1;
+-----------------------+
| cast(0 as unsigned)-1 |
+-----------------------+
| -1 |
+-----------------------+
1 row in set (0.00 sec)
复制代码
NO_ZERO_DATE
该MODE会影响'0000-00-00'的插入。实际效果还取决于是否开启严格模式。
1. 在开启严格模式，且同时开启该MODE，是不允许'0000-00-00'插入的。
复制代码
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warnin