MySQL 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 模式是在启动阶段配置的，通过命令行或配置文件。

1. 语法

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

2. 动态设置

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

3. 查看

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

4. 注意事项

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

二、完整模式列表

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

1. 日期和时间

（1）ALLOW_INVALID_DATES

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

（2）TIME_TRUNCATE_FRACTIONAL

8.0 版本新增 mode。

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

2. 特殊字符

（1）ANSI_QUOTES

将双引号 " 视为标识符（如反引号 `），而不作为字符串引号字符
不能使用双引号将字符串引起来，因为双引号已经被解释为标识符了

（2）NO_BACKSLASH_ESCAPES

禁止在字符串和标识符中将反斜杠字符（\）用作转义字符

（3）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);

此模式适用于内置函数，不适用于用户定义的函数或存储的函数

（4）PIPES_AS_CONCAT

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

3. 数据类型

（1）NO_AUTO_VALUE_ON_ZERO

自增列插入 0 不自动生成序列号，作用于自增列
通常，可以在自增列中插入 NULL 或 0 来生成下一个序列号。此模式下，抑制 0，只有 NULL 才会生成下一个序列号。
不建议自增列存储 0，例如 mysqldump 导出数据然后重新导入时，自增列遇到 0 会重新生成序列号，导致和原表内容不一致
- mysqldump 时，会自动开启此模式

（2）REAL_AS_FLOAT

将 REAL 视为 FLOAT 的同义词
默认情况下，MySQL 将 REAL 视为 DOUBLE 的同义词

4. 运算比较

（1）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

（2）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       |       |
+-------+------------+------+-----+---------+-------+

5. 表定义

（1）NO_DIR_IN_CREATE

创建表时，忽略所有的索引目录和数据目录
此模式在主从复制的从节点上很有用

（2）NO_ENGINE_SUBSTITUTION

禁止自动替换引擎，默认已开启
当 create table 或 alter table 时，指定了一个禁用的或未编译的存储引擎，此模式控制是否自动进行替换。
当此模式禁用时，create table 如果指定了不存在的引擎，会使用默认引擎替换，并发出警告；alter table 会发出警告，不做任何操作。当此模式开启时，会报错。

6. 聚合函数

（1）ONLY_FULL_GROUP_BY

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

7. 严格模式

（1）STRICT_ALL_TABLES

所有引擎开启严格模式，拒绝非法数值
包括
这 3 个模式已过时，将来可能会和严格模式合并。
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ZERO_DATE
- NO_ZERO_IN_DATE

（2）STRICT_TRANS_TABLES

为事务性存储引擎（也有可能是非事务性存储引擎）开启严格模式
如果不能按照给定值插入事务表中，会中止该语句。属于默认模式。
包括
这 3 个模式已过时，将来可能会和严格模式合并。
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ZERO_DATE
- NO_ZERO_IN_DATE

8. 已过时的模式

（1）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，无论是否开启严格模式

（2）NO_ZERO_DATE

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

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

（3）NO_ZERO_IN_DATE

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

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

（4）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)

9. 已删除的模式

（1）NO_AUTO_CREATE_USER

8.0 版本已删除。

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

（2）NO_FIELD_OPTIONS

8.0 版本已删除。

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

（3）NO_KEY_OPTIONS

8.0 版本已删除。

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

（4）NO_TABLE_OPTIONS

8.0 版本已删除。

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

三、组合模式

1. 标准模式

（1）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 ...);
```

2. 传统模式

（1）TRADITIONAL

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

3. 已删除的模式

（1）DB2

等效于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。
8.0 版本已删除。

（2）MAXDB

等效于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER。
8.0 版本已删除。

（3）MSSQL

等效于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。
8.0 版本已删除。

（4）MYSQL323

等效于 MYSQL323、HIGH_NOT_PRECEDENCE。
8.0 版本已删除。

（5）MYSQL40

等效于 MYSQL40、HIGH_NOT_PRECEDENCE。
8.0 版本已删除。

（6）ORACLE

等效于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER。
8.0 版本已删除。

（7）POSTGRESQL

等效于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。
8.0 版本已删除。

四、严格模式

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

1. 严格模式的处理

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

2. 启用严格模式

开启 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES，则启用严格模式
严格模式包括
这 3 个模式已过时，将来可能会和严格模式合并。
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ZERO_DATE
- NO_ZERO_IN_DATE

红炉点雪