MySQL或者MariaDB裡面sql_mode的設置詳解
- 2019 年 10 月 5 日
- 筆記
詳見MySQL5.6手冊的"Server SQL Modes"章節。
地址：http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
參考了好幾個網站的博文，出處不太好找了，對大家的分享表示感謝。
MySQL5.6的sql_mode是STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
MariaDB10.0.17的sql_mode是空的。
sql_mode 分為global、session作用範圍。
> SETGLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
> SETSESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
>SELECT @@GLOBAL.sql_mode;
>SELECT @@SESSION.sql_mode;
最重要的sql_mode
- ANSI
等價於：REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
更改語法和行為，使其更符合標準SQL。
- STRICT_TRANS_TABLES
等價於：STRICT_TRANS_TABLES
如果不能將給定的值插入到事務表中，則放棄該語句。對於非事務表，如果值出現在單行語句或多行語句的第1行，則放棄該語句。
- TRADITIONAL
等價於：STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Make MySQL的行為象「傳統」SQL資料庫系統。該模式的簡單描述是當在列中插入不正確的值時「給出錯誤而不是警告」。注釋：一旦發現錯誤立即放棄INSERT/UPDATE。如果你使用非事務存儲引擎，這種方式不是你想要的，因為出現錯誤前進行的數據更改不會「滾動」，結果是更新「只進行了一部分」。
如果把sql_mode的值設置成後面的兩個值（STRICT_TRANS_TABLES或者TRADITIONAL，也就是我們說的嚴格模式），那麼當在列中插入或更新不正確的值時，mysql將會給出錯誤，並且放棄insert/update操作。
在我們的一般應用中建議使用STRICT_TRANS_TABLES或者TRADITIONAL這兩種模式，而不是使用默認的空或ANSI模式。
但是需要注意的問題是，如果資料庫運行在嚴格模式下，並且你的存儲引擎不支援事務(現在基本上都是用的InnoDB引擎了,不支援事務的較少見)，那麼有數據不一致的風險存在，比如一組sql中有兩個dml語句，如果後面的一個出現了問題，但是前面的已經操作成功，那麼mysql並不能回滾前面的操作。因此說設置sql_mode需要應用人員權衡各種得失，從而得到一個合適的選擇。
下面的內容翻譯自官方文檔，有些參數翻譯不上，搜了下網上也很少提到，翻譯時候直接忽略了。
全部sql_mode
MySQL版本<=5.6.5
默認為空
可選值如下：
|
參數 |
含義及示例 |
|---|---|
|
ALLOW_INVALID_DATES |
允許無效的日期插入到資料庫(無效的日期插入時候會有warning，但仍能插入，但是日期會變成0000-00-00這種無效的數據)>set session sql_mode='ALLOW_INVALID_DATES';>insert into new(`d`) VALUES('201');Query OK, 1 row affected, 1 warning (0.01sec) 提示插入成功並有一個警告該模式僅對DATE和DATETIME類型有效，而對TIMESTAMP無效，因為TIMESTAMP總是要求一個合法的輸入。 |
|
ANSI_QUOTES |
啟用ANSI_QUOTES後，不能用雙引號來引用字元串，因為它被解釋為識別符。>set session sql_mode='ANSI_QUOTES';>insert into teachersvalues(22,'lee',12,"M");ERROR1054 (42S22): Unknown column 'M' in 'field list' 用了雙引號的話，直接報錯了 |
|
ERROR_FOR_DIVISION_BY_ZERO |
在INSERT或UPDATE過程中，該模式未啟用的話，如果數據被零除，最終結果插入NULL並且不提示warnings該模式啟用的話，如果數據被零除，最終結果插入NULL並且會提示warnings>set session sql_mode='STRICT_TRANS_TABLES';>insert into t1 VALUES(5/0);Query OK, 1 row affected (0.00 sec) 插入時候沒有警告，結果是個NULL >set session sql_mode='ERROR_FOR_DIVISION_BY_ZERO';>insert into t1 VALUES(6/0);QueryOK, 1 row affected, 1 warning (0.01 sec) 插入時候有警告，結果是個NULL |
|
HIGH_NOT_PRECEDENCE |
設置優先順序關係【主要為了兼容老版本的MySQL】> SET sql_mode = '';l> SELECT NOT 1 BETWEEN -5 AND 5;結果為0> SET sql_mode = 'HIGH_NOT_PRECEDENCE';> SELECT NOT 1 BETWEEN -5 AND 5;結果為1 |
|
IGNORE_SPACE |
函數名和括弧「(」之間有空格。除了增加一些煩惱，這個選項好像沒有任何好處，要訪問保存為關鍵字的資料庫、表或列名，用戶必須引用該選項。例如某個表中有user這一列，而MySQL資料庫中又有user這個函數， user會被解釋為函數，如果想要選擇user這一列，則需要引用。>createtable `partition` (i int(3)); 對於表名或者其它與保留關鍵字的地方或者內置函數同名稱的話，，必須加反引號，不然MySQL報錯。 |
|
NO_AUTO_CREATE_USER |
禁止GRANT創建密碼為空的用戶。> SET sql_mode = 'NO_AUTO_CREATE_USER'; > grant all on *.* to 'lirl'@'%';ERROR 1133 (42000): Can't find any matchingrow in the user table>grant all on *.* to 'lirl2'@'%'identified by '123456';QueryOK, 0 rows affected (0.00 sec) |
|
NO_AUTO_VALUE_ON_ZERO |
該選項影響列為自增長的插入。在默認設置下，插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。 |
|
NO_BACKSLASH_ESCAPES |
反斜杠「」作為普通字元而非轉義符>set sql_mode='';>select '\';+—+| |+—+| |+—+>SET sql_mode = 'NO_BACKSLASH_ESCAPES';>select '\';+—-+| \ |+—-+| \|+—-+ |
|
NO_DIR_IN_CREATE |
在創建表時忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項。> set sql_mode='';> create table t1 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp";說明：此時查看/tmp下面會有相關的ibd表空間文件 > set sql_mode='NO_DIR_IN_CREATE';> create table t2 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp";說明：此時查看 /tmp下面找不到相關的ibd表空間文件，表文件還是原來的datadir下面。 |
|
NO_ENGINE_SUBSTITUTION不使用默認引擎替代 |
設置這個參數後，如果需要的存儲引擎被禁用或未編譯，那麼拋出錯誤。不設置這個參數的話，找不到指定的存儲引擎的話，會用默認的存儲引擎替代，但會有warning提示。>set sql_mode='';> create table t4 (`age` int(3))ENGINE=Inff; > show create table t4G*************************** 1. row*************************** Table: t4Create Table: CREATE TABLE `t4` ( `age` int(3) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8上面我們隨便用了個不存在的存儲引擎建表時候也沒報錯，只要有個warning提示，系統自帶用默認的存儲引擎替代了。 > set sql_mode='NO_ENGINE_SUBSTITUTION';> create table t4 (`age` int(3))ENGINE=Inff;ERROR 1286 (42000): Unknown storage engine'Inff'顯式設置了sql_mode='NO_ENGINE_SUBSTITUTION'後，建表時候指定一個不存在的存儲引擎後，就直接報錯了。 |
|---|---|
|
NO_FIELD_OPTIONS |
|
|
NO_KEY_OPTIONS |
|
|
NO_TABLE_OPTIONS |
|
|
NO_UNSIGNED_SUBTRACTION |
|
|
NO_ZERO_DATE |
啟用的話，表示允許0000-00-00這種日期的插入，但是會有warnings不啟用的話，表示允許0000-00-00這種日期的插入，不會有warnings 如果啟動NO_ZERO_DATE模時,還啟用了strict模式，則0000-00-00這種不允許插入, 顯式使用IGNORE參數的話，才能插入成功。> setsql_mode='NO_ZERO_DATE,.STRICT_TRANS_TABLES';> insert into tb2 VALUES('0000-00-00'); 這樣插入會報錯的>INSERT IGNORE INTO tb1 VALUES('0000-00-00'); # 加了IGNORE後才能插入成功 |
|
NO_ZERO_IN_DATE |
> set sql_mode='NO_ZERO_IN_DATE';允許插2015-00-02、2015-03-00 這種非法日期，但是實際上插入的是0000-00-00。插入2013-02-01這種正常的數據是沒問題的。> set sql_mode='';>insert into tb2 VALUES('2010-00-02'); 允許這種格式的日期插入 |
|
ONLY_FULL_GROUP_BY |
對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中。 |
|
PAD_CHAR_TO_FULL_LENGTH |
|
|
PIPES_AS_CONCAT |
將"||"視為字元串的連接操作符而非或運算符，這和Oracle資料庫是一樣的，也和字元串的拼接函數Concat相類似。 |
|
REAL_AS_FLOAT |
實數是浮點數的同義詞。【默認情況下，MySQL把實數當double同義詞】double 8位元組float 4位元組real8位元組 |
|---|---|
|
STRICT_ALL_TABLES |
嚴格模式 |
|
STRICT_TRANS_TABLES |
嚴格模式 |
STRICT_ALL_TABLES和STRICT_TRANS_TABLES都表示啟用嚴格模式。但二者還有些區別：
嚴格模式控制MySQL如何處理非法或丟失的輸入值。有幾種原因可以使一個值為非法。例如，數據類型錯誤，不適合列，或超出範圍。當新插入的行不包含某列的沒有顯示定義DEFAULT子句的值，則該值被丟失。
對於事務表，當啟用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式時，如果語句中有非法或丟失值，則會出現錯誤。語句被放棄並滾動。
對於非事務表，如果插入或更新的第1行出現壞值，兩種模式的行為相同。語句被放棄，表保持不變。如果語句插入或修改多行，並且壞值出現在第2或後面的行，結果取決於啟用了哪個嚴格選項：
- 對於STRICT_ALL_TABLES，MySQL返回錯誤並忽視剩餘的行。但是，在這種情況下，前面的行已經被插入或更新。這說明你可以部分更新，這可能不是你想要的。要避免這點，最好使用單行語句，因為這樣可以不更改表即可以放棄。
- 對於STRICT_TRANS_TABLES，MySQL將非法值轉換為最接近該列的合法值並插入調整後的值。如果值丟失，MySQL在列中插入隱式默認值。在任何情況下，MySQL都會生成警告而不是給出錯誤並繼續執行語句。
一般情況下，不要使用MySQL來檢查列的內容。最安全的方式（通常也較快）是，讓應用程式負責，僅將有效值傳遞給資料庫。
在嚴格模式下，我們仍然可以使用[INSERT|UPDATE] IGNORE INTO TB_NAMEVALUES(xxxx); 將錯誤當中警告對待，但是實際上不建議這樣做。
MySQL版本>=5.6.6
默認為: NO_ENGINE_SUBSTITUTION
可選參數和上面的一樣。就不一一解釋了。
