详解 MySQL 的 sql_mode（SQL 模式）

一、sql_mode 核心概念

sql_mode 是 MySQL 中语法校验、数据校验、行为兼容的核心配置，它定义了 MySQL 对 SQL 语法的解析规则、数据有效性的校验标准，以及与其他数据库（如 Oracle、SQL Server）的兼容策略。

简单来说：sql_mode 决定了 MySQL 是 “宽松模式” 还是 “严格模式”，以及支持哪些 SQL 语法、拒绝哪些非法数据。

核心作用

规范 SQL 语法：限制或支持特定的 SQL 语法（如是否允许非标准标识符引用）；
数据有效性校验：阻止无效数据插入 / 更新（如非法日期、除以零、字符串截断等）；
兼容其他数据库：模拟其他数据库的 SQL 行为（如 Oracle 的字符串连接符 ||）；
避免歧义行为：明确 SQL 执行逻辑（如分组查询的字段限制）。

二、查看当前 sql_mode

1. 查看会话级 sql_mode（当前连接生效）

会话级仅对当前数据库连接有效，断开后失效：

sql
 
SELECT @@SESSION.sql_mode;
-- 或简写
SELECT @@sql_mode;

2. 查看全局级 sql_mode（所有新连接生效）

全局级对所有新建立的连接生效，但不影响已存在的连接：

sql
 
SELECT @@GLOBAL.sql_mode;

3. 配置文件位置（永久生效）

MySQL 的默认配置文件（my.cnf 或 my.ini）中，sql_mode 可通过配置项直接设置（后续详解）。

三、修改 sql_mode 的方式

1. 会话级修改（临时生效，重启 / 断开连接失效）

仅对当前连接有效，适合临时测试场景：

sql
 
-- 设置会话级 sql_mode（示例：严格模式+分组限制）
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';

2. 全局级修改（需重启 MySQL 生效）

对所有新连接生效，但需重启 MySQL 才能完全生效（或执行 FLUSH PRIVILEGES 刷新权限）：

sql
 
-- 设置全局级 sql_mode
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';-- 刷新权限（无需重启，新连接立即生效）
FLUSH PRIVILEGES;

3. 配置文件修改（永久生效，推荐生产环境）

在 MySQL 配置文件（my.cnf 或 my.ini）中添加 / 修改 sql_mode，重启 MySQL 后永久生效：

ini
 
