MySQL规范与疑难杂症

1.SQL_MODE

　　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;

　　结果如下：
+---------------------+
| @@global.sql_mode   |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.07 sec)
　　
　　设置SQL_MODE命令：

set session sql_mode='STRICT_TRANS_TABLES';

 

　　各种模式：
　　严格模式是指将SQL_MODE变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。现在来看一下SQL_MODE可以设置的选项。
       STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表(例如表的存储引擎为InnoDB)中，则中断当前的操作不影响非事务表(例如表的存储引擎为MyISAM)。
　　ALLOW_INVALID_DATES：该选项并不完全对日期的合法性进行检查，只检查月份是否在1～12之间，日期是否在1～31之间。该模式仅对DATE和DATETIME类型有效，而对TIMESTAMP无效，因为TIMESTAMP总是要求一个合法的输入。
　　ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它将被解释为识别符，示例如下：

mysql> CREATE TABLE z ( a VARCHAR(10))ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql>INSERT INTO z SELECT "aaa";
Query OK, 1 rows affected (0.00 sec)

mysql> SET sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO z SELECT "aaa";
ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'

 注意：每次断开后，就要重新执行上面的SQL，才能使sql_mode模式生效。