sql_mode 是 MySQL 中一个关键的系统变量，用于控制 SQL 语法解析、数据校验和存储行为的严格程度。合理配置 sql_mode 可以确保数据一致性、兼容性和安全性。以下是详细的配置说明及操作指南：
一、sql_mode 的核心作用
语法校验：控制 SQL 语句的语法规则（如是否允许省略
GROUP BY中的非聚合字段）。数据校验：严格检查数据的合法性（如日期格式、除零操作）。
兼容性：调整 MySQL 行为以兼容其他数据库（如 Oracle 或 SQL Server）。
安全性与性能：通过严格模式避免脏数据写入，提升数据质量。
二、常见的 sql_mode 模式
| 模式名称 | 说明 |
|---|---|
STRICT_ALL_TABLES | 对所有表的写入操作启用严格模式，非法数据会触发错误。 |
STRICT_TRANS_TABLES | 对事务型存储引擎（如 InnoDB）启用严格模式，非事务引擎可能宽松处理。 |
ONLY_FULL_GROUP_BY | 要求 GROUP BY 子句包含所有非聚合字段，避免查询结果不确定性。 |
NO_ZERO_IN_DATE | 禁止日期中的月份或日为 0（如 '2023-00-01'）。 |
NO_ZERO_DATE | 禁止插入 '0000-00-00' 这样的零日期。 |
ERROR_FOR_DIVISION_BY_ZERO | 除零操作触发错误（否则返回 NULL 并警告）。 |
NO_AUTO_CREATE_USER | 禁止 GRANT 语句自动创建用户（需先显式创建用户）。 |
NO_ENGINE_SUBSTITUTION | 阻止存储引擎自动替换（如表指定引擎不可用时直接报错）。 |
ANSI | 启用 ANSI SQL 兼容模式，包含多种严格规则。 |
TRADITIONAL | 启用传统严格模式（类似 STRICT_TRANS_TABLES + 其他严格规则）。 |
三、查看与配置 sql_mode
1. 查看当前 sql_mode
SHOW VARIABLES LIKE 'sql_mode';
示例输出：
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
2. 临时修改（会话级）
-- 设置当前会话的 sql_mode SET SESSION sql_mode = '模式1,模式2,...'; -- 示例：启用严格模式和 ONLY_FULL_GROUP_BY SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
3. 永久修改（全局级）
1.编辑 MySQL 配置文件（如 my.cnf 或 my.ini）：
[mysqld] sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
2.重启 MySQL 服务：
systemctl restart mysql # Linux # 或通过服务管理器重启 Windows 上的 MySQL 服务
四、常见配置场景
场景 1：严格模式（推荐生产环境）
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
作用：阻止非法数据写入，确保数据合法性。
场景 2：兼容传统应用
sql_mode = ""
作用：禁用所有严格模式，允许宽松的数据处理（需谨慎使用）。
场景 3：兼容 ANSI SQL
sql_mode = "ANSI"
作用：启用 ANSI 标准兼容模式，适用于跨数据库迁移场景。
五、注意事项
版本差异：
MySQL 5.7：默认包含
ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES等模式。MySQL 8.0：默认启用更严格的模式，且弃用
NO_AUTO_CREATE_USER。严格模式的影响：
写入失败：非法数据（如字符串插入整数字段）会直接报错，而非截断或警告。
业务兼容性：确保应用程序能正确处理严格模式触发的错误。
配置回滚：
修改
sql_mode前备份配置文件。测试环境验证后再应用到生产环境。
动态修改限制：
部分模式（如
NO_AUTO_CREATE_USER）需重启 MySQL 才能生效。
六、常见问题解决
问题 1：GROUP BY 查询报错
报错信息：
Expression #1 of SELECT list is not in GROUP BY clause原因：启用了
ONLY_FULL_GROUP_BY模式。解决：
修改
sql_mode移除ONLY_FULL_GROUP_BY。或调整 SQL 语句，确保
GROUP BY包含所有非聚合字段。
问题 2：日期 0000-00-00 插入失败
报错信息：
Incorrect date value: '0000-00-00'原因：启用了
NO_ZERO_DATE模式。解决：
移除
NO_ZERO_DATE和NO_ZERO_IN_DATE。或修改应用程序，避免插入零日期。
问题 3：除零操作返回 NULL 而非报错
原因：未启用
ERROR_FOR_DIVISION_BY_ZERO或严格模式。解决：
SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
