mysql SQL_MODE 的配置詳解

MySQL服務器可以以不同的SQL模式來操作，並且可以為不同客戶端應用不同模式。這樣每個應用程序可以根據自己的需求來定制服務器的操作模式,而sql-mode就是我們要介紹的這個模式了。

最近接手一個正在運行中的項目，拉到本地運行的時候出了問題，在插入數據庫的時候報錯 Incorrect integer value: ” for column ‘id’ at row 1。

字面意思就是自增主鍵ID被寫成了空，導致插入失敗。原服務器上跑的是正常的，應該是環境的問題了。

打印了下sql語句：

INSERT INTO `config` (`id`,`key`,`value`) VALUES (”,’aaa’,'bbb’);

有些別扭，正常套路，自增主鍵應該不用寫的，不知道當時的程序是怎麼想的。

直接執行sql語句也是報錯，可見這條語句就是有錯的，我本地mysql版本是5.7的，編譯好之後很少改配置，除了有一次跑其他項目的時候，改過一次sql_mode，可能是這方面的問題，由於當時並沒有深究，遂細查了下。

先說下我怎麼解決的，再復制網上查的資料。

查看了下我本地的 my.cnf，看到我之前把默認為空的SQL_MODE寫成了：sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES，將SQL_MODE改了成：sql_mode=’NO_ENGINE_SUBSTITUTION’，就可以了。

下面是網上復制的 SQL_MODE 可設置項：

STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務表(例如表的存儲引擎為InnoDB)中，則中斷當前的操作不影響非事務表(例如表的存儲引擎為MyISAM)。

ALLOW_INVALID_DATES：該選項並不完全對日期的合法性進行檢查，只檢查月份是否在1～12之間，日期是否在1～31之間。該模式僅對DATE和DATETIME類型有效，而對TIMESTAMP無效，因為TIMESTAMP總是要求一個合法的輸入。

ANSI_QUOTES：啟用ANSI_QUOTES後，不能用雙引號來引用字符串，因為它將被解釋為識別符。

ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數據被零除(或MOD(X，0))，則產生錯誤(否則為警告)。如果未給出該模式，那麼數據被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作結果為NULL。

本文來自Ruesin博客
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模式，可以獲得以前舊版本的更高優先級的結果。

IGNORE_SPACE：函數名和括號“(”之間有空格。除了增加一些煩惱，這個選項好像沒有任何好處，要訪問保存為關鍵字的數據庫、表或列名，用戶必須引用該選項。例如某個表中有user這一列，而MySQL數據庫中又有user這個函數， user會被解釋為函數，如果想要選擇user這一列，則需要引用。

NO_AUTO_CREATE_USER：禁止GRANT創建密碼為空的用戶。

NO_AUTO_VALUE_ON_ZERO：該選項影響列為自增長的插入。在默認設置下，插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。

NO_BACKSLASH_ESCAPES：反斜槓“\”作為普通字符而非轉義符。

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

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

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

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

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

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

PAD_CHAR_TO_FULL_LENGTH：對於CHAR類型字段，不要截斷空洞數據。空洞數據就是自動填充值為0×20的數據。先來看MySQL數據庫在默認情況下的表現。

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

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

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

在嚴格模式下，一旦任何操作的數據產生問題，都會終止當前的操作。對於啟用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的組合。

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

SQL_MODE的設置其實是比較冒險的一種設置，因為在這種設置下可以允許一些非法操作，比如可以將NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生產環境中強烈建議開發人員將這個值設為嚴格模式，這樣有些問題可以在數據庫的設計和開發階段就能發現，而如果在生產環境下運行數據庫後發現這類問題，那麼修改的代價將變得十分巨大。此外，正確地設置SQL_MODE還可以做一些約束(Constraint)檢查的工作。

請把最起碼的版權請留給博主，謝謝！