1.SQL Mode解决的问题:
a.通过设置SQL Mode , 可以完成不同严格程度的数据校验,有效地保障数据准确性.
b.通过设置SQL Mode 为ANSI模式,来保证大多数SQL符合标准的SQL语法,应用在不同数据库之间迁移时,对业务SQL进行的修改较小.
2.MySQL SQL Mode
MySQL5.0上,默认SQL Mode(参数)为: REAL_AS_FLOAT , PIPES_AS_CONCAT, ANSI_QUOTES , GNORE_SPACE , ANSI .
这种模式下,允许插入超过字段长度的值,只是在插入后,MySQL会返回一个Warning.
通过修改SQL Mode 为 STRICT_TRANS_TABLES(严格模式),进行严格的数据校验,使得错误数据不能插入表中,从而保证数据的准确性.
1).查看SQL Mode 命令
SELECT @@sql_mode ;
2).设置SQL Mode
SET [SESSION|GLOBAL] sql_mode = 'xx_mode' ;
其中,SESSION : 只在本次连接中生效;
GLOBAL : 表示本次连接不生效,而对于新的连接则生效.
另外可以在MySQL启动时,通过 --sql-mode="xx_mode" 来设置SQL Mode ;
3.SQL Mode 常见功能
1).校验日期的合法性
严格模式下 , 如果不合法,则报错;例如4月没有31日,这样的数据无法插入.
非严格模式下, 会插入一条 0000-00-00 00:00:00 的数据
2).INSERT/UPDATE过程中
严格模式下 MOD(x,0) 这种会报错;
非严格模式下 会插入 null
3).NO_BACKSLASH_ESCAPES 模式
导入数据时,如果数据中含有反斜线字符,启用NO_BACKSLASH_ESCAPES模式来保证数据的正确性,是个不错的选择.
4).PIPES_AS_CONCAT模式
其他数据库,如Oracle使用 || 作为字符串连接符,这样的SQL正常情况下在 MySQL中无法执行, 为了解决这个问题 , MySQL 提供了 PIPES_AS_CONCAT 模式.
4.常用的SQL Mode
1).ANSI
等同于 REAL_AS_FLOAT , PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE 和ANSI组合模式 , 使语法和行为更符合标准SQL.
2).STRICT_TRANS_TABLES
严格模式
3)TRADITIONAL
等同于 STRICT_TRANS_TABLES , STRICT_ALL_TABLES , NO_ZERO_IN_DATE , NO_ZERO_DATE , ERROR_FOR_DIVISION_BY_ZERO , TRADITIONAL , NO_AUTO_CREATE_USER 组合模式 , 所以它也是严格模式.
4.SQL Mode 在数据迁移中的使用
1) 各种异构数据库在MySQL中的SQL 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 |
NO_TABLE_OPTIONS : 可以去掉建表语句中的 table options, 例如engine的设置, 这种模式下 , 可以获得通用的建表脚本.