深入理解 MySQL 中的 SQL_MODE

SQL_MODE 是 MySQL 数据库中的一个系统变量，用于控制 MySQL 如何处理 SQL 语句和数据校验。它可以看作是一组约束和规范，确保数据的准确性、完整性和一致性。例如，可以控制如何处理无效日期、是否允许插入不完整的记录、是否区分大小写等。

作者：许祥，爱可生 MySQL DBA 团队成员，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题。

出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 2600 字，预计阅读需要 8 分钟。

什么是 SQL_MODE？

SQL_MODE 是 MySQL 数据库中的一个系统变量，用于控制 MySQL 如何处理 SQL 语句和数据校验。它可以看作是一组约束和规范，确保数据的准确性、完整性和一致性。例如，可以控制如何处理无效日期、是否允许插入不完整的记录、是否区分大小写等。

在 MySQL 中，通过设置 SQL_MODE 可以解决下面几类问题：

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

通过阅读本文，你将收获以下知识点：

1. MySQL 不同版本的默认 SQL_MODE 值
2. 常用的 SQL_MODE 设置及其含义
3. SQL_MODE 在数据迁移中的关键作用

SQL_MODE 有哪些值？

MySQL 5.7 的默认值和描述

• ONLY_FULL_GROUP_BY：在 GROUP BY 子句中没有出现的列，若出现在 SELECT 列表、HAVING 条件、ORDER BY 条件中时会被拒绝。

• STRICT_TRANS_TABLES：非法日期，超过字段长度的值插入时，直接报错，拒绝执行。例如，如果向一个整数列插入超出范围的值，将触发错误。

• NO_ZERO_DATE：针对日期 ‘0000-00-00’，执行逻辑如下：

  1. disable：可以正常插入，没有警告。

  2. enable：可以正常插入，有警告。

     如果 SQL_MODE 中包含 STRICT TRANS TABLES，则日期被拒绝写入，但可以通过加 IGNORE 关键字写入 ‘0000-00-00’，有警告。

• NO_ZERO_IN_DATE：日期中针对月份和日期部分，如果为0，比如 ‘2024-00-00’，有不同的执行逻辑：

  1. disable：可以正常插入，实际插入值还是 ‘2024-00-00’ 没有警告。

  2. enable：可以正常插入，有警告。

     如果 SQL_MODE 中包含STRICT TRANS TABLES 则日期被拒绝写入，但可以通过加 IGNORE 关键字写入 ‘0000-00-00’。

• ERROR_FOR_DIVISION_BY_ZERO：除数为 0（包括 MOD(N,0)），执行逻辑如下：

  1. disable：插入 NULL，没有警告。

  2. enable：插入 NULL，有警告。

     如果 SQL_MODE 中包含 STRICTTRANSTABLES，则数据被拒绝写入，但可以通过加 IGNORE 关键字写入 NULL，有警告。

• NO_AUTO_CREATE_USER：防止使用不带密码子句的 GRANT 语句来创建一个用户。

• NO_ENGINE_SUBSTITUTION：执行 CREATE TABLE 或者 ALTER TABLE 语句时，如果指定的存储引擎不可用，MySQL 会抛出错误，而不是使用默认存储引擎。

  1. disable：CREATE TABLE 会自动替换后执行，ALTER TABLE 不会执行，两个命令都有警告。
  2. enable