SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量,5.6以前的版本默认为空,mysql 5.6中默认用的是严格模式了。SQL_MODE的默认设置其实是比较冒险的一种设置,因为在这种设置下可以允许一些非法操作,在5.6版本设置sql_mode为空:比如varchar(2),如果插入'abc',那么只存前两位;如果有默认有符号tinyint,插入128,那么只保存到有符号tinyint的最大数127;如果时间格式,插入不符合格式的时间类型,如‘abs’、'2012-23-58',那么数据保存为'0000-00-00';
如果在生产环境下运行数据库后发现这类问题,那么修改的代价将变得十分巨大。此外,正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。
对于SQL_MODE的设置,可以在MySQL的配置文件如my.cnf和my.ini中进行,也可以在客户端工具中进行,并且可以分别进行全局的设置或当前会话的设置。下面的命令可以用来查看当前SQL_MODE的设置情况。
*************************** 1 . row ***************************
@@global.sql_mode:
1 row in set ( 0.00 sec)
mysql > SELECT @@session.sql_mode \ G;
*************************** 1 . row ***************************
@@session.sql_mode: NO_UNSIGNED_SUBTRACTION
1 row in set ( 0.00 sec)
sql_mode常用值如下:
红色(强烈建议)
蓝色(根据需要)
绿色(一般不会使用,除非你真的十分确定他的作用)
ONLY_FULL_GROUP_BY:
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO:
该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
STRICT_TRANS_TABLES:
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_ZERO_IN_DATE:
在严格模式下,不允许日期和月份为零
NO_ZERO_DATE:
设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO:
在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER:
禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION:
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
PIPES_AS_CONCAT:
将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
ANSI_QUOTES:
启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符