Server SQL Modes

没有评论

Server

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
发表于：MySQL
2021-05-17
# MySQL# MySQL 服务器# MySQL 服务器管理# SQL mode
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-05-17发表，共计6753字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
Clone 插件
MySQL 密码管理
mysqlslap-压测客户端
MySQL 版本
评论（没有评论）