SQL Mode

与其他数据库不同，MySQL 可以运行在不同SQL Mode（SQL 模式）下。SQL Mode 定义了 MySQL 应支持的 SQL 语法、数据校验等，这样可以更容易的在不同环境中使用 MySQL。

SQL Mode 简介

在 MySQL 中，SQL Mode 主要用来解决下面几类问题：

• 通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效的保证数据的准确性。
• 通过设置 SQL Mode 为 ANSI 模式，来保证大多数 SQL 符合标准的 SQL语法，这样应用在不同数据库之间迁移时，不需要对业务 SQL 进行较大的修改。
• 在不同数据库迁移数据之前，通过设置 SQL Mode 可以使 MySQL 上的数据更方便的迁移到目标数据库中。

MySQL 5.7 中默认的 SQL Mode

• ONLY_FULL_GROUP_BY

  • 在 group by 子句中没有出现的列，出现在select 列表、having 条件、order 条件中会被拒绝。

• STRICT_TRANS_TABLES

  • 非法日期，超过字段长度的值插入时，直接报错，拒绝执行。

• NO_ZERO_IN_DATE

  • 日期中针对月份和日期部分，如果为 0，比如：2021-00-00，有不同的执行逻辑。
  • disable： 可以正常插入，实际插入值还是 2021-00-00，没有警告。
  • enable： 可以正常插入，有警告。如果 mode 中包含 STRICT_TRANS_TABLES 则日期被拒绝写入，但可以通过加 ignore 关键字 写入 0000-00-00 有警告。

• NO_ZERO_DATE

  • 针对 0000-00-00，执行逻辑如下：
  • disable：可以正常插入，没有警告
  • enable： 可以正常插入，有警告。如果 mode 中包含 STRICT_TRANS_TABLES 则日期被拒绝写入，但可以通过加 ignore 关键字 写入 0000-00-00 有警告。

• ERROR＿FOR_DIVISION_BY_ZERO

  • 除数为 0
  • disable：插入NULL 没有警告。
  • enable：插入NULL，有警告，如果 mode 中包含 STRICT_TRANS_TABLES 则日期被拒绝写入，但可以通过加 ignore 关键字 写入 NULL 有警告。

• NO_AUTO_CREATE_USER

  • 防止使用不带密码子句的 grant 语句来创建一个用户

• NO_ENGINE_SUBSTTTUTION

  • 执行 create table 或者 alter table 语句时，如果指定了不支持（包括 disablie 或未编译）的存储引擎，是否自动替换为默认的存储引擎
  • disable：create table 会自动替换后执行，alter table 不会执行，两个命令都有警告。
  • enable：两个命令直接报错

相比之前的版本，MySQL 5.7.5 之后的版本最大的区别在 SQL Mode 的默认设置中，加入了严格的事务表模式 (STAICT_TRANS_TABLES)，在这种模式下不允许插入字段类型不一致的值，不允许插入超过字段长度的值，这在大多数情况下是合理的。如果不设置 STAICT_TRANS_TABLES，那么上述的操作会被允许，只是在插入后，MySQL 会返回一个 warning，从而导致表中写入错误数据。

SQL Mode 的常见功能

校验日期合法性

下面的例子中，在不同模式下插入 "2021-04-31"（四月没有31天） 是否能正确插入：

