MENU

SQL Mode

2021 年 08 月 26 日 • 阅读: 147 • 编码,MySQL

与其他数据库不同,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

模式名称模式中各个Mode
DB2PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDBPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
MSSQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
ORACLEPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
POSTGRESQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS

在数据迁移的过程中,可以设置 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 的使用更为重要。
CC版权: 本篇博文采用《CC 协议》,转载必须注明作者和本文链接
最后编辑于: 2021 年 09 月 12 日