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
1.1 ANSI_QUOTES
双引号””不能作为引用字符串，而是会解释为标识符。
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'
1.2 ERROR_FOR_DIVISION_BY_ZERO
当不设置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并不包含在严格模式里，但这个选项即将被废弃，在未来的版本会合并到严格模式里。
1.3 HIGH_NOT_PRECEDENCE
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)
1.4 IGNORE_SPACE
允许函数名和()之间有空格，这会导致内置的函数为关键字。只是对于内置函数而言，对于用户定义的函数，总是允许和()之间有空格。
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 1mysql> CREATE TABLE `count` (i INT);Query OK, 0 rows affected (0.10 sec)
由于count是内置函数，创建表名为count时需要加标识符
1.5 NO_UNSIGNED_SUBTRACTION
当两个整数相减时，其中一个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: 0mysql> 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: 0mysql> DESCRIBE t2;+-------+--------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| c2 | bigint | NO | | 0 | |+-------+--------+------+-----+---------+-------+1 row in set (0.00 sec)
1.6 ONLY_FULL_GROUP_BY
对于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_BYmysql> 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_bymysql> 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)
1.7 PAD_CHAR_TO_FULL_LENGTH
默认情况下，列类型为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)
1.8 TIME_TRUNCATE_FRACTIONAL
当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并继续处理下面的操作
2.1 IGNORE和Strict SQL mode对比
从以上可以看出，当同时设置strict SQL mode和IGNORE下，IGNORE优先级更高
2.1.1 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: 1mysql> 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_ERRORER_DUP_ENTRYER_DUP_ENTRY_WITH_KEY_NAMEER_DUP_KEYER_NO_PARTITION_FOR_GIVEN_VALUEER_NO_PARTITION_FOR_GIVEN_VALUE_SILENTER_NO_REFERENCED_ROW_2ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SETER_ROW_IS_REFERENCED_2ER_SUBQUERY_NO_1_ROWER_VIEW_CHECK_FAILED
2.1.2 Strict SQL Mode的影响
在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_ERRORER_CUT_VALUE_GROUP_CONCATER_DATA_TOO_LONGER_DATETIME_FUNCTION_OVERFLOWER_DIVISION_BY_ZEROER_INVALID_ARGUMENT_FOR_LOGARITHMER_NO_DEFAULT_FOR_FIELDER_NO_DEFAULT_FOR_VIEW_FIELDER_TOO_LONG_KEYER_TRUNCATED_WRONG_VALUEER_TRUNCATED_WRONG_VALUE_FOR_FIELDER_WARN_DATA_OUT_OF_RANGEER_WARN_NULL_TO_NOTNULLER_WARN_TOO_FEW_RECORDSER_WRONG_ARGUMENTSER_WRONG_VALUE_FOR_TYPEWARN_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
