学习 sql_mode 的不同的值对数据库操作的数据的影响。

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 默认设置越来越严格。

比较重要的SQL MODE 值

首先说一下严格模式的定义。严格模式是 指将 SQL_MODE 变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一个或者两个同时设置。

STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 对于事务表的影响是一样的。区别在于非事务表涉及到多行记录变更时的表现。下面我们通过例子来阐述两者之间的差异。

(1) 、STRICT_TRANS_TABLES

在该模式下对INSERT ,UPDATE 的影响和变更的记录条数有关。

当第一个行数据非法，事务表和非事务表都直接报错，不会有记录插入成功。

当第二条记录以及之后数据非法时，事务表直接报错，非事务表可以继续插入但是报warning，数据被截断。(如果是其他类型的值MySQL则会转换回最接近的默认值)。

单独设置 sql_mode 为 STRICT_TRANS_TABLES 会提示warning，NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.

建议和严格模式一起设置，这样数据才会更安全。

(2) 、STRICT_ALL_TABLES

当第一个行数据非法，事务表和非事务表都直接报错，不会有记录插入成功。

当第二条记录以及之后数据非法时，事务表和非事务表都直接报错，但是非事务表是有部分记录插入成功的。非法的数据被丢弃而不是截断或者存储其他默认值。

(3) 、NO_ENGINE_SUBSTITUTION

MySQL 创建表的时候通过指定 engine 的值来选择存储引擎。如果指定的存储引擎不存在呢(比如某些存储引擎没有编译)

如果 sql_mode 设置NO_ENGINE_SUBSTITUTION ，指定不存在的存储引擎会直接报错。

如果 sql_mode 没有设置 NO_ENGINE_SUBSTITUTION ，指定不存在的存储引擎会被替换为默认的存储引擎。

测试数据库不支持 FEDERATED 存储引擎，则自动替换为默认的 InnoDB 存储引擎。

该模式相当于宽松模式，不涉及表里面数据的校验方式和sql语法控制。

(4) 、ONLY_FULL_GROUP_BY

设置该模式时，对于 GROUP BY 聚合操作，如果 GROUP BY的列没有在 select 中出现，那么这个 SQL 是不合法的。

(5) 、NOAUTO_VALUE_ON_ZERO

该模式影响自增值，不设置该模式，通过向自增字段插入 null或者0 值时，自增字段会产生一个自增值。设置该模式之后，只能通过插入 null 值产生自增或者不插入对应的值。插入0值存储的还是0 而非下一个自增值。

不设置该模式

设置该模式

(6)、NO_ZERO_IN_DATE

该模式决定了date类型的值(年月日)中，年不是0值，日期和月份是否可以是0值的情况。比如 2020-00-23 ，2020-00-00 这样的值是否可以成为合法的日期值。该模式的对数据合法性的影响依然和是否设置了 严格模式有关。

如果没有设置该模式，则允许设置日期和月份为0的值而且insert的时候 不产生warning。

如果设置该模式，则不允许设置日期和月份为0的值而且insert的时候产生warning，存储的记录为'0000-00-00'。

当同时设置严格模式和NO_ZERO_IN_DATE时，写入类似2020-00-23 2020-00-00的值会导致报错。

在严格模式下，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

(7)、NO_ZERO_DATE

该模式决定了 '0000-00-00' 是否可以成为合法的日期。它的作用和是否开启 严格模式有关。

如果没有设置 NO_ZERO_DATE，'0000-00-00' 可以当做合法的日期而且insert时不会有warning。

如果设置了NO_ZERO_DATE，'0000-00-00' 可以当做合法的日期而且insert时会有warning。

同时设置严格模式和NO_ZERO_DATE，'0000-00-00' 是一个非法的日期值。插入时会直接报错。但是如果用IGNORE选项可以插入零日期，但是会生成警告。

严格模式下，使用insert 插入'0000-00-00' 直接报错。

严格模式下，使用insert ignore 插入'0000-00-00'则可以插入成功，但是提示warning。

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。

总结

本文列举了经常使用的 SQL MODE ，还有其他很多不常见的，大家可以移步官方文档去探索。

其实从自身的角度来看这个结果有一定历史原因，从5.6 升级到5.7 版本 或者升级到8.0版本，默认宽松模式下直接到严格模式下会导致正常的程序代码报错，停止执行，影响业务。导致相当比例的DBA会选择默认宽松模式。

看完本文，希望DBA或者开发同学对 sql_mode 有个进一步的认识，建议对于新申请的数据库选择使用支持事务的存储引擎并且设置为严格模式。