Server SQL Modes

Server SQL Modes

MySQL 服务器可以在不同的 SQL modes 下运行,并且可以根据 sql_mode 系统变量的值将这些模式应用于不同的客户端 。

SQL modes 会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查。

8.0 版本的默认6个 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

设置

SQL 模式是在启动阶段配置的,通过命令行或配置文件。

语法

sql-mode="modes"  # 设置
sql-mode=""  # 清除

动态设置

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

查看

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

注意事项

  • 一旦使用自定义分区建表,之后就不要再更改 SQL 模式,否则,可能会导致此类表的行为发生重大变化,并可能导致数据丢失或损坏
  • 主从复制中,主从的 SQL 模式应该一致,否则在复制分区表时也会导致问题

完整列表

如果没有特殊需求,使用默认的 SQL mode 就可以了。

日期和时间

  • ALLOW_INVALID_DATES
    • 不对日期进行全面检查
    • 仅检查月份是否是 1-12 和日期是否是 1-31
    • 此模式适用于 DATE 和 DATETIME 列。它不适用于始终需要有效日期的 TIMESTAMP 列
  • TIME_TRUNCATE_FRACTIONAL

    8.0 版本新增 mode。

    • 当将一个具有小数位的秒值插入 TIME/DATE/TIMESTAMP 类型但小数位数较少的列时,默认会进行四舍五入 (round)。如果启动此模式,则会截断 (truncate)

