MySQL 数据库 sql_mode

在 MySQL 中，sql_mode 是一个系统变量，用于控制 SQL 语法和数据库行为的特性。不同的 sql_mode 设置可以影响数据库查询的执行方式、数据验证、错误处理等。设置正确的 sql_mode 可以提高 SQL 查询的安全性和准确性，防止潜在的错误和不一致的数据状态。

默认的 sql_mode 在不同版本的 MySQL 中可能有所不同。比如在 5.7.43 中，默认是 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

1. ONLY_FULL_GROUP_BY
强制要求所有在 SELECT 语句中未被聚合函数（count,sum,avg,max,min,group_concat等函数用于对一组值进行计算并返回单个值）包围的列，必须在 GROUP BY 子句中列出。这有助于避免模糊查询，确保 SQL 查询的准确性。
  
2. STRICT_TRANS_TABLES
启用严格模式。在插入或更新数据时，如果数据不符合列的定义（例如，数据类型不匹配或超出范围），则会产生错误，事务将被回滚，而不是自动截断或转换数据类型。

3. NO_ZERO_IN_DATE
禁止插入日期包含零的值（如 '2021-00-01' 或 '2021-01-00'）。如果尝试插入这样的日期，将会导致错误，而不是将其视为有效日期。

4. NO_ZERO_DATE
禁止插入零日期（如 '0000-00-00'）。如果插入这样的值，将会导致错误。这个设置有助于确保日期数据的有效性。

5. ERROR_FOR_DIVISION_BY_ZERO
在执行除以零的操作时，MySQL 将返回错误，而不是返回 NULL。这有助于在处理数学运算时确保数据的准确性。

6. NO_AUTO_CREATE_USER
在使用 GRANT 语句时，如果指定的用户不存在，MySQL 不会自动创建该用户。这可以防止意外创建用户，增加安全性。

7. NO_ENGINE_SUBSTITUTION
如果请求的存储引擎不可用，MySQL 将抛出错误，而不是使用默认存储引擎。此设置确保使用者明确其选择的存储引擎。

在 MySQL 中，STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES 都是严格模式（Strict SQL Mode）的变体，用于控制数据写入时的校验行为，但它们的生效范围和处理方式有重要区别。以下是两者的对比分析：

1. 核心区别

2. 具体行为差异

(1) 对事务型表（如 InnoDB）

• 两者行为一致：

  • 若插入数据违反规则（如超长、非法值、违反 NOT NULL 约束），会立即报错并回滚整个语句（非整个事务）。
  • 例如：

  INSERT INTO inno_table (text_column) VALUES (NULL);  -- 若字段是 NOT NULL，直接报错

  • 例如TEXT类型字段（65,535 bytes (~64KB)）超长的报错信息：SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'desc' at row 1。

(2) 对非事务型表（如 MyISAM）

• STRICT_TRANS_TABLES：

  • 非事务表的插入错误会静默警告（如数据截断），可能部分成功。
  • 例如：

  INSERT INTO myisam_table (text_column) VALUES (REPEAT('a', 65536));
  -- 超长数据会被截断，生成警告而非报错（非严格行为）

• STRICT_ALL_TABLES：

  • 非事务表的插入错误会立即报错，并停止当前语句的后续操作（已插入的行可能保留）。
  • 例如：

  INSERT INTO myisam_table (text_column) VALUES ('valid'), (NULL), ('valid');
  -- 遇到 NULL 值直接报错，但第一行 'valid' 可能已插入（部分写入）

  • 注意：超长的字段插入后数据会被截断，导致字段保存的数据不完整。

4. 如何选择？

• 优先 STRICT_TRANS_TABLES：
  • 若仅使用事务引擎（如 InnoDB），两者效果相同，但 STRICT_TRANS_TABLES 是 MySQL 5.7+ 的默认选项。
• 使用 STRICT_ALL_TABLES：
  • 需要确保非事务表也严格校验时（如混合引擎环境）。
  • 注意：部分写入问题可能需额外处理（如批量插入时部分成功）。

5. 与其他模式的组合

常见的严格模式组合（如 MySQL 5.7+ 默认）：

SET sql_mode = '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';

• 此配置下，事务表严格校验，非事务表宽松处理。

如果需要全局严格，可替换为 STRICT_ALL_TABLES。

总结

• 严格性：STRICT_ALL_TABLES ≥ STRICT_TRANS_TABLES。
• 关键差异体现在对非事务型表的处理方式。
• 生产环境建议至少启用 STRICT_TRANS_TABLES，避免数据静默截断或污染。

--