MySQL SQL_MODE明细

ゝ一世哀愁。 2023-03-02 10:56 1阅读 0赞

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编程》