MySQL SQL_MODE詳解

神諭丶發表於2015-10-08
MySql

SQL_MODE：通過對其正確的設定可以完成一些約束檢查的工作，設定時，可在配置檔案my.cnf或my.ini中進行，也可在客戶端中進行，並可分別進行全域性的設定或當前會話的設定。

檢視SQL_MODE設定情況：
  1. mysql> SHOW VARIABLES LIKE 'SQL_MODE';
  2. +---------------+--------------------------------------------+
  3. | Variable_name | Value                                      |
  4. +---------------+--------------------------------------------+
  5. | sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
  6. +---------------+--------------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT @@global.SQL_MODE;
  10. +--------------------------------------------+
  11. | @@global.SQL_MODE                          |
  12. +--------------------------------------------+
  13. | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
  14. +--------------------------------------------+
  15. 1 row in set (0.00 sec)

  17. mysql> SELECT @@session.SQL_MODE;
  18. +--------------------------------------------+
  19. | @@session.SQL_MODE                         |
  20. +--------------------------------------------+
  21. | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
  22. +--------------------------------------------+
  23. 1 row in set (0.00 sec)


在client設定方法很簡單：
  1. mysql> SET global sql_mode='STRICT_TRANS_TABLES';
  2. 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：
啟用後，不能用雙引號來引用字串，因為"（雙引號）將被解釋為識別符號
  1. mysql> CREATE TABLE a ( a char(5));
  2. Query OK, 0 rows affected (0.26 sec)

  4. mysql> INSERT INTO a SELECT 'abc';
  5. Query OK, 1 row affected (0.05 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. mysql> SET SQL_MODE='ANSI_QUOTES';
  9. Query OK, 0 rows affected (0.00 sec)

  11. mysql> INSERT INTO a SELECT "abc";
  12. ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

  14. mysql> SELECT @@session.sql_mode;
  15. +--------------------+
  16. | @@session.sql_mode |
  17. +--------------------+
  18. | ANSI_QUOTES        |
  19. +--------------------+
  20. 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：
啟用後，忽略函式名和括號"("之間空格，要訪問儲存為關鍵字的資料庫名，表名，列名時，需啟用。
  1. mysql> SELECT NOW ();
  2. ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
  3. mysql> SET SQL_MODE='IGNORE_SPACE';
  4. Query OK, 0 rows affected (0.00 sec)

  6. mysql> SELECT NOW ();
  7. +---------------------+
  8. | NOW ()              |
  9. +---------------------+
  10. | 2015-10-08 17:49:35 |
  11. +---------------------+
  12. 1 row in set (0.00 sec)

NO_AUTO_CREATE_USER：
禁止GRANT建立密碼為空的使用者。

NO_AUTO_VALUE_ON_ZERO：
在自增長的列中插入0或NULL將不會是下一個自增長值。

NO_BACKSLASH_ESCAPES：
反斜槓"\"作為普通字元而非轉義字元
  1. mysql> SET SQL_MODE='';
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> SELECT '\\';
  5. +---+
  6. | \ |
  7. +---+
  8. | \ |
  9. +---+
  10. 1 row in set (0.00 sec)

  12. mysql> SET SQL_MODE='NO_BACKSLASH_ESCAPES';
  13. Query OK, 0 rows affected (0.00 sec)

  15. mysql> SELECT '\\';
  16. +----+
  17. | \\ |
  18. +----+
  19. | \\ |
  20. +----+
  21. 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型別將不會截斷空洞資料；
  1. mysql> CREATE TABLE a ( a char(10), b varchar(10));
  2. Query OK, 0 rows affected (0.29 sec)

  4. mysql> INSERT INTO a SELECT 'a','b';
  5. Query OK, 1 row affected (0.03 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0;

  8. mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
  9. +----------------+----------------+
  10. | CHAR_LENGTH(a)  | CHAR_LENGTH(b)  |
  11. +----------------+----------------+
  12. | 1              | 1              |
  13. +----------------+----------------+
  14. 1 row in set (0.01 sec)

  16. mysql> SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';
  17. Query OK, 0 rows affected (0.00 sec)

  19. mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
  20. +----------------+----------------+
  21. | CHAR_LENGTH(a) | CHAR_LENGTH(b) |
  22. +----------------+----------------+
  23. | 10             | 1              |
  24. +----------------+----------------+
  25. 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程式設計》


作者公眾號（持續更新）


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1813501/，如需轉載，請註明出處，否則將追究法律責任。

相關文章