MySQL SQL Mode及其说明

以下是MySQL中所有支持的SQL Mode及其说明,综合了多个来源的信息并进行了分类整理:

一、严格模式相关

  1. STRICT_TRANS_TABLES

    • 对事务型存储引擎(如InnoDB)启用严格数据校验。若插入非法值(如类型不符、超出范围等),语句会被中止。对于非事务表,仅当首行数据非法时中止语句。
  2. STRICT_ALL_TABLES

    • 对所有存储引擎启用严格模式。与STRICT_TRANS_TABLES的区别在于:对于多行插入的非事务表,即使后续行数据非法,也会中止整个操作(而非仅跳过非法行)。
  3. TRADITIONAL

    • 组合模式,等同于STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION。行为类似传统SQL数据库,对非法数据直接报错而非警告。

二、日期与零值控制

  1. NO_ZERO_IN_DATE

    • 禁止年月日中部分为零的日期(如2025-00-01),但允许0000-00-00。需与严格模式配合生效。
  2. NO_ZERO_DATE

    • 禁止0000-00-00作为有效日期。若启用严格模式,插入该值会报错。
  3. ALLOW_INVALID_DATES

    • 宽松日期校验,仅检查月份(1-12)和日(1-31),不验证实际有效性(如允许2025-02-30)。不适用于TIMESTAMP列。

三、数学运算与类型处理

  1. ERROR_FOR_DIVISION_BY_ZERO

    • 除零或模零操作在严格模式下报错(否则返回NULL并警告)。注意:该模式在MySQL 8.0中已标记为弃用,其功能将合并到严格模式。
  2. NO_UNSIGNED_SUBTRACTION

    • 允许无符号整数减法结果为负数(默认会溢出报错)。
  3. REAL_AS_FLOAT

    • REAL类型视为FLOAT(默认视为DOUBLE)。

四、语法与标识符规则

  1. ANSI_QUOTES

    • 双引号"视为标识符引用符(如列名),而非字符串引号。需改用单引号定义字符串。
  2. IGNORE_SPACE

    • 允许函数名与括号间有空格(如COUNT (1)),但会导致函数名成为保留字需转义。
  3. PIPES_AS_CONCAT

    • ||视为字符串连接符(标准SQL行为),而非逻辑OR。
  4. HIGH_NOT_PRECEDENCE

    • 提高NOT运算符优先级(如NOT a BETWEEN b AND c解析为(NOT a) BETWEEN b AND c)。

五、GROUP BY与聚合控制

  1. ONLY_FULL_GROUP_BY
    • 严格校验GROUP BY查询,SELECT中的非聚合列必须在GROUP BY子句中出现或函数依赖。

六、引擎与存储相关

  1. NO_ENGINE_SUBSTITUTION

    • 若创建表时指定不可用的存储引擎(如未编译的引擎),直接报错而非替换为默认引擎。
  2. NO_DIR_IN_CREATE

    • 忽略建表语句中的INDEX DIRECTORYDATA DIRECTORY指令(常用于主从复制环境)。

七、其他特殊模式

  1. NO_AUTO_VALUE_ON_ZERO

    • 禁止0作为自增列的下一个值(仅NULL触发自增)。
  2. NO_BACKSLASH_ESCAPES

    • 禁用反斜杠\作为转义符(如\n视为普通字符)。
  3. PAD_CHAR_TO_FULL_LENGTH

    • 检索CHAR类型时保留右端空格(默认自动修剪)。

八、组合模式(预定义模式组)

  1. ANSI

    • 等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,接近标准SQL语法。
  2. DB2/MSSQL/ORACLE/POSTGRESQL

    • 兼容其他数据库的语法组合(如PIPES_AS_CONCAT,ANSI_QUOTES等),但这些模式在MySQL 8.0中已移除。

注意事项

  • 不同MySQL版本的默认模式可能不同(如5.7默认包含ONLY_FULL_GROUP_BY和严格模式,而5.6默认宽松)。
  • 部分模式(如ERROR_FOR_DIVISION_BY_ZERO)已弃用,未来版本可能整合到严格模式中。

完整列表可参考MySQL官方文档。

### 配置设置 MySQL `sql-mode` 参数 #### 修改全局 SQL 模式 可以通过修改 MySQL 的配置文件来永久更改服务器级别的 `sql_mode` 设置。对于大多数 Linux 发行版,默认配置文件位于 `/etc/my.cnf` 或者 `/etc/mysql/my.cnf` 中,在 Windows 上则通常是安装目录下的 `my.ini` 文件。 找到 `[mysqld]` 节点并添加或编辑如下行以指定所需的模式: ```ini [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION ``` 保存更改后重启 MySQL 服务使新设置生效[^1]。 #### 动态调整会话级 SQL 模式 除了通过配置文件静态设定外,还可以临时改变当前连接中的 `sql_mode` 值而不影响其他客户端连接。这适用于不想让改动立即应用到整个系统的场景下。使用 SET SESSION 语句即可实现这一点: ```sql SET SESSION sql_mode='STRICT_TRANS_TABLES'; ``` 此命令仅对该特定会话有效直至断开连接为止[^2]。 #### 清除某些严格条件 有时可能希望移除部分严格的约束以便兼容旧版本的应用程序逻辑或是处理特殊情况。比如去除对日期字段中零值敏感性的控制,可通过以下方式完成: ```sql SELECT @@sql_mode INTO @current_sql_mode; SET SESSION sql_mode = REPLACE(@current_sql_mode,'NO_ZERO_IN_DATE',''); SET SESSION sql_mode = REPLACE(@current_sql_mode,'NO_ZERO_DATE',''); ``` 上述脚本首先获取现有的 `sql_mode` 字符串存入变量 `@current_sql_mode` ,接着两次调用 `REPLACE()` 函数分别去掉两个不想要的选项后再重新赋给 `session sql_mode` 。请注意这种方式同样只作用于单次会话内。 #### 处理常见错误提示 当启用了 `ONLY_FULL_GROUP_BY` 这样的严格规则时可能会遇到类似下面这样的报错信息:“Expression #X of SELECT list is not in GROUP BY clause...”。这是因为查询违反了标准SQL关于分组聚合的要求——所有未被聚集函数处理过的列都应当出现在 `GROUP BY` 子句里。解决办法是在查询语句适当位置加入缺失项至 `GROUP BY` 后面或者利用子查询等方式重构原有表达式结构[^5]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

暮雨疏桐

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值