特殊字符

  • ANSI_QUOTES
    • 将双引号 " 视为标识符 (如反引号 `),而不作为字符串引号字符
    • 不能使用双引号将字符串引起来,因为双引号已经被解释为标识符了
  • NO_BACKSLASH_ESCAPES
    • 禁止在字符串和标识符中将反斜杠字符 () 用作转义字符
  • IGNORE_SPACE
    • 允许函数名称和括号 ( 之间的空格。这会将内置的函数名称作为保留字
      mysql> CREATE TABLE count (i INT);
      ERROR 1064 (42000): You have an error in your SQL syntax
      -- 报错原因:表名和内置函数 count() 名称冲突
      -- 解决方法:使用反引号
      mysql> CREATE TABLE `count` (i INT);
      
    • 此模式适用于内置函数,不适用于用户定义的函数或存储的函数

  • PIPES_AS_CONCAT

    • 将管道符 || 作为字符串连接运算符,类似
      concat(),而不是 or 的同义词

数据类型

  • NO_AUTO_VALUE_ON_ZERO
    • 自增列插入0不自动生成序列号,作用于自增列。
      • 通常,可以在自增列中插入 NULL 或0来生成下一个序列号。此模式下,抑制0,只有 NULL 才会生成下一个序列号。
    • 不建议自增列存储0,例如,mysqldump 导出数据,然后重新导入,自增列遇到0时,会重新生成序列号,导致和原表的内容不一致。
      • mysqldump 时,会自动开启此模式
  • REAL_AS_FLOAT
    • 将 REAL 视为 FLOAT 的同义词。
    • 默认情况下,MySQL 将 REAL 视为 DOUBLE 的同义词。

运算比较

  • HIGH_NOT_PRECEDENCE
    • NOT 运算的优先级。启用此模式,会使用旧版的 NOT 运算优先级

    • 默认情况下,NOT a BETWEEN b AND c 被解析为 NOT (a BETWEEN b AND c),旧版会解析成 (NOT a) BETWEEN b AND c

    • 举例

      mysql> SET sql_mode = '';
      mysql> SELECT NOT 1 BETWEEN -5 AND 5;
              -> 0
      mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
      mysql> SELECT NOT 1 BETWEEN -5 AND 5;
              -> 1
      
  • NO_UNSIGNED_SUBTRACTION
    • 默认情况下,整数之间的减法 (如果其中一个类型为 unsigned ) 将产生 unsigned 类型的结果。如果结果为负数,还会报错
      例如
      mysql> SET sql_mode = '';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SELECT CAST(0 AS UNSIGNED) - 1;
      ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
      
      -- 如果开启此模式,上面的结果就是 -1
      
      mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
      mysql> SELECT CAST(0 AS UNSIGNED) - 1;
      +-------------------------+
      | CAST(0 AS UNSIGNED) - 1 |
      +-------------------------+
      |                      -1 |
      +-------------------------+
      
    • 如果将此类操作的结果用于更新 UNSIGNED 整数列,则将结果裁剪为该列类型的最大值,如果启用了此模式,则裁剪为0。
      • 启用严格 SQL 模式后,将发生错误,并且列将保持不变。
    • 启用此模式时,即使任何操作数都是 unsigned 的,减法结果也是 signed 。
      mysql> SET sql_mode='';
      mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
      mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
      mysql> DESCRIBE t1;
      +-------+---------------------+------+-----+---------+-------+
      | Field | Type                | Null | Key | Default | Extra |
      +-------+---------------------+------+-----+---------+-------+
      | c2    | bigint(21) unsigned | NO   |     | 0       |       |
      +-------+---------------------+------+-----+---------+-------+
      
      mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
      mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
      mysql> DESCRIBE t2;
      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | c2    | bigint(21) | NO   |     | 0       |       |
      +-------+------------+------+-----+---------+-------+
      

表定义

  • NO_DIR_IN_CREATE
    • 创建表时,忽略所有的索引目录和数据目录
    • 此模式在主从复制的从节点上很有用
  • NO_ENGINE_SUBSTITUTION
    • 禁止自动替换引擎,默认已开启
    • 当 create table 或 alter table 时,指定了一个禁用的或未编译的存储引擎,此模式控制是否自动进行替换
    • 当此模式禁用时,create table 如果指定了不存在的引擎,会使用默认引擎替换,并发出警告;alter table 会发出警告,不做任何操作。当此模式开启时,会报错

聚合函数

  • ONLY_FULL_GROUP_BY
    • 拒绝查询选择列表,HAVING 条件或 ORDER BY 列表引用的非聚集列既未在 GROUP BY 子句中命名,也未在功能上依赖于 GROUP BY 列 (由其唯一确定)。默认已开启
    • MySQL 对标准 SQL 的扩展允许在 HAVING 子句中引用选择列表中的别名表达式。
      • 概括一下就是,HAVING 子句是否允许使用别名

严格模式

  • STRICT_ALL_TABLES
    • 所有引擎开启严格模式,拒绝非法数值。

    • 包括

      这3个模式已过时,将来可能会和严格模式合并。

      • ERROR_FOR_DIVISION_BY_ZERO
      • NO_ZERO_DATE
      • NO_ZERO_IN_DATE
  • STRICT_TRANS_TABLES
    • 为事务性存储引擎 (也有可能是非事务性存储引擎) 开启严格模式。如果不能按照给定值插入事务表中,会中止该语句。属于默认模式。

    • 包括

      这3个模式已过时,将来可能会和严格模式合并。

      • ERROR_FOR_DIVISION_BY_ZERO
      • NO_ZERO_DATE
      • NO_ZERO_IN_DATE

已过时的模式

  • ERROR_FOR_DIVISION_BY_ZERO

    已过时,应该和严格模式一起使用,否则会警告,默认已开启。

    • 除0错误,包括 MOD(N,0)
    • 必须与严格模式一起使用,否则发出警告,默认是开启的
    • 操作影响
      • 数据变更操作 (insert/update)
        • 如果禁用此模式,除0会插入 NULL,且不警告
        • 如果启用此模式,除0插入 NULL,且发出警告
        • 如果启用此模式和严格模式,除0会报错,除非指定 ignore,如 INSERT IGNORE 和 UPDATE IGNORE,这时会插入 NULL,且发出警告
      • select 操作
        • 除0会返回 NULL,无论是否开启严格模式
  • NO_ZERO_DATE

    已过时,应该和严格模式一起使用,否则会警告,默认已开启。

    • 禁止 ‘0000-00-00’ 的时间格式
    • 此模式允许 ‘0000-00-00’ 格式,如果禁用此模式,insert 过程不产生警告,如果开启此模式,insert 过程产生警告
    • 如果开启此模式和严格模式,则不允许 ‘0000-00-00’ 格式,insert过程报错。除非使用 ignore,如 INSERT IGNORE
      UPDATE IGNORE
  • NO_ZERO_IN_DATE

    已过时,应该和严格模式一起使用,否则会警告,默认已开启。

    • 是否允许日期中,年份部分非0,月份部分为0,影响 ‘2010-00-01’ 或 ‘2010-01-00’,不影响 ‘0000-00-00’
    • 如果禁用此模式,则允许0,insert 过程不产生警告,如果开启此模式,则带0的时间会插入 ‘0000-00-00’ ,并产生警告
    • 如果开启此模式和严格模式,不允许0,insert 过程报错,除非使用 ignore
  • PAD_CHAR_TO_FULL_LENGTH

    8.0 版本中已过时,计划删除。

    • 默认情况下,检索时会把 CHAR 类型列值中的空格 trim 掉

    • 如果开启此模式,则不会去除 CHAR 列的空格,并且以全长填充。这个模式只适用于 CHAR 格式的列

      mysql> CREATE TABLE t1 (c1 CHAR(10));
      Query OK, 0 rows affected (0.37 sec)
      
      mysql> INSERT INTO t1 (c1) VALUES('xy');
      Query OK, 1 row affected (0.01 sec)
      
      mysql> SET sql_mode = '';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
      +------+-----------------+
      | c1   | CHAR_LENGTH(c1) |
      +------+-----------------+
      | xy   |               2 |
      +------+-----------------+
      1 row in set (0.00 sec)
      
      mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
      +------------+-----------------+
      | c1         | CHAR_LENGTH(c1) |
      +------------+-----------------+
      | xy         |              10 |
      +------------+-----------------+
      1 row in set (0.00 sec)
      

已删除的模式

  • NO_AUTO_CREATE_USER

    8.0 版本已删除。

    • 不自动创建用户,此模式默认开启
    • 对于 grant 语句,除非指定了身份验证信息,否则应防止 grant 语句自动创建新的用户。grant 创建用户的方法已过时,推荐使用 create user。可以使用 identified by 指定非空密码,使用 identified with 指定身份验证插件
  • NO_FIELD_OPTIONS

    8.0 版本已删除。

    • 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 特定的列选项
  • NO_KEY_OPTIONS

    8.0 版本已删除。

    • 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 特定的索引选项
  • NO_TABLE_OPTIONS

    8.0 版本已删除。

    • 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 特定的表选项 (例如ENGINE)

组合模式

标准模式

  • ANSI
    • 最重要的模式。此模式更改语法和行为以更符合标准 SQL。

    • 等效于 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY

    • ANSI 模式还会导致服务器返回查询错误,其中无法将具有外部引用 S(outer_ref) 的集合函数 S 聚合到已解决外部引用的外部查询中

      SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);
      

