MySQL SQL Mode 及相关问题

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


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


sql_mode 的修改方法：
 SET [SESSION|GLOBAL] sql_mode='modes'，其中SESSION 选项表示只在本次连接中生效；而GLOBAL 选项表示在本次连接中并不生效，而对于新的连接则生效。
另外，也可以通过使用“--sql-mode="modes"”选项，在MySQL 启动时设置sql_mode。
还可以，通过my.cnf参数文件设置：sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


下面介绍一下SQL Mode 的常见功能：
修改sql_mode 为STRICT_TRANS_TABLES（严格模式）实现了数据的严格校验，使错误数据不能插入表中，从而保证了数据的准确性
在ANSI 模式下，非法日期可以插入，但是插入值却变为“0000-00-00 00:00:00”，并且系统给出了warning；而在TRADITIONAL 模式下，在直接提示日期非法，拒绝插入。
如果SQL MODE 处于TRADITIONAL 模式，运行MOD(X，0)会产生错误，因为TRADITIONAL 也属于严格模式，在非严格模式下 MOD(X，0)返回的结果是NULL
启用NO_BACKSLASH_ESCAPES 模式，使反斜线成为普通字符。在导入数据时，如果数据中含有反斜线字符，启用NO_BACKSLASH_ESCAPES 模式保证数据的正确性，是个不错的选择。
启用PIPES_AS_CONCAT 模式。将“||”视为字符串连接操作符，在Oracle 等数据库中，“||”被视为字符串的连接操作符，所以，在其他数据库中含有“||”操作符的SQL在MySQL 中将无法执行，为了解决这个问题，MySQL 提供了PIPES_AS_CONCAT 模式。
在数据迁移过程中，可以设置SQL Mode 为NO_TABLE_OPTIONS 模式，这样将去掉show create table 中的“engine”关键字，获得通用的建表脚本。


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


MySQL 中的常用数据库Mode：
DB2： PIPES_AS_CONCAT 、ANSI_QUOTES 、IGNORE_SPACE 、NO_KEY_OPTIONS 、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDB： PIPES_AS_CONCAT 、ANSI_QUOTES 、IGNORE_SPACE 、NO_KEY_OPTIONS 、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER
MSSQL： PIPES_AS_CONCAT 、ANSI_QUOTES 、IGNORE_SPACE 、NO_KEY_OPTIONS 、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS
ORACLE： PIPES_AS_CONCAT 、ANSI_QUOTES 、IGNORE_SPACE 、NO_KEY_OPTIONS 、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
POSTGRESQL： PIPES_AS_CONCAT 、ANSI_QUOTES 、IGNORE_SPACE 、NO_KEY_OPTIONS 、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS