MySQL SQL_MODE详解-神谕丶-ITPUB博客

ITPUB首页　| 　论坛 | 认证专区 | 博客登录 | 注册

Fantasy life

明月三千里

首页　| 　博文目录　| 　关于我

神谕丶

博客访问： 785018
博文数量： 156
用户组：普通用户
注册时间： 2014-07-28 23:51
认证徽章：

个人简介

转载请注明出处：作者：神谕。博客地址：http://blog.itpub.net/29773961/。

文章分类

全部博文（156）

troubleshooting（25）
MySQL-admin（56）
MySQL-dev（25）
Oracle DB（7）
Redis（1）
MongoDB（4）
LinuxServer（29）
其他分享（10）
未分配的博文（0）

文章存档

2018年（4）

2017年（23）

2016年（38）

2015年（66）

2014年（25）

我的朋友

最近访客

推荐博文

MySQL SQL_MODE详解

分类： MySQL

2015-10-08 16:34:40

SQL_MODE：通过对其正确的设置可以完成一些约束检查的工作，设置时，可在配置文件my.cnf或my.ini中进行，也可在客户端中进行，并可分别进行全局的设置或当前会话的设置。

查看SQL_MODE设置情况：

mysql> SHOW VARIABLES LIKE 'SQL_MODE';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@global.SQL_MODE;
+--------------------------------------------+
| @@global.SQL_MODE |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@session.SQL_MODE;
+--------------------------------------------+
| @@session.SQL_MODE |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

在client设置方法很简单：

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

这样可以将sql_mode设置为“严格模式”。

严格模式是指将sql_mode设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。

其他可以设置的选项有：

STRICT_TRANS_TALES（严格模式）：
只对支持事务的表启用严格模式

STRICT_ALL_TABLES（严格模式）：
对所有引擎的表都启用严格模式

严格模式：
在此模式下，一旦任何操作的数据产生问题，都将终止当前的操作，对于启用STRICT_ALL_TABLES的非事务引擎而言，这时数据可能停留在一个未知的状态，因此需非常小心这个选项可能带来的潜在影响。

ALLOW_INVALID_DATES：
不完全对日期合法性作检查，只检查月份是否在1~12，日期是否在1~31之间；仅对DATE和DATETIME有效，而对TIMESTAMP无效，因为TIMESTAMP总要求一个合法的输入。

ANSI_QUOTES：
启用后，不能用双引号来引用字符串，因为"（双引号）将被解释为标识符

mysql> CREATE TABLE a ( a char(5));
Query OK, 0 rows affected (0.26 sec)
mysql> INSERT INTO a SELECT 'abc';
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SET SQL_MODE='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO a SELECT "abc";
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ANSI_QUOTES |
+--------------------+
1 row in set (0.00 sec)

ERROR_FOR_DIVISION_BY_ZERO：
启用后，在insert或update过程中，若数据被零除（或MOD(x,0），则产生错误，若未启用，则产生警告，数据被零除时系统返回NULL。

HIGH_NOT_PRECEDENCE：
启用后，可获得以前旧版本的优先级:
NOT a BETWEEN b AND c这个语句:
now: NOT (a BETWEEN b AND c)
before: (NOT a) BETWEEN b AND c

IGNORE_SPACE：
启用后，忽略函数名和括号"("之间空格，要访问保存为关键字的数据库名，表名，列名时，需启用。

mysql> SELECT NOW ();
ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
mysql> SET SQL_MODE='IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NOW ();
+---------------------+
| NOW () |
+---------------------+
| 2015-10-08 17:49:35 |
+---------------------+
1 row in set (0.00 sec)

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

NO_AUTO_VALUE_ON_ZERO：
在自增长的列中插入0或NULL将不会是下一个自增长值。

NO_BACKSLASH_ESCAPES：
反斜杠"\"作为普通字符而非转义字符

mysql> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT '\\';
+---+
| \ |
+---+
| \ |
+---+
1 row in set (0.00 sec)
mysql> SET SQL_MODE='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT '\\';
+----+
| \\ |
+----+
| \\ |
+----+
1 row in set (0.00 sec)

NO_DIR_IN_CREATE：
在创建表时忽略所有index directory和data directory的选项。

NO_ENGINE_SUBSTITUTION：
启用后，若需要的存储引擎被禁用或未编译，则抛出错误；未启用时将用默认的存储引擎代替，并抛出一个异常。

NO_UNSIGNED_SUBSTRACTION：
启用后，两个UNSIGNED类型相减返回SIGNED类型。

NO_ZERO_DATE：
启用后，不允许插入“0000-00-00 00:00:00”形如此类的零日期，这将抛出一个错误，若未启用，则可插入但仅会抛出一个警告。

NO_ZERO_IN_DATE：
启用后，不允许月份和日期为零，和NO_ZERO_DATE一起启用，如“1999-01-00”将抛出错误而非警告。
若单独启用本项，则会抛出warning，然后插入如“0000-00-00 00:00:00”。

ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，若select中的列没有在group by中出现，那么这句SQL是不合法的。

PAD_CHAR_TO_FULL_LENGTH：
启用后，对于CHAR类型将不会截断空洞数据；

mysql> CREATE TABLE a ( a char(10), b varchar(10));
Query OK, 0 rows affected (0.29 sec)
mysql> INSERT INTO a SELECT 'a','b';
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0;
mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
+----------------+----------------+
| CHAR_LENGTH(a) | CHAR_LENGTH(b) |
+----------------+----------------+
| 1 | 1 |
+----------------+----------------+
1 row in set (0.01 sec)
mysql> SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
+----------------+----------------+
| CHAR_LENGTH(a) | CHAR_LENGTH(b) |
+----------------+----------------+
| 10 | 1 |
+----------------+----------------+
1 row in set (0.00 sec)

PIPES_AS_CONCAT：
将"||"视为连接操作符而非“或运算符”。

REAL_AS_FLOAT：
将REAL视为FLOAT的同义词而非DOUBLE的同义词。

组合选项：
·ANSI：
REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ANSI
·ORACLE：
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER、ORACLE
·TRADITIONAL：
STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION、TRADITIONAL
·MSSQL：
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、MSSQL、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、MSSQL
·DB2：
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、DB2
·MYSQL323：
HIGH_NOT_PRECEDENCE、MYSQL323
·MYSQL40：
HIGH_NOT_PRECEDENCE、MYSQL40
·MAXDB：
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER、MAXDB

参考文档：
MySQL 5.6 Reference Manual
姜承尧《MySQL技术内幕：SQL编程》

阅读(12351) | 评论(0) | 转发(0) |

上一篇：MySQL 查询处理 SQL查询执行顺序

下一篇：MySQL 字符串函数大全