# Linux/Mac（my.cnf 通常在 /etc/my.cnf 或 /etc/mysql/my.cnf）
# Windows（my.ini 通常在 MySQL 安装目录的 bin 文件夹）
[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

修改后重启 MySQL：

bash
 
运行
 
# Linux 重启命令
systemctl restart mysqld
# 或
service mysqld restart# Windows 重启命令（cmd 管理员模式）
net stop mysql
net start mysql

四、常见 sql_mode 取值详解

sql_mode 支持多值组合（用逗号分隔），以下是最常用的模式值，按功能分类说明：

1. 严格模式相关（核心推荐生产启用）

严格模式是数据校验的核心，阻止无效数据写入，避免脏数据。

模式值	作用说明
`STRICT_TRANS_TABLES`	对事务表（如 InnoDB）启用严格模式： - 无效数据插入 / 更新直接报错； - 非事务表（如 MyISAM）仍宽松（仅警告，数据截断）。
`STRICT_ALL_TABLES`	对所有表（事务 / 非事务）启用严格模式： - 无论表类型，无效数据均报错，不允许写入。

示例：严格模式 vs 宽松模式

假设表结构：

sql
 
CREATE TABLE test_strict (id INT,name VARCHAR(5)  -- 姓名最长 5 个字符
) ENGINE=InnoDB;

宽松模式（未启用 STRICT_TRANS_TABLES）：

插入超长字符串时，MySQL 自动截断并警告，数据仍写入：

sql
 
INSERT INTO test_strict VALUES (1, 'abcdefgh'); -- 字符串长度 8 > 5
-- 警告：Data truncated for column 'name' at row 1
-- 结果：name 字段值为 'abcde'（截断后）

严格模式（启用 STRICT_TRANS_TABLES）：

插入超长字符串直接报错，数据不写入：

sql
 
INSERT INTO test_strict VALUES (1, 'abcdefgh');
-- 报错：Data truncation: Data too long for column 'name' at row 1

2. 数据有效性校验相关

模式值	作用说明
`NO_ZERO_IN_DATE`	禁止日期中的 “月 / 日” 为 0（如 `'2025-00-10'`、`'2025-01-00'`），严格模式下报错，宽松模式下警告。
`NO_ZERO_DATE`	禁止插入 “全零日期”（`'0000-00-00'`），严格模式下报错，宽松模式下警告。
`ERROR_FOR_DIVISION_BY_ZERO`	禁止 “除以零” 操作： - 整数除法（如 `5/0`）直接报错； - 浮点数除法（如 `5.0/0`）返回 `NULL` 并警告（避免完全阻断查询）。
`NO_AUTO_VALUE_ON_ZERO`	插入自增字段时，禁止 `0` 作为自增值（仅允许 `NULL` 或不指定字段，自动生成自增值）。

示例：禁止全零日期

启用 NO_ZERO_DATE + 严格模式：

sql
 
INSERT INTO test_date (create_time) VALUES ('0000-00-00');
-- 报错：Invalid datetime value: '0000-00-00' for column 'create_time' at row 1

3. 语法兼容与规范相关

模式值	作用说明
`ONLY_FULL_GROUP_BY`	分组查询（`GROUP BY`）的严格限制： - `SELECT` 后的字段必须是 `GROUP BY` 中的字段，或被聚合函数（`SUM`/`AVG`/`MAX` 等）包裹； - 避免 “非确定性分组”（即同一分组下非聚合字段的值不唯一）。
`ANSI_QUOTES`	启用后，字符串只能用单引号 `'` 包裹，双引号 `"` 视为标识符（如表名、字段名），兼容 SQL 标准。
`PIPES_AS_CONCAT`	把管道符 `	`视为字符串连接符（替代`CONCAT ()` 函数），兼容 Oracle 语法。
`IGNORE_SPACE`	允许函数名和括号之间有空格（如 `SUM (1+2)` 等同于 `SUM(1+2)`），兼容部分数据库语法。
`NO_ENGINE_SUBSTITUTION`	当指定的存储引擎（如 `ENGINE=MyISAM`）不存在时，直接报错，而非自动替换为默认引擎（如 InnoDB）。

示例 1：ONLY_FULL_GROUP_BY（重点）

禁用 ONLY_FULL_GROUP_BY（宽松）：

分组查询允许非分组字段出现在 SELECT 中，结果可能随机（同一分组下取第一条数据）：
sql
```
SELECT name, age FROM user GROUP BY name; -- age 未分组，未聚合
-- 结果：返回每个 name 对应的第一条 age（不确定）
```

启用 ONLY_FULL_GROUP_BY（严格）：

非分组字段必须用聚合函数包裹，否则报错：

sql
 
-- 正确：age 用聚合函数 AVG() 包裹
SELECT name, AVG(age) FROM user GROUP BY name;-- 错误：age 未分组且未聚合
SELECT name, age FROM user GROUP BY name;
-- 报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

示例 2：PIPES_AS_CONCAT（兼容 Oracle）

启用 PIPES_AS_CONCAT 后：

sql
 
SELECT 'Hello' || ' ' || 'MySQL' AS result; -- 等同于 CONCAT('Hello', ' ', 'MySQL')
-- 结果：result = 'Hello MySQL'

4. 常用模式组合

MySQL 提供了一些预定义的模式组合（本质是多值拼接）：

组合模式	包含的模式值	适用场景
`ANSI`	`REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE`	兼容 SQL 标准，适合多数据库迁移
`TRADITIONAL`	`STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION`	“传统严格模式”，模拟严格的数据库行为
`ALLOW_INVALID_DATES`	仅校验日期格式（如 `MM-DD-YYYY`），不校验日期有效性（如 `2025-02-30` 视为有效）	兼容旧系统的非法日期数据

五、MySQL 不同版本的默认 sql_mode

1. MySQL 5.7+（推荐生产版本）

默认启用严格模式组合，核心包含：

plaintext
 
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

（注：NO_AUTO_CREATE_USER 在 MySQL 8.0 中被移除，因为 8.0 不再支持 GRANT 语句自动创建用户，必须显式执行 CREATE USER）

2. MySQL 8.0+

默认模式简化（移除 NO_AUTO_CREATE_USER）：

plaintext
 
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

3. MySQL 5.6 及以下

默认是宽松模式（无 STRICT_*、ONLY_FULL_GROUP_BY 等），容易产生脏数据，不推荐直接使用。

六、常见问题与解决方案

1. 报错：`this is incompatible with sql_mode=only_full_group_by`

原因

分组查询中 SELECT 包含非分组、非聚合字段，违反 ONLY_FULL_GROUP_BY 规则。

解决方案

优先方案：优化 SQL，将非分组字段用聚合函数（SUM/AVG/MAX/ANY_VALUE()）包裹：

sql
 
-- 用 ANY_VALUE() 取任意值（适合非核心字段）
SELECT name, ANY_VALUE(age) FROM user GROUP BY name;

不推荐方案：临时关闭 ONLY_FULL_GROUP_BY（会牺牲数据一致性）：

sql
 
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

2. 报错：`Invalid datetime value: '0000-00-00'`

原因

启用了 NO_ZERO_DATE + 严格模式，禁止插入全零日期。

解决方案

修正数据：将 '0000-00-00' 改为合法日期（如 '1970-01-01'）；

临时关闭 NO_ZERO_DATE（不推荐生产）：

sql
 
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'NO_ZERO_DATE', ''));