传统模式

  • TRADITIONAL
    • 当在列中插入不正确的值时,报错,而不是警告。
      • 启用传统模式后,一旦发生错误,INSERT 或 UPDATE 将中止。
      • 如果使用的是非事务性存储引擎,错误之前可能不会进行回滚,导致部分完成更新。
    • 等效于 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER, 和 NO_ENGINE_SUBSTITUTION

已删除的模式

  • DB2

    8.0 版本已删除。

    • 等效于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
  • MAXDB

    8.0 版本已删除。

    • 等效于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
  • MSSQL

    8.0 版本已删除。

    • 等效于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
  • MYSQL323

    8.0 版本已删除。

    • 等效于 MYSQL323, HIGH_NOT_PRECEDENCE
  • MYSQL40

    8.0 版本已删除。

    • 等效于 MYSQL40, HIGH_NOT_PRECEDENCE
  • ORACLE

    8.0 版本已删除。

    • 等效于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
  • POSTGRESQL

    8.0 版本已删除。

    • 等效于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

严格模式

严格模式控制在数据变更操作 (insert/update) 时,怎么处理无效或缺失值。

严格模式的处理

  • 正常的严格模式对无效值产生错误,而不是警告
    • 可以通过 INSERT IGNORE 或 UPDATE IGNORE,使 MySQL 为无效或缺失的值插入调整后的值并产生警告
  • IGNORE 关键字和严格模式
    • ignore 将错误降级为警告
    • 严格模式,将警告升级为错误
    • 当同时使用 IGNORE 关键字和严格的 SQL 模式时,IGNORE 优先

启用严格模式

  • 开启 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES,则启用严格模式

  • 严格模式包括

    这3个模式已过时,将来可能会和严格模式合并。

    • ERROR_FOR_DIVISION_BY_ZERO
    • NO_ZERO_DATE
    • NO_ZERO_IN_DATE

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注