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程式設計》

作者公眾號（持續更新）

MySQL SQL_MODE詳解

相關文章