MySQL数据类型:SQL_MODE设置不容忽视

        【IT168 技术】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;

*************************** 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设置为空,而当前会话的设置为NO_UNSIGNED_SUBTRACTION。通过以下语句可以将当前的SQL_MODE设置为严格模式。

  mysql> SET GLOBAL sql_mode=’strict_trans_tables’;

Query OK, 0 rows affected (0.00 sec)

严格模式是指将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’

ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,那么数据被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

HIGH_NOT_PRECEDENCE NOT:操作符的优先顺序是表达式。例如,NOT a BETWEEN b AND c被解释为NOT(a BETWEEN b AND c),在一些旧版本MySQL中, 前面的表达式被解释为(NOT a)BETWEEN b AND c。启用HIGH_NOT_PRECEDENCE SQL模式,可以获得以前旧版本的更高优先级的结果。下面看一个例子:

  mysql> SELECT 0 BETWEEN -1 AND 1\G;

*************************** 1. row ***************************

0 BETWEEN -1 AND 1: 1

1 row in set (0.00 sec)

0在-1到1之间,所以返回1,如果加上NOT,则返回0,过程如下:

  mysql> SELECT @@sql_mode\G;

*************************** 1. row ***************************

@@sql_mode:

1 row in set (0.00 sec)

mysql> SELECT not 0 BETWEEN -1 AND 1\G;

*************************** 1. row ***************************

NOT 0 BETWEEN -1 AND 1: 0

1 row in set (0.00 sec)

但是如果启用HIGH_NOT_PRECEDENCE模式,则SELECT NOT 0 BETWEEN -1 AND 1被解释为SELECT(NOT 0)BETWEEN -1 AND 1,结果就完全相反,如下所示:

  mysql> SELECT NOT 0 BETWEEN -1 AND 1\G;

*************************** 1. row ***************************

NOT 0 BETWEEN -1 AND 1: 1

1 row in set (0.00 sec)

从上述例子中还能看出,在MySQL数据库中BETWEEN a AND b被解释为[a,b]。下面做两个简单的测试。

  mysql> SELECT 1 BETWEEN -1 AND 1\G;

*************************** 1. row ***************************

1 BETWEEN -1 AND 1: 1

1 row in set (0.00 sec)

mysql> SELECT -1 BETWEEN -1 AND 1\G;

*************************** 1. row ***************************

-1 BETWEEN -1 AND 1: 1

1 row in set (0.00 sec)

IGNORE_SPACE:函数名和括号“(”之间有空格。除了增加一些烦恼,这个选项好像没有任何好处,要访问保存为关键字的数据库、表或列名,用户必须引用该选项。例如某个表中有user这一列,而MySQL数据库中又有user这个函数, user会被解释为函数,如果想要选择user这一列,则需要引用。

NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户。

NO_AUTO_VALUE_ON_ZERO:该选项影响列为自增长的插入。在默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

NO_BACKSLASH_ESCAPES:反斜杠“\”作为普通字符而非转义符,示例如下:

  mysql> SET sql_mode=”;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ‘\\’\G;

*************************** 1. row ***************************

\: \

1 row in set (0.00 sec)

mysql> SET sql_mode=’NO_BACKSLASH_ESCAPES’;

Query OK, 0 rows affected (0.00 sec)

mysql> SET ‘\\’\G;

*************************** 1. row ***************************

\\: \\

1 row in set (0.00 sec)

0 Comments
Leave a Reply