详解Mysql的 sql_mode（SQL 模式）

 
详解 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  

1. SELECT @@SESSION.sql_mode;
2. -- 或简写
3. SELECT @@sql_mode;

复制代码

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

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

1. SELECT @@GLOBAL.sql_mode;

复制代码

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

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

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

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

1. -- 设置会话级 sql_mode（示例：严格模式+分组限制）
2. 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  

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

复制代码

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

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

1. # Linux/Mac（my.cnf 通常在 /etc/my.cnf 或 /etc/mysql/my.cnf）
2. # Windows（my.ini 通常在 MySQL 安装目录的 bin 文件夹）
3. [mysqld]
4. 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 运行    

1. # Linux 重启命令
2. systemctl restart mysqld
3. # 或
4. service mysqld restart
6. # Windows 重启命令（cmd 管理员模式）
7. net stop mysql
8. net start mysql

复制代码

 四、常见 sql_mode 取值详解

sql_mode 支持多值组合（用逗号分隔），以下是最常用的模式值，按功能分类说明：1. 严格模式相关（核心推荐生产启用）

严格模式是数据校验的核心，阻止无效数据写入，避免脏数据。 模式值作用说明STRICT_TRANS_TABLES对事务表（如 InnoDB）启用严格模式： - 无效数据插入 / 更新直接报错； - 非事务表（如 MyISAM）仍宽松（仅警告，数据截断）。STRICT_ALL_TABLES对所有表（事务 / 非事务）启用严格模式： - 无论表类型，无效数据均报错，不允许写入。示例：严格模式 vs 宽松模式

假设表结构： sql  

1. CREATE TABLE test_strict (
2.   id INT,
3.   name VARCHAR(5)  -- 姓名最长 5 个字符
4. ) ENGINE=InnoDB;

复制代码

  

• 宽松模式（未启用 STRICT_TRANS_TABLES）： 插入超长字符串时，MySQL 自动截断并警告，数据仍写入：sql  
  1. INSERT INTO test_strict VALUES (1, 'abcdefgh'); -- 字符串长度 8 > 5
  2. -- 警告：Data truncated for column 'name' at row 1
  3. -- 结果：name 字段值为 'abcde'（截断后）

  复制代码
    
  
• 严格模式（启用 STRICT_TRANS_TABLES）： 插入超长字符串直接报错，数据不写入：sql  
  1. INSERT INTO test_strict VALUES (1, 'abcdefgh');
  2. -- 报错：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  

1. INSERT INTO test_date (create_time) VALUES ('0000-00-00');
2. -- 报错：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  
  1. SELECT name, age FROM user GROUP BY name; -- age 未分组，未聚合
  2. -- 结果：返回每个 name 对应的第一条 age（不确定）

  复制代码
    
  
• 启用 ONLY_FULL_GROUP_BY（严格）： 非分组字段必须用聚合函数包裹，否则报错：sql  
  1. -- 正确：age 用聚合函数 AVG() 包裹
  2. SELECT name, AVG(age) FROM user GROUP BY name;
  4. -- 错误：age 未分组且未聚合
  5. SELECT name, age FROM user GROUP BY name;
  6. -- 报错：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  

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

复制代码

 4. 常用模式组合

MySQL 提供了一些预定义的模式组合（本质是多值拼接）： 组合模式包含的模式值适用场景ANSIREAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE兼容 SQL 标准，适合多数据库迁移TRADITIONALSTRICT_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  

1. 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  

1. 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  
  1. -- 用 ANY_VALUE() 取任意值（适合非核心字段）
  2. SELECT name, ANY_VALUE(age) FROM user GROUP BY name;

  复制代码
    
  
• 不推荐方案：临时关闭 ONLY_FULL_GROUP_BY（会牺牲数据一致性）：sql  
  1. 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  
  1. SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'NO_ZERO_DATE', ''));

  复制代码
    
  

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

解决方案

启用 PIPES_AS_CONCAT 模式： sql  

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

复制代码

 七、生产环境最佳实践

• 启用严格模式组合： 推荐配置（兼容 MySQL 5.7/8.0）：ini  
  1. 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  

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

复制代码

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

1. -- 一键启用“传统严格模式”（包含上述所有规则+STRICT_ALL_TABLES）
2. 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  

1. -- 基于 TRADITIONAL 组合，去掉 STRICT_ALL_TABLES
2. sql_mode = "TRADITIONAL,STRICT_TRANS_TABLES"

复制代码

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

• 默认 sql_mode 缺少 STRICT_ALL_TABLES 的原因： 为了 “适配主流 InnoDB 事务表、避免非事务表的数据一致性风险、兼容旧系统”，MySQL 选择了 “适度严格” 的 STRICT_TRANS_TABLES，而非 “过度严格” 的 STRICT_ALL_TABLES，是平衡后的最优解。
  
• 模式组合的核心用处： 提供 “场景化、预定义的配置模板”，帮用户 简化配置、快速适配业务场景（如迁移、严格校验）、统一环境规范，同时支持自定义扩展，兼顾 “便捷性” 和 “灵活性”。
  

 简单说：模式组合是 “给用户的快捷选项”，而默认 sql_mode 是 “MySQL 为大多数用户选的最优默认选项”—— 两者互补，用户可根据自身场景（如是否用非事务表、是否需要兼容其他数据库）选择直接用组合模式，或基于默认值微调。 
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

收藏一下   不知道什么时候能用到

分享、互助 让互联网精神温暖你我