SQL Mode及相关问题
- SQL Mode可以解决几类问题：
1. 通过设置SQL Mode，可以完成不同严格程度的数据校验，有效的保障数据准确性。
2. 通过设置SQL Mode为ANSI模式，来保证大多数SQL符合标准的SQL语法，这样应用在不同数据库之间进行迁移时，则不需要对业务SQL进行较大 的修改。
3. 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中。
- 查看默认地SQL Mode:
mysql> SELECT @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)- 范例
-- 测试表
mysql> CREATE TABLE sqlmode_test(
-> name VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)
-- 插入一个超长的记录，会出现提醒
mysql> INSERT INTO sqlmode_test VALUES ('abcdefghiklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
-- 数据被截断了
mysql> SELECT * FROM sqlmode_test;
+------------+
| name |
+------------+
| abcdefghik |
+------------+
1 row in set (0.00 sec)
-- 修改SQL_MODE，再次插入，会报错
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO sqlmode_test VALUES ('abcdefghiklmnopqrstuvwxyz');
ERROR 1406 (22001): Data too long for column 'name' at row 1- TRADITIONAL：MOD(?, 0)会报错，不正确的日期也会报错。
- NO_BACKSLASH_ESCAPES：使反斜杠\成为普通字符。
- PIPES_AS_CONCAT: 使用||作为字符串连接符。
常用的一些SQL Mode：
| sql_mode值 |
描述 |
| ANSI |
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI的组合 |
| STRICT_TRANS_TABLES |
使用于事务表和非事务表，属于严格模式，不允许非法日期，也不允许超长字段，对于不正确的 值，都会给出错误。 |
| 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的组合 |
不同MySQL版本SQL Mode不同，可参考http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo
SQL Mode在迁移中的使用：
- 为了在不同数据库中的数据迁移，MySQL也支持一些不同数据库模式，如：
-
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
-
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
-
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
-
Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
-
Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
-
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
-
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS.
- 在数据迁移过程中，可以设置SQLMode为NO_TABLE_OPTIONS模式，这样将去掉create table中的"engine"关键字，如：
具体详细可参考：
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
不吝指正。
