MySQL--SQL Mode及相关问题

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

 

1) MySQL SQL Mode 简介

• 在 MySQL 中，SQL Mode 常用来解决下面几类问题。

　　　　通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。

　　　　通过设置 SQL Mode 为 ANSI 模式，来保证大多数 SQL 符合标准的 SQL 语法，这样在不同的数据库之间进行迁移时，则不需要对业务 SQL 进行较大的修改。

　　　　在不同数据库之间进行数据迁移之前，通过设置 SQL Mode 可以使 MySQL 上的数据更方便地迁移到目标数据库中。

• 查看默认的SQL Mode

　　　　SELECT @@sql_mode

• 修改 SQL Mode

　　　　SET [SESSION|GLOBAL] sql_mode='modes'　　#其中 SESSION 选项表示只在本次连接中生效；而 GLOBAL 选项表示在本次连接中并不生效，而对于新的连接则生效，这种方法在 MySQL 4.1 开始有效。

　　　　或　　在启动的时候通过使用 --sql-mode='modes'

 

2) SQL Mode 的常见功能

• 校验日期数据合法性
• 在 INSERT 或 UPDATE 过程中，如果 SQL Mode 处于 TRADITIONAL 模式，那么运行 MOD(X, 0) 就会产生错误，这是因为 TRADITIONAL 也属于严格模式，在非严格模式下返回的结果是 NULL，所以在含有 MOD 的运算中要根据实际情况设定好 SQL Mode
• 启用 NO_BACKSLASH_ESCAPES 模式，是反斜线称为普通字符。在导入数据时，如果数据中含有反斜线字符，那么启用 NO_BACKSLASH_ESCAPES 模式保证数据的正确性，是个不错的选择。
• 启用 PIPES_AS_CONCAT 模式，将 || 视为字符串连接操作符，在 Oracle 等数据库中，|| 被视为字符串的连接操作符，所以，在其他数据库中含有 || 操作符的 SQL 在 MySQL 中将无法执行，为了解决这个问题，MySQL 提供了 PIPES_AS_CONCAT 模式。

 

3) 常用的 SQL Mode

SQL Mode 值	描述
ANSI	等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 ANSI 组合模式，这种模式使语法和行为更符合标准的 SQL
STRICT_TRANS_TABLES	STRICT_TRANS_TABLES 适用于事务表和非事务表，他是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告
TRADITIONAL	TRADITIONAL 模式等同于 STRICT_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRADITIONAL 和 NO_AUTO_CREATE_USER 组合模式，所以他也是严格模式，对于插入不正确的值会给出错误而不是警告。可以应用在事务表和非事务表，用在事务表时，只要出现错误就会立即回滚。

 

4) SQL Mode 在迁移中如何使用

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

在异构数据库之间迁移数据时可以尝试使用这些模式来导出适合于目标数据库的数据，这样就使得导出数据更容易导入目标数据库。

组合后的模式名称	组合模式中的各个sql_mode
DB2	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FILED_OPTIONS
MAXDB	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FILED_OPTIONS, NO_AUTO_CREATE_USER
MSSQL	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FILED_OPTIONS
ORACLE	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FILED_OPTIONS, NO_AUTO_CREATE_USER
POSTGRESQL	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FILED_OPTIONS