MySQL SQL Mode你了解多少？

　　SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量，默认为空。SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法操作，比如可以将NULL插入NOTNULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式，这样有些问题可以在数据库的设计和开发阶段就能发现，而如果在生产环境下运行数据库后发现这类问题，那么修改的代价将变得十分巨大。

　　MySQL SQL Mode简介

　　MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。可以使用“--sql-mode="modes"”选项，通过启动mysqld来设置默认的SQL模式。从MySQL4.1开始，也能在启动之后，使用SET[SESSION|GLOBAL]sql_mode='modes'语句，通过设置sql_mode变量更改模式。

　　常见方式

　　mysql可以运行在不同sqlmode模式下面，sqlmode模式定义了mysql应该支持的sql语法，数据校验等！

　　查看默认的sqlmode模式：

　　select@@sql_mode;

　　我的数据库是：

　　STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

　　在此模式下面，如果插入的数据的长度大于定义的长度，那么就会报错！

　　setsessionsql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI';

　　在这种模式下面：插入的数据的长度大于定义的时候，就会截取，并警告，但是可以插入进去

　　session：表示只在本次中有效

　　global：表示在本次连接中不生效，而对于新的连接就生效

　　启用NO_BACKSLASH_ESCAPES模式，使反斜线成为普通字符，在导入数据时候，如果数据中有反斜线，启用这个模式是个不错的选择

　　启用PIPES_AS_CNCAT模式，将||看成是普通字符串

　　sql mode值说明

　　ANSI

　　'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE和ANSI组合'，

　　这种模式使语法和行为更符合标准的sql

　　STRICT_TRANS_TABLES使用与事务和非事务表，严格模式

　　TRADITIONAL也是严格模式，对于插入不正确的值给出错误而不是警告。用在事务时，只要发生错误就立即回滚

　　通常在Linux下安装完mysql后，默认的sql-mode值是空，在这种情形下mysql执行的是一种不严格的检查，例如日期字段可以插入’0000-00-0000:00:00’这样的值，还有如果要插入的字段长度超过列定义的长度，那么mysql不会终止操作，而是会自动截断后面的字符继续插入操作，如下例：

　　我们发现插入的字符被自动截断了，但是如果我们本意希望如果长度超过限制就报错，那么我们可以设置sql_mode为STRICT_TRANS_TABLES，如下：

　　mysql>setsessionsql_mode='STRICT_TRANS_TABLES'

　　这样我们再执行同样的操作，mysql就会告诉我们插入的值太长，操作被终止，如下：

　　mysql>insertintot5values('abcd');

　　ERROR1406(22001):Datatoolongforcolumn'c1'atrow1

   　说明：如果把sql_mode的值设置成后面的两个值（也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，mysql将会给出错误，并且放弃insert/update操作。在我们的一般应用中建议使用这两种模式，而不是使用默认的空或ANSI模式。但是需要注意的问题是，如果数据库运行在严格模式下，并且你的存储引擎不支持事务，那么有数据不一致的风险存在，比如一组sql中有两个DML语句，如果后面的一个出现了问题，但是前面的已经操作成功，那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失，从而得到一个合适的选择。

　　SQLMode与可移植性

　　如果mysql与其它异构数据库之间有数据移植的需求的话，那么下面的sql_mode的组合设置可以达到相应的效果：

　　SQLMode与数据效验

　　SQLMode还可以实现对数据效验和转移等功能如：

　　1.效验日期数据合法性.

　　2.在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误

　　3.将‘"’视为识别符引号(‘`’引号字符)

　　4.禁用反斜线字符(‘\\’)做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式，反斜线则成为普通字符。

　　5.将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。

　　小编结语：

　　更多内容尽在课课家教育！