3. 迁移 Oracle 数据时，`||` 无法连接字符串

解决方案

启用 PIPES_AS_CONCAT 模式：

sql
 
SET GLOBAL sql_mode = CONCAT(@@GLOBAL.sql_mode, ',PIPES_AS_CONCAT');
FLUSH PRIVILEGES;

七、生产环境最佳实践

启用严格模式组合：

推荐配置（兼容 MySQL 5.7/8.0）：
ini
```
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
```
核心目的：阻止无效数据写入，保证数据一致性。
避免随意关闭核心模式：

如 ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES 等，关闭后可能导致数据歧义或脏数据。
开发 / 测试环境与生产保持一致：

避免开发环境宽松、生产环境严格导致的 “本地正常，线上报错”。
迁移场景按需调整：
- 从 Oracle 迁移：添加 PIPES_AS_CONCAT, ANSI_QUOTES；
- 从旧系统迁移（含非法日期）：临时关闭 NO_ZERO_IN_DATE, NO_ZERO_DATE，同步后修正数据再启用。
通过配置文件永久设置：

避免使用 SET GLOBAL 临时修改（MySQL 重启后失效），优先在 my.cnf/my.ini 中配置。
临时设置模式组合： set @@sql_mode = 'ANSI'; 或者 set @@sql_mode = 'TRADITIONAL,STRICT_ALL_TABLES';

总结

sql_mode 是 MySQL 数据质量和语法兼容的核心配置，生产环境建议启用严格模式 + 必要的兼容模式，通过规范 SQL 语法和校验数据有效性，避免脏数据和歧义行为。理解各模式值的作用，结合业务场景（如迁移、旧系统兼容）灵活调整，是 MySQL 运维和开发的关键技能。

==================================================

MySQL 默认 sql_mode 不包含 STRICT_ALL_TABLES（即不完整套用 TRADITIONAL 组合），本质是 “平衡严格性与兼容性、适配主流场景”；而模式组合（如 TRADITIONAL、ANSI）的核心用处是 “提供预定义的、场景化的配置模板，简化用户选型成本”。

下面分两部分详细拆解你的问题：

一、为什么 MySQL 默认 `sql_mode` 缺少 `TRADITIONAL` 中的 `STRICT_ALL_TABLES`？

要理解这个设计，必须先回顾两个关键前提：

STRICT_TRANS_TABLES vs STRICT_ALL_TABLES 的核心差异（之前提到过，这里聚焦 “默认选择逻辑”）：
- STRICT_TRANS_TABLES：仅对事务表（如 InnoDB）严格（无效数据报错），对非事务表（如 MyISAM）宽松（仅警告、截断数据）；
- STRICT_ALL_TABLES：对所有表（事务 / 非事务）严格（无效数据均报错）。
MySQL 的默认存储引擎演变：
- MySQL 5.5 后默认引擎改为 InnoDB（事务表），至今仍是主流；
- 早期非事务表（MyISAM）逐渐被淘汰，但仍有旧系统依赖。

基于这两个前提，MySQL 默认不选 STRICT_ALL_TABLES 的原因的是 “避免过度严格导致的副作用，兼顾主流场景与历史兼容”：

1. 主流场景已被 `STRICT_TRANS_TABLES` 覆盖

现在绝大多数业务用的是 InnoDB 事务表，STRICT_TRANS_TABLES 已经能满足 “严格校验数据、阻止脏数据” 的核心需求 —— 事务表支持回滚，一旦数据无效，报错后整个事务回滚，不会出现 “部分数据写入成功、部分失败” 的情况，数据一致性有保障。

而 STRICT_ALL_TABLES 针对的是非事务表（MyISAM），但这类表现在极少用，没必要为了小众场景让所有用户承担 “过度严格” 的成本。

2. `STRICT_ALL_TABLES` 对非事务表存在 “数据一致性风险”

非事务表的特性是不支持回滚，如果启用 STRICT_ALL_TABLES，会出现严重问题：

假设用 MyISAM 表批量插入 100 条数据，前 99 条有效，第 100 条无效 —— 此时 STRICT_ALL_TABLES 会直接报错，但前 99 条数据已经写入表中（无法回滚），导致 “部分数据成功、部分失败” 的脏数据状态。

