本文章sql_mode参数使用及释义基于MySQL 8.0及以上版本
sql_mode定义了MySQL CBO对特定SQL执行的对应动作，如果时常发生测试环境编写的SQL可以正常运行，但是迁移到生产环境上，运行报错的情况，在数据库大版本一致的情况下，首要确定sql_mode是否一致。
root@localhost:[mysql] 12:25:18> 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_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.83 sec)
sql_mode的两种模式：
sql_mode分为宽松模式和严格模式两种，两种模式搭配sql_mode参数是否配置，分别对应1-4种对应动作（一般为2-3种）
- 设置严格模式（设置参数STRICT_TRANS_TABLES/STRICT_ALL_TABLES）
root@localhost:[mysql] 12:29:25> set session sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost:[mysql] 12:29:28> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)
- 设置宽松模式（不设置STRICT_TRANS_TABLES/STRICT_ALL_TABLES）
root@localhost:[mysql] 12:29:30> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost:[mysql] 12:30:48> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
sql_mode各参数及释义
作用
| 使能严格模式
|
设置参数
| 使能所有存储引擎的严格模式 |
不设置参数
| 宽松模式
|
备注
| 可能会有歧义，设置该参数会至少使能myisam/innodb存储引擎的严格模式，其他模式未测试，未知效果
|
作用
| 使能严格模式
|
设置参数
| 使能事务表存储引擎(innodb)的严格模式 |
不设置参数
| 宽松模式
|
备注
|
|
作用
| 检查date和datatime类型，根据输入的时间范围，确认可以时间是否合法
|
设置参数
| 只检查 月 between 1 and 12，日 between 1 and 31，无需检查年，如'0000-12-13'，不报错 |
不设置参数 + 严格模式
| 非法时间，如date '2021-02-31' 都将会转换为'0000-00-00'插入，报warning |
不设置参数 + 宽松模式
| 所有非法时间都将无法插入，报错 |
适用范围
| 多适用于web app，年/月/日 分属三个不同的feild，并且数据无需准确，如日记本等应用 |
备注
|
|
作用
| 检查date和datatime类型，根据输入的时间范围，确认可以时间是否合法
|
设置参数 + 严格模式
| 年月日都不能为0，并error报错退出 |
设置参数 + 宽松模式
| 年月日可以为0，如date '0000-00-00'，数据可以插入，有warning |
不设置参数
| 可以插入，无warning |
备注
|
|
作用
| 检查date和datatime类型，根据输入的时间范围，确认可以时间是否合法
|
设置参数 + 严格模式
| 年月日都不能为0，并error报错退出 |
设置参数 + 宽松模式
| 年不能为0，但是月日可以为0，如date '2010-00-14'/date '2010-01-00'，有warning |
不设置参数
| 可以插入，无warning |
备注
|
|
作用
| 单列group by，在汇聚函数中，可以只选择某个列作为group by子句，而无需group by 所有的列。
如：select id,name,sum(*) from t group by id;
|
设置参数
| SQL严格要求select full column + group by full column
|
不设置参数
| SQL允许select full cloumn + group by single column
|
备注
| 8.0以上版本可以配置该参数，如果想使用单列group by，group by 后的列必须为主键，否则sql出数不准
为了避免sql兼容性等问题，建议设置该参数，不使用单列group by
|
作用
| 符号转义
|
设置参数
| 双引号(")，作为识别符(`)的同义词使用，不再用于引用字符串 |
不设置参数
| 双引号(")，作为应用字符串使用 |
备注
|
|
作用
| 符号转义
|
设置参数
| 转义符(\)作为普通字符使用 |
不设置参数
| 转义符(\)，作为应用字符串使用 |
备注
| 设置后，会导致mysql_real_escape_string()函数失败，使用mysql_real_escape_string_quote()函数替换 |
作用
| 符号转义
|
设置参数
| 管道符(||)，作为连接函数concat的同义词 |
不设置参数
| 管道符(||)，作为or运算符，使用 |
备注
| 设置后，会导致mysql_real_escape_string()函数失败，使用mysql_real_escape_string_quote()函数替换 |
作用
| 建表时，表名和数据库函数同名的话，是否需要`进行应用创建 |
设置参数
| 表名需要带(`)
|
不设置参数
| 表名不需要带(`)
|
备注
| 无需设置，没啥用
|
作用
| 建表时，是否需要设置index_directory及data_directory |
设置参数
| 建表时，需要指定index_directory及data_directory |
不设置参数
| 建表时，不需要指定index_directory及data_directory |
备注
| 不要设置，设置后，分区表建表SQL有歧义bug |
作用
| create table/alter table时，指定存储引擎，并此存储引擎属于禁用或者未编译状态时，对应的动作 |
设置参数
| 终止操作，error报错退出 |
不设置参数
| create table正常运行，有warning
alter table不操作，有warning |
备注
|
|
ERROR_FOR_DIVISION_BY_ZERO
作用
| 影响update/insert操作，除数是否为0对应的操作 |
设置参数 + 宽松模式
| 除数为0时，插入NULL值，报warning
|
| 设置参数 + 严格模式 | 除数为0时，直接error报错退出
|
| 不设置参数 | 除数为0时，插入NULL值，不报错 |
备注
| 也会影响mod(n,0)等函数
|
作用
| not运算符的操作 |
设置参数 + 宽松模式
| 解析为not (a between b and c) ：例子 select not 1 between 0 and 2; ##返回0 |
| 不设置参数 | 解析为(not a ) between b and c ：例子 select not 1 between 0 and 2; ##返回1 |
备注
| 主要用于兼容部分老版本SQL，不建议配置 |
作用
| 影响自增长列的插入 |
设置参数
| 插入NULL可以自动生成一个自增成列值
|
不设置参数
| 插入NULL或者0都可以自动生成一个自增长列值
|
备注
|
|
作用
| 两个整型做减法时，其中一个如果为unsigned时的算法 |
设置参数
|
|
不设置参数
|
|
备注
| 没看懂说明，没见过配置该参数的生产数据库，详情请参考：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#no_unsigned_subtraction |
作用
| 对于char数据类型，select时，是否会将char中填充的字符trim掉 |
设置参数
| 不会trim掉填充字符，会影响特定函数的准确，如length等 |
不设置参数
| 自动trim掉填充字符 |
备注
| 官方不建议设置，有可能在更高的版本被移除 |
作用
| 数据类型转换
|
设置参数
| 将real视为float的同义词 |
不设置参数
| 将real视为double的同义词 |
备注
| 首先尽量不要使用浮点型运算，可以使用整形+运算，计算浮点
其次，Mysql最后都会将浮点型的数据类型转换为double后，进行运算
|
作用
| date / time / timestamp数据类型，当插入时间时，对浮点小数位，进行的操作类型 |
设置参数
| 使用truncate处理s的小数浮点位 |
不设置参数
| 使用round处理s的小数浮点位 |
备注
|
|
sql_mode角色：
一组sql_mode参数的集合，8.0版本目前有以下两个角色
角色名
| 包含参数
|
|---|
| ANSI | REAL_AS_FLOAT，PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，ONLY_FULL_GROUP_BY |
| TRADITIONAL | STRICT_TRANS_TABLES，STRICT_ALL_TABLES，NO_ZERO_IN_DATE |
参考文档：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
