概述​

sql_mode 是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

sql_model 常用来解决下面几类问题：

1. 通过设置 sql_mode, 可以完成不同严格程度的数据校验，有效地保障数据完整性。
2. 通过设置 sql_model 为宽松模式，来保证大多数 sql 符合标准的 sql 语法，这样应用在不同数据库之间进行迁移时，则不需要对业务 sql 进行较大的修改。
3. 在不同数据库之间进行数据迁移之前，通过设置 sql_mode 可以使 MySQL 上的数据更方便地迁移到目标数据库中。

​

常用取值

sql_mode 常用值如下：

• ONLY_FULL_GROUP_BY：对于 GROUP BY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，那么这个 SQL 是不合法的；
• NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，那么这个选项就有用了；
• STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制；
• NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零；
• NO_ZERO_DATE：设置该值，MySQL 数据库不允许插入零日期，插入零日期会抛出错误而不是警告；
• ERROR_FOR_DIVISION_BY_ZERO：在 INSERT 或 UPDATE 过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时 MySQL 返回 NULL；
• ​NO_AUTO_CREATE_USER：禁止 GRANT 创建密码为空的用户；
• NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常；
• PIPES_AS_CONCAT：将 || 视为字符串的连接操作符而非或运算符，这和 Oracle 数据库是一样的，也和字符串的拼接函数 concat 相类似；
• ANSI_QUOTES：启用 ANSI_QUOTES 后，不能用双引号来引用字符串，因为它被解释为识别符；

宽松与严格模式

MySQL 5.6 和 MySQL 5.7 默认的 sql_mode 模式参数是不一样的，5.6 的是 NO_ENGINE_SUBSTITUTION，其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。5.7 的是 STRICT_TRANS_TABLES，也就是严格模式。

如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。
例如：我在创建一个表时，该表中有一个字段为 name，给 name 设置的字段类型时 char(10)，如果我在插入数据的时候，其中 name 这个字段对应的有一条数据的长度超过了 10，例如 '1234567890abc'，超过了设定的字段长度 10，那么不会报错，并且取前十个字符存上。也就是说你这个数据被存为了 '1234567890'，而 'abc' 就没有了，但是我们知道，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且 MySQL 自行处理并接受了，这就是宽松模式的效果。

其实在开发、测试、生产等环境中，我们应该采用的是严格模式，出现这种错误，应该报错才对，所以 MySQL 5.7 版本就将 sql_mode 默认值改为了严格模式，并且我们即便是用的 MySQL 5.6，也应该自行将其改为严格模式。

查看与设定

可通过如下语句查看当前生效的 sql_mode：

-- 查看会话级别的 sql_mode
mysql> select @@sql_mode;
-- 查看全局级别的 sql_mode
mysql> select @@global.sql_mode;

可通过如下语句设定当前生效的 sql_mode：

-- 会话级别的设定 session 可省略
mysql> set session sql_mode='值'
-- 全局级别的设定
mysql> set global sql_mode='值'

永久生效

要让指定的 sql_mode 设定永久生效，只需要在 my.cnf 中的 [mysqld] 节下添加 sql_mode 配置即可，看下面示例。
​
如果当前使用 MySQL 但希望遵循 Oracle 的 SQL 规范，可以​在 my.cnf 添加如下对 MySQL 的 sql_mode 设置:

[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

当然，此种配置需要重启生效。

参考：

• https://www.cnblogs.com/he-qing-qing/p/11233443.html