而 STRICT_TRANS_TABLES 对非事务表的处理是 “宽松模式（警告 + 截断）”，虽然会允许部分不严重的无效数据（如字符串截断），但避免了 “批量插入中断导致的数据碎片化”—— 这是 MySQL 权衡后的选择：对小众的非事务表，优先保证 “插入不中断”，而非 “绝对严格”。

3. 历史兼容：避免升级后旧系统大面积报错

MySQL 5.6 及以下默认是 “完全宽松模式”，很多旧系统（尤其是依赖 MyISAM 表的系统）可能存在 “字符串截断、无效日期” 等不规范数据插入逻辑。

如果默认启用 STRICT_ALL_TABLES，这些旧系统升级后会直接大面积报错，迁移成本极高。而 STRICT_TRANS_TABLES 只针对 InnoDB 严格，对旧系统的非事务表影响极小，兼顾了 “逐步收紧严格性” 和 “历史系统兼容”。

二、模式组合（如 `TRADITIONAL`、`ANSI`）的实际用处是什么？

模式组合的本质是 “MySQL 官方预定义的、经过场景优化的 sql_mode 集合”—— 它不强制用户使用，但能帮用户 “快速选型、减少配置错误”，核心用处有 3 点：

1. 简化配置：一键启用 “场景化规则”，不用手动拼接

如果没有模式组合，用户要启用 “严格模式”，需要手动拼接 5-6 个模式值：

sql
 
-- 手动拼接严格模式（容易漏写、写错）
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

而用 TRADITIONAL 组合，一句话就能实现 “近似效果”（官方已帮你拼好核心严格规则）：

sql
 
-- 一键启用“传统严格模式”（包含上述所有规则+STRICT_ALL_TABLES）
SET GLOBAL sql_mode = 'TRADITIONAL';

对新手或快速部署场景，模式组合能避免 “漏配关键模式”（如忘记加 ERROR_FOR_DIVISION_BY_ZERO），降低配置门槛。

2. 场景化适配：快速对齐目标行为（兼容其他数据库 / 严格校验）

模式组合是为特定场景设计的 “模板”，用户可以根据需求直接选用，不用逐个研究单个模式的作用：

组合模式	核心场景	解决的问题
`TRADITIONAL`	需要 “极致严格” 的场景（如金融）	模拟 Oracle/SQL Server 的严格行为，拒绝任何无效数据，适合对数据一致性要求极高的业务
`ANSI`	多数据库迁移（如从 SQL Server 迁移）	对齐 SQL 标准语法（如双引号当标识符、`	` 连接字符串），减少 SQL 改写成本
`ALLOW_INVALID_DATES`	兼容旧系统非法日期数据	只校验日期格式（如 `MM-DD-YYYY`），不校验有效性（如 `2025-02-30`），避免旧数据导入报错

举个实际例子：

如果你的业务需要从 Oracle 迁移到 MySQL，直接启用 ANSI + PIPES_AS_CONCAT 组合，就能直接使用 Oracle 的 || 字符串连接语法，不用把所有 a||b 改成 CONCAT(a,b)，极大降低迁移成本。

3. 统一规范：团队 / 系统间保持一致的 SQL 行为

模式组合是 “官方认证” 的配置模板，比团队手动约定的 sql_mode 更权威、更合理。

比如团队约定 “所有环境启用 TRADITIONAL 模式”，就能避免出现 “开发环境用宽松模式、测试环境用严格模式” 的不一致问题，减少 “本地正常、线上报错” 的排查成本。

补充：模式组合不是 “固定不变” 的，支持自定义扩展

模式组合是 “基础模板”，用户可以根据需求修改 —— 比如你需要 TRADITIONAL 的严格性，但不想用 STRICT_ALL_TABLES（因为有少量 MyISAM 表），可以这样配置：

ini
 
-- 基于 TRADITIONAL 组合，去掉 STRICT_ALL_TABLES
sql_mode = "TRADITIONAL,STRICT_TRANS_TABLES"

（原理：后配置的模式会覆盖组合中冲突的规则，最终等效于 TRADITIONAL 去掉 STRICT_ALL_TABLES，保留其他严格规则）

总结

默认 sql_mode 缺少 STRICT_ALL_TABLES 的原因：

为了 “适配主流 InnoDB 事务表、避免非事务表的数据一致性风险、兼容旧系统”，MySQL 选择了 “适度严格” 的 STRICT_TRANS_TABLES，而非 “过度严格” 的 STRICT_ALL_TABLES，是平衡后的最优解。
模式组合的核心用处：

提供 “场景化、预定义的配置模板”，帮用户简化配置、快速适配业务场景（如迁移、严格校验）、统一环境规范，同时支持自定义扩展，兼顾 “便捷性” 和 “灵活性”。