深入了解MySQL SQL Mode，掌握不同模式的用法和最佳实践，提升数据库管理效率。
原文标题：数据库系列之MySQL中的SQL Mode介绍
原文作者：牧羊人的方向
冷月清谈：
怜星夜思：
2、文章中提到的IGNORE关键字可以忽略多种错误类型，那么在实际应用中，使用IGNORE关键字的潜在风险是什么？如何规避这些风险？
3、文章最后提到了核心业务系统SQL Mode的最佳实践设置，这个设置是通用的吗？在不同的业务场景下，是否需要根据实际情况进行调整？
原文内容
SQL mode定义了不同的SQL语法操作，本文简要介绍了MySQL中的SQL mode类型及使用，结合具体案例使用加深理解，适用于兼容MySQL语法的分布式数据库。
1、Server SQL Mode介绍
MySQL Server可以运行在不同的SQL Modes下，每一类SQL Modes定义MySQL应该支持什么样的SQL语法。DBA可以设置系统级别的SQL mode，每个应用也可以根据需要设置session级别的SQL mode。在MySQL 8.0版本中默认设置以下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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-
ANSI更接近于标准SQL，等同于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE和ONLY_FULL_GROUP_BY
-
STRICT_TRANS_TABLES：如果无法按给定的方式将值插入到事务表中，请中止该语句。对于非事务表，如果值出现在单行语句或多行语句的第一行中，则中止该语句
-
TRADITIONAL：使MySQL的行为像一个“传统”的SQL数据库系统，在向列中插入错误值时，此模式“给出错误而不是警告”。等同于STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO和NO_ENGINE_SUBSTITUTION
双引号””不能作为引用字符串，而是会解释为标识符。
mysql> set @@session.sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)
mysql> select "test" from tango.test01;
+------+
| test |
+------+
| test |
| test |
| test |
+------+
3 rows in set (0.02 sec)
设置sql_mode为ansi_quotes，可以看到test标识为column列名进行查询
mysql> set @@session.sql_mode='ansi_quotes';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ANSI_QUOTES |
+--------------------+
1 row in set (0.00 sec)
mysql> select "test" from tango.test01;
ERROR 1054 (42S22): Unknown column 'test' in 'field list'
-
当不设置ERROR_FOR_DIVISION_BY_ZERO，除以0或以0取模的结果为null，不产生告警；
-
当设置ERROR_FOR_DIVISION_BY_ZERO，除以0或以0取模的结果为null，并产生告警；
-
当设置ERROR_FOR_DIVISION_BY_ZERO和严格模式，除以0或以0取模会产生报错，除非insert和update配合ignore适用；
-
对于select查询，除以0或以0取模在任何情况下都会返回null，如果设置ERROR_FOR_DIVISION_BY_ZERO，则会产生warnings；
ERROR_FOR_DIVISION_BY_ZERO并不包含在严格模式里，但这个选项即将被废弃，在未来的版本会合并到严格模式里。
NOT操作的顺序，比如NOT a BETWEEN b AND c被解释为NOT (a BETWEEN b AND c)。
mysql> set @@session.sql_mode='';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
| 0 |
+------------------------+
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
允许函数名和()之间有空格，这会导致内置的函数为关键字。只是对于内置函数而言，对于用户定义的函数，总是允许和()之间有空格。
mysql> set @@session.sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.10 sec)
当设置ignore_space后
mysql> set @@session.sql_mode='ignore_space';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE count (i 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 'count (i INT)' at line 1
mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.10 sec)
由于count是内置函数，创建表名为count时需要加标识符
当两个整数相减时，其中一个unsigned，会默认生成unsigned的结果。但是如果结果是负数，则会出错。
mysql> set @@session.sql_mode='';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
如果设置了NO_UNSIGNED_SUBTRACTION，结果如下：
mysql> SET 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)
如果两个整数相减的结果用来更新一个unsigned列，默认情况下是该列数据类型的最大值，如果开启了NO_UNSIGNED_SUBTRACTION，则设置为0。但是在strict SQL mode下，列值不会变化，并且会报错。
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE t1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| c2 | bigint unsigned | NO | | 0 | |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE t2;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| c2 | bigint | NO | | 0 | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
对于group by聚合操作，如果在select中出现的列没有在group by中出现，那么这种SQL是不合法的。
##创建表并插入数据
mysql> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b VARCHAR(10),
-> c INT
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO mytable
-> VALUES (1, 'abc', 'qrs', 1000),
-> (2, 'abc', 'tuv', 2000),
-> (3, 'def', 'qrs', 4000),
-> (4, 'def', 'tuv', 8000),
-> (5, 'abc', 'qrs', 16000),
-> (6, 'def', 'tuv', 32000);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
##设置SQL MODE为ONLY_FULL_GROUP_BY
mysql> SET sql_mode='ONLY_FULL_GROUP_BY';
mysql> select a,b,max(c) from mytable group by a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tango.mytable.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select a,b,max(c) from mytable group by a,b;
+------+------+--------+
| a | b | max(c) |
+------+------+--------+
| abc | qrs | 16000 |
| abc | tuv | 2000 |
| def | qrs | 4000 |
| def | tuv | 32000 |
+------+------+--------+
4 rows in set (0.00 sec)
默认情况下，列类型为char会裁剪掉，但是如果设置了PAD_CHAR_TO_FULL_LENGTH，CHAR类型的列会使用空Ox20进行填充。
mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.00 sec)
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1 | CHAR_LENGTH(c1) |
+------+-----------------+
| xy | 2 |
+------+-----------------+
1 row in set (0.00 sec)
设置SQL Mode为PAD_CHAR_TO_FULL_LENGTH后
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1 | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy | 10 |
+------------+-----------------+
1 row in set (0.00 sec)
当date、time或timestamp中带有飞秒值时根据列的精度是truncate还是rounding，默认是使用rounding，设置该SQL mode时候会使用truncate。
mysql> CREATE TABLE t2 (id INT, tval TIME(1));
Query OK, 0 rows affected (0.02 sec)
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id, tval) VALUES(1, 1.55);
Query OK, 1 row affected (0.01 sec)
mysql> SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (id, tval) VALUES(2, 1.55);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, tval FROM t2 ORDER BY id;
+------+------------+
| id | tval |
+------+------------+
| 1 | 00:00:01.6 |
| 2 | 00:00:01.5 |
+------+------------+
2 rows in set (0.00 sec)
2、Strict SQL mode
Strict mode控制着MySQL在update/insert时候对于invalid或missing数据的处理。插入或更新的值invalid有多种原因，比如数据类型错误、值越界等；值缺失是因为向NOT NULL列插入数据的时候没有显示的指定DEFAULT语句。Strict mode也会影响到create table语句。而对于strict mode下的select语句，只会生成一个warning，不会报错。
-
对于业务的表，在两种模式下当insert/update语句出现了invalid或missing values会报错，语句会中断并回滚
-
对于非业务的表，对于insert/update第一行数据的时候跟业务表有同样的行为，语句出错并回滚。但是对于多行记录，并且出错的不在第一行记录的时候，会有所不同
-
对于STRICT_ALL_TABLES，mysql会报错并忽略余下所有的记录数，这样就会造成有部分成功了。为了避免这个问题，可以使用single-row语句。
-
对于STRICT_TRANS_TABLES，mysql会把invalid值转换为最接近的valid值并插入；如果值缺失了，会插入默认的值。同时mysql会产生一个warning并继续处理下面的操作
从以上可以看出，当同时设置strict SQL mode和IGNORE下，IGNORE优先级更高
在mysql中有些语句支持IGNORE关键字，它会将某些类型的错误降级并产生warning来替代。对于multi-row语句，错误降级为warning可以继续处理接下去的语句，同时IGNORE也会跳过出错的rows。
如下表t3中的主键包含唯一性约束，当插入相同的数据时会提示重复键值。
mysql> CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY);
mysql> INSERT INTO t3 (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't3.PRIMARY'
加入IGNORE参数后，重复值依然不会插入，但是会报一个warning，语句并没有出错中断
mysql> INSERT IGNORE INTO t3 (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 1 Warnings: 1
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't3.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
在MySQL中以下语句支持IGNORE关键字：CREATE TABLE ... SELECT、DELETE、INSERT、LOAD DATA和UPDATE。IGNORE关键字会忽略以下错误：
ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED
在Strict SQL mode下，会将特定的warning升级为error。比如在non-strict mode下，插入字符串到整数列会将值转为0，并产生warning。
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t3 (i) VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t3;
+---+
| i |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)
如果在strict mode下，invalid value会被拒绝并且报错：
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> INSERT INTO t3 (i) VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1
Strict SQL mode在值invalid或缺失的时候会产生以下错误信息：
ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED
以上是SQL Mode的简单介绍，在核心业务系统中，根据最佳实践SQL mode设置为ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, PIPES_AS_CONCAT, IGNORE_SPACE。
参考资料：
-
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
-
https://blog.csdn.net/weixin_39004901/article/details/89378097
-
https://www.modb.pro/db/24613