MySQL資料類型：SQL_MODE設定不容忽視

標籤：

【IT168 技術】SQL_MODE可能是比較容易讓開發人員和DBA忽略的一個變數，預設為空白。SQL_MODE的設定其實是比較冒險的一種設定，因為在這種設定下可以允許一些非法操作，比如可以將NULL插入NOT NULL的欄位中，也可以插入一些非法日期，如“2012-12-32”。因此在生產環境中強烈建議開發人員將這個值設為strict 模式，這樣有些問題可以在資料庫的設計和開發階段就能發現，而如果在生產環境下運行資料庫後發現這類問題，那麼修改的代價將變得十分巨大。此外，正確地設定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設定為strict 模式。

mysql> SET GLOBAL sql_mode=‘strict_trans_tables‘;　　Query OK, 0 rows affected (0.00 sec)

strict 模式是指將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)

　　

NO_DIR_IN_CREATE：在建立表時忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項。

　　NO_ENGINE_SUBSTITUTION：如果需要的儲存引擎被禁用或未編譯，那麼拋出錯誤。預設用預設的儲存引擎替代，並拋出一個異常。

　　NO_UNSIGNED_SUBTRACTION：之前已經介紹過，啟用這個選項後兩個UNSIGNED類型相減返回SIGNED類型。

　　NO_ZERO_DATE：在非strict 模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL資料庫僅拋出一個警告。而啟用該選項後，MySQL資料庫不允許插入零日期，插入零日期會拋出錯誤而非警告。

　　NO_ZERO_IN_DATE：在strict 模式下，不允許日期和月份為零。如“2011-00-01”和“2011-01-00”這樣的格式是不允許的。採用日期或月份為零的格式時MySQL都會直接拋出錯誤而非警告，樣本如下：

mysql> SET sql_mode=‘NO_ZERO_IN_DATE‘;　　Query OK, 0 rows affected (0.00 sec)　　mysql> CREATE TABLE a ( a DATETIME );　　Query OK, 0 rows affected (0.04 sec)　　mysql> INSERT INTO a SELECT ‘2011-01-00‘;　　ERROR 1292 (22007): Incorrect datetime value: ‘2011-01-00‘ for column ‘a‘ at row 1

　　ONLY_FULL_GROUP_BY：對於GROUP BY彙總操作，如果在SELECT中的列沒有在GROUP BY中出現，那麼這句SQL是不合法的，因為a列不在GROUP BY從句中，樣本如下：

mysql> SET sql_mode=‘ONLY_FULL_GROUP_BY‘;　　Query OK, 0 rows affected (0.00 sec)　　mysql> SELECT a,SUM(b) FROM t GROUP BY b;　　ERROR 1055 (42000): ‘test.t.a‘ isn‘t in GROUP BY

　　PAD_CHAR_TO_FULL_LENGTH：對於CHAR類型欄位，不要截斷空洞資料。空洞資料就是自動填滿值為0x20的資料。先來看MySQL資料庫在預設情況下的表現。

mysql> CREATE TABLE t ( a CHAR(10) );　　Query OK, 0 rows affected (0.04 sec)　　mysql> INSERT INTO t SELECT ‘a‘;　　Query OK, 1 row affected (0.01 sec)　　Records: 1 Duplicates: 0 Warnings: 0　　mysql>SELECT a,CHAR_LENGTH(a),HEX(a) FROM t\G;　　*************************** 1. row ***************************　　a: a　　CHAR_LENGTH(a): 1　　HEX(a): 61　　1 row in set (0.04 sec)

可以看到，在預設情況下，雖然a列是CHAR類型，但是返回的長度是1，這是因為MySQL資料庫已經對後面的空洞資料進行了截斷。若啟用PAD_CHAR_TO_FULL_LENGTH選項，則反映的是實際儲存的內容，例如：

mysql> SELECT a,CHAR_LENGTH(a),HEX(a) FROM t\G;　　*************************** 1. row ***************************　　a: a　　CHAR_LENGTH(a): 10　　HEX(a): 61202020202020202020　　1 row in set (0.00 sec)

　　

可以看到在CHAR列a中實際儲存的值為0x61202020202020202020。

　　PIPES_AS_CONCAT：將“||”視為字串的串連操作符而非或運算子，這和Oracle資料庫是一樣的，也和字串的拼接函數Concat相類似，例如：

mysql> SET sql_mode=‘pipes_as_concat‘;　　Query OK, 0 rows affected (0.01 sec)　　mysql> SELECT ‘a‘||‘b‘||‘c‘\G;　　*************************** 1. row ***************************　　‘a‘||‘b‘||‘c‘: abc　　1 row in set (0.00 sec)

　　

REAL_AS_FLOAT：將REAL視為FLOAT的同義字，而不是DOUBLE的同義字。

　　STRICT_ALL_TABLES：對所有引擎的表都啟用strict 模式。(STRICT_TRANS_TABLES只對支援事務的表啟用strict 模式)。

　　在strict 模式下，一旦任何操作的資料產生問題，都會終止當前的操作。對於啟用STRICT_ALL_TABLES選項的非事務引擎來說，這時資料可能停留在一個未知的狀態。這可能不是所有非事務引擎願意看到的一種情況，因此需要非常小心這個選項可能帶來的潛在影響。

　　下面的幾種SQL_MODE設定是之前討論的幾種選項的組合。

　　ANSI：等同於REAL_AS_FLOAT、PIPES_AS_CONCAT和ANSI_QUOTES、IGNORE_SPACE的組合。

　　ORACLE：等同於PIPES_AS_CONCAT、 ANSI_QUOTES、IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和NO_AUTO_CREATE_USER的組合。

　　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的組合。

　　MSSQL：等同於PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS和 NO_FIELD_OPTIONS的組合。

　　DB2：等同於PIPES_AS_CONCAT、ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS和NO_FIELD_OPTIONS的組合。

　　MYSQL323：等同於NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的組合。

　　MYSQL40：等同於NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的組合。

　　MAXDB：等同於PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和 NO_AUTO_CREATE_USER的組合。

MySQL資料類型：SQL_MODE設定不容忽視