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 上的数据更方便的迁移到目标数据库中

a、查看当前数据库的 SQL Mode ： select @@sql_mode;

REAL_AS_FOLAT  PIPES_AS_CONCAT  ANSI_QUOTES  GNORE_SPACE  ANSI  -----允许插入超过字段长度的值，返回一个warning
STRICT_TRANS_TABLES(严格模式) ------实现数据的严格校验，使错误数据不能插入表中

b、设置SQL Mode 为 STRICT_TRANS_TABLES：set session sql_mode='STRICT_TRANS_TABLES'; （MySQL4.1开始有效）

语法： SET [SESSION|GLOBAL] sql_mode='modes'

SESSION 表示只在本次链接中生效；
GLOBAL 表示在本次连接中并不生效，而对于新的连接则生效

也可以通过使用“--sql-mode="modes"” 选项在MySQL启动时设置sql_mode

2、SQL Mode 的常见功能

a、校验日期数据合法性
b、在INSERT 或 UPDATE 过程中，如果SQL Mode 处于 TRADITION模式，运行MOD(x,0)会产生错误，因为TRADITIONAL也属严格模式
  在含有MOD的运算中根据实际情况设定好sql_mode
c、启用NO_BACKSLASH_ESCAPES模式，使反斜线成为普通字符。如果导入的数据存在反斜线，可以设置此模式，保证导入数据的正确性
d、启用 PIPES_AS_CONCAT模式，将“||”视为字符串的连接操作符。在Oracle等数据库中，“||”被视为字符串的连接操作符。
MySQL默认情况下支持 PIPES_AS_CONCAT模式

3、MySQL 常用的 SQL Mode

sql_mode 值 描述

ANSI ： 等同于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ANSI组合模式，这种模式使
语法和行为更符合标准的SQL

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组合模式，所以他也是严格模式，对于插入 不正确的 值给 出错误而不是警告。可以应用在事务表和非事务表，用在事务表时只要出现错误就会立即回滚

可以发现，SQL Mode 的值其实都是一些原子模式的组合，类似于角色和权限的关系。这样当实际应用时，只需要设置一个模式组合就可以设置很多的原子模式，大大方便了用户的工作。

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

MySQL 提供了很多数据库的组合模式名称，例如“ORACLE”、“DB2”等，这些模式组合是由很多小的sql_mode组合而成，在异构数据库之间迁移
数据时可以尝试使用这些模式来导出适合于目标数据库格式的数据，这样就使得导出数据更容易导入目标数据库。

MySQL中常用的 sql_mode 组合：
组合后的模式名称 组合模式中的各个sql_mode

DB2 ： PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTION、NO_FIELD_OPTIONS

MAXDB ： PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTION、NO_FIELD_OPTIONS、
NO_ATUO_CREATE_USER

MSSQL： PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTION、NO_FIELD_OPTIONS

ORACLE ： PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTION、NO_FIELD_OPTIONS、
NO_ATUO_CREATE_USER

POSTGRESQL ： PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTION、NO_FIELD_OPTIONS

例：在数据库迁移过程中，可以设置SQL Mode为NO_TABLE_OPTIONS 模式，这样将去掉show create table 中的 “engine”关键字，获得通用
的建表脚本