SQL Mode
SQL Mode简介
在MySQL中，SQL Mode常常用来解决以下问题：
1.通过设置SQL Mode，可以完成不同严格程度的数据校验，有效保证数据准确性。
2.通过设置SQL Mode为ANSI模式，来保证大多数SQL是符合标准的SQL语法，这样应用在不同数据库之间迁移时，则不需要对业务SQL进行较大修改。
3.在不同数据库进行数据迁移时，通过设置SQL Mode可以使得MySQL上的数据更方便迁移到目标数据库
在MySQL5.7.18上，查询默认的SQL Mode(@@sql_mode)为STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES为严格模式，实现了数据的严格校验，对不符合数据类型的错误数据不能插入表中，保证了数据的准确性。
mysql> select @@sql_mode; # 严格模式
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> desc transaction;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | UNI | NULL | |
| account | double | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>insert into transaction values(null, "123456789abcdefghijkkkkkkkkkkkkkkk", 2000);
ERROR 1406 (22001): Data too long for column 'name' at row 1
如果更改SQL Mode为ANSI模式，错误数据也会被插入表中, 对于超过长度的值，会进行截取。
mysql> set session sql_mode="ANSI"; #ANSI模式
Query OK, 0 rows affected (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> insert into transaction values(null,'1111111111111111222222222222223333333333333',2000);
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> select name from transaction where id=27; # 允许数据插入但多余长度被截断
+----------------------+
| name |
+----------------------+
| 11111111111111112222 |
+----------------------+
1 row in set (0.00 sec)
SQL Mode的常见功能
1 校验日期格式的合法性
例如：给定一个非法日期如“2019-2-31”进行插入操作
mysql> select @@sql_mode; # ANSI模式
+--------------------------------------------------------------------------------+
| @@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 t1(d date);
Query OK, 0 rows affected (0.29 sec)
mysql> SET @invalidData='2017-2-31';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(@invalidData);
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from t1; # ANSI模式支持非法数据插入，插入值为“0000-00-00"
+------------+
| d |
+------------+
| 0000-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> insert into t1 values(@invalidData);
ERROR 1292 (22007): Incorrect date value: '2017-2-31' for column 'd' at row 1
mysql>
2 在ANSI模式下执行MOD(x, 0)不会出错，插入时数值变为NULL。
3 启用NO_BACKSLASH_ESCAPES使得反斜杠成普通字符。
mysql> select @@sql_mode; #ANSI模式下，没有启用NO_BACKSLASH_ESCAPES
+--------------------------------------------------------------------------------+
| @@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> set @escapeStr = '\\a\s\t\n123';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t2; # 出现转义字符被执行
+----------+
| url |
+----------+
| \as
123 |
+----------+
1 row in set (0.00 sec)
mysql> set session sql_mode='ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)
mysql> set @escapeStr = '\\begin';
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table t2;
Query OK, 0 rows affected (0.20 sec)
mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t2;
+---------+
| url |
+---------+
| \\begin |
+---------+
1 row in set (0.00 sec)
mysql>
4 启用PIPES_AS_CONTACT模式。将“||”视为字符串连接符号(同CONCAT)
mysql> select '1'||'2' as A, concat('3', '4') as B;
+----+----+
| A | B |
+----+----+
| 12 | 34 |
+----+----+
1 row in set (0.00 sec)
常见的SQL Mode
| sql_mode值 | 描述 |
|---|---|
| ANSI | 等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
| STRICT_TRANS_TABLES | 适用于事务处理，对于非法数据直接抛出错误，而非warning |
| TRADITIONAL | 等同于STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION， 属于严格模式，对于非法数据直接抛出错误，可用在事务表中，出现错误立即回滚 |
SQL Mode在数据迁移中如何使用
MySQL提供了很多数据库的组合模式，例如“ORACLE、POSTGRESQL”等
其组合形式可参考https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html[https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html]: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html。
