SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量,默认为空。SQL_MODE的设置其实是比较冒险的一种设置,因为在这种设置下可以允许一些非法操作,比如可以将NULL插入NOT NULL的字段中,也可以插入一些非法日期,如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式,这样有些问题可以在数据库的设计和开发阶段就能发现,而如果在生产环境下运行数据库后发现这类问题,那么修改的代价将变得十分巨大。此外,正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。
对于SQL_MODE的设置,可以在MySQL的配置文件如my.cnf和my.ini中进行,也可以在客户端工具中进行,并且可以分别进行全局的设置或当前会话的设置。下面的命令可以用来查看当前SQL_MODE的设置情况。
※ mysql>SELECT @@global.sql_mode\G //查询全局的sql_mode变量值(全局的变量值约束的是以后所有的新的连接,直到MySQL服务器重启[重启之后就要用配置文件中的设置了],修改之后对本次连接并无效果而只对新的连接有效)。[\G按行横向显示查询结果, \g普通显示结果,后面可以不用分号了]
*************************** 1. row ***************************
@@global.sql_mode:
※ mysql> SELECT @@session.sql_mode\G; //查询当前会话的sql_mode值(当前会话的变量值只约束本次连接,修改之后对本次连接立即生效)。
或 SELECT @@sql_mode\G 【优先查询会话值,没有则查询全局值】
*************************** 1. row ***************************
@@session.sql_mode: NO_UNSIGNED_SUBTRACTION
可以看到当前全局的SQL_MODE设置为空,而当前会话的设置为NO_UNSIGNED_SUBTRACTION。通过以下语句可以将当前的SQL_MODE设置为严格模式。
mysql>SET @@global.sql_mode = 'xxx'; 或者
SET GLOBAL sql_mode='strict_trans_tables'; //设置全局的sql_mode值。
Query OK, 0 rows affected (0.00 sec)
也可以设置当前会话的SQL_MODE的值:
mysql>SET @@session.sql_mode = 'xxxx'; 或者
SET SESSION SQL_MODE='PAD_CHAR_TO_FULL_LENGTH'; 或者
mysql>SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';【这一句是优先设置会话值,如果会话中没有此变量则设置全局值】
严格模式是指将SQL_MODE变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。现在来看一下SQ