mysql> create table t_sql_mode_date (d datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> set session sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_sql_mode_date value('2021-04-31');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM t_sql_mode_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM t_sql_mode_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t_sql_mode_date value('2021-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2021-04-31' for column 'd' at row 1

在 ANSI 模式下，非法的日期可以插入，但插入的值变成了 "0000-00-00 00:00:00"，并且系统给出了 warning；

而在 TRADITIONAL 模式下，会直接提示日期违法，拒绝插入。

反斜线处理

启用 NO_BACKSLASH_ESCAPES 模式，使反斜线 “\”，变为普通字符。在导入时，如果数据中包含反斜线字符，那么启用 NO_BACKSLASH_ESCAPES 模式保证数据的正确性，是个不错的选择。

下面测试启用 NO_BACKSLASH_ESCAPES 模式前后，插入反斜杠的变化：

mysql> set session sql_mode = 'ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t_sql_mode_bs (context varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_sql_mode_bs VALUES('\beijing');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_sql_mode_bs;
+---------+
| context |
+---------+
|eijing   |
+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO t_sql_mode_bs VALUES('\\beijing');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_sql_mode_bs;
+-----------+
| context   |
+-----------+
|eijing     |
| \beijing  |
+-----------+
1 row in set (0.00 sec)

-- 开启 NO_BACKSLASH_ESCAPES 模式
mysql> set sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@sql_mode\G;
*************************** 1. row ***************************
@@sql_mode: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,NO_BACKSLASH_ESCAPES
1 row in set (0.00 sec)

mysql> INSERT INTO t_sql_mode_bs VALUES('\\beijing');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_sql_mode_bs;
+-----------+
| context   |
+-----------+
|eijing     |
| \beijing  |
| \\beijing |
+-----------+
2 rows in set (0.00 sec)

字符串连接符

启用 PIPES_AS_CONCAT 模式，将 “||” 视为字符串链接操作符 ，在 Oracle 等数据库中，将 “||” 视为字符串连接符，所以在其他数据库中 含有“||”的SQL，MySQL 中无法执行。为了解决这个问题，MySQL 增加了 PIPES_AS_CONCAT 模式：

mysql> set session sql_mode = 'ANSI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'abc'||'123';
+--------------+
| 'abc'||'123' |
+--------------+
| abc123       |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 'abc'||123;
+------------+
| 'abc'||123 |
+------------+
| abc123     |
+------------+
1 row in set (0.00 sec)

ANSI 模式中，包含了 PIPES_AS_CONCAT 模式，所以默认情况下，MySQL 5.7版本 将“||”，视为字符串链接操作符。

需要注意的是，在分区表和主从复制的环境中，要谨慎修改 SQL Mode。

分区表导致数据在不同的 SQL Mode下写入不同分区；

主从服务器的 SQL Mode 不同会导致复制的数据在主从服务器上写入逻辑不同；

以上两种都会导致数据混乱

常用的 SQL Mode

熟悉并了解经常使用的 SQL Mode 会帮助用户更好的使用它。

常用的 SQL Mode：

• ANSI

  • 等同于 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI 的组合模式，这种模式遇到异常倾向于警告而不是立刻返回错误。

• STRICT_TRANS_TABLES

  • STRICT_TRANS_TABLES 适用于事务表或非事务表。它是严格模式，不允许插入非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值会给出错误而不是警告。MySQL 5.7版本之后添加到了默认的SQL Mode中

• TRADITIONAL

  • TRADITIONAL 模式等同于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVSION_BY_ZERO、NO_AUTO_CREATE_USER、和 NO_ENGINE_SUBSTITUTION 的组合模式。

SQL Mode 的值大都是一些原子模式的组合，类似于角色和权限的关系。这样在实际应用时，只需要设置一个模式组合、就可以设置很多原子模式，大大简化了用户的工作。

TRADITIONAL 和 MySQL 5.7 的默认模式很相似，都是属于严格模式。主要区别是 TRADITIONAL 包含了原子模式： STRICT_ALL_TABLES 对于事务表写入规则完全一致，而非事务表（MySIAM）有细微差距。

在 STRICT_TRANS_TABLES 模式下，同时写入多条记录 ，只要第一条记录写入成功，那么后面的记录即使违反了严格模式的约束，也会自动转换为最接近的数据写入成功，而在 STRICT_ALL_TABLES 模式下则相反，只要多行记录中的任意一行违反严格模式的约束，则本次所有记录都不会写入。前者可能会导致数据的异常，而后者可能导致事务的不一致。避免这种问题最好的办法就是让数据逐条写入。

SQL Mode 在迁移中如何使用

如果 MySQL 与其他异构数据库之间有迁移的需求，那么 MySQL 中提供的数据组合模式就会对数据迁移有所帮助。

MySQL 常用的数据库 Mode

在数据迁移的过程中，可以设置 SQL_Mode 为 NO_TABLE_OPTIONS 模式，这样将去掉 show create table 中的 “engine” 关键字，获取通用的建表脚本：

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(20) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> set session sql_mode="NO_TABLE_OPTIONS";
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(20) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
)
1 row in set (0.00 sec)

小结

• SQL Mode 的 “严格模式” 为 MySQL 提供很好的数据校验功能，保证了数据的准确性。TRIADITIONAL 和 STRICT_TRANS_TABLES 是两种常用的严格模式，使用时要注意区别。
• SQL Mode 的多种模式可以灵活组合，组合后的模式可以更好的满足应用程序的需求。尤其是数据迁移中，SQL Mode 的使用更为重要。