溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
詳見MySQL5.6手冊的"Server SQL Modes"章節(jié)。
地址：http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
參考了好幾個網(wǎng)站的博文，出處不太好找了，對大家的分享表示感謝。
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
更改語法和行為，使其更符合標(biāo)準(zhǔn)SQL。
STRICT_TRANS_TABLES
等價于：STRICT_TRANS_TABLES
如果不能將給定的值插入到事務(wù)表中，則放棄該語句。對于非事務(wù)表，如果值出現(xiàn)在單行語句或多行語句的第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的行為象“傳統(tǒng)”SQL數(shù)據(jù)庫系統(tǒng)。該模式的簡單描述是當(dāng)在列中插入不正確的值時“給出錯誤而不是警告”。注釋：一旦發(fā)現(xiàn)錯誤立即放棄INSERT/UPDATE。如果你使用非事務(wù)存儲引擎，這種方式不是你想要的，因為出現(xiàn)錯誤前進行的數(shù)據(jù)更改不會“滾動”，結(jié)果是更新“只進行了一部分”。
如果把sql_mode的值設(shè)置成后面的兩個值（STRICT_TRANS_TABLES或者TRADITIONAL，也就是我們說的嚴格模式），那么當(dāng)在列中插入或更新不正確的值時，mysql將會給出錯誤，并且放棄insert/update操作。
在我們的一般應(yīng)用中建議使用STRICT_TRANS_TABLES或者TRADITIONAL這兩種模式，而不是使用默認的空或ANSI模式。
但是需要注意的問題是，如果數(shù)據(jù)庫運行在嚴格模式下，并且你的存儲引擎不支持事務(wù)(現(xiàn)在基本上都是用的InnoDB引擎了,不支持事務(wù)的較少見)，那么有數(shù)據(jù)不一致的風(fēng)險存在，比如一組sql中有兩個dml語句，如果后面的一個出現(xiàn)了問題，但是前面的已經(jīng)操作成功，那么mysql并不能回滾前面的操作。因此說設(shè)置sql_mode需要應(yīng)用人員權(quán)衡各種得失，從而得到一個合適的選擇。
下面的內(nèi)容翻譯自官方文檔，有些參數(shù)翻譯不上，搜了下網(wǎng)上也很少提到，翻譯時候直接忽略了。
全部sql_mode
MySQL版本<=5.6.5
默認為空
可選值如下：
| 參數(shù) | 含義及示例 |
| ALLOW_INVALID_DATES | 允許無效的日期插入到數(shù)據(jù)庫(無效的日期插入時候會有warning，但仍能插入，但是日期會變成0000-00-00這種無效的數(shù)據(jù)) >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過程中， 該模式未啟用的話，如果數(shù)據(jù)被零除，最終結(jié)果插入NULL并且不提示warnings 該模式啟用的話，如果數(shù)據(jù)被零除，最終結(jié)果插入NULL并且會提示warnings >set session sql_mode='STRICT_TRANS_TABLES'; >insert into t1 VALUES(5/0); Query OK, 1 row affected (0.00 sec) 插入時候沒有警告，結(jié)果是個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) 插入時候有警告，結(jié)果是個NULL |
| HIGH_NOT_PRECEDENCE | 設(shè)置優(yōu)先級關(guān)系【主要為了兼容老版本的MySQL】 > SET sql_mode = ''; l> SELECT NOT 1 BETWEEN -5 AND 5; 結(jié)果為0 > SET sql_mode = 'HIGH_NOT_PRECEDENCE'; > SELECT NOT 1 BETWEEN -5 AND 5; 結(jié)果為1 |
| IGNORE_SPACE | 函數(shù)名和括號“(”之間有空格。除了增加一些煩惱，這個選項好像沒有任何好處，要訪問保存為關(guān)鍵字的數(shù)據(jù)庫、表或列名，用戶必須引用該選項。 例如某個表中有user這一列，而MySQL數(shù)據(jù)庫中又有user這個函數(shù)， user會被解釋為函數(shù)，如果想要選擇user這一列，則需要引用。 >createtable `partition` (i int(3)); 對于表名或者其它與保留關(guān)鍵字的地方或者內(nèi)置函數(shù)同名稱的話，，必須加反引號，不然MySQL報錯。 |
| NO_AUTO_CREATE_USER | 禁止GRANT創(chuàng)建密碼為空的用戶。 > 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 | 該選項影響列為自增長的插入。 在默認設(shè)置下，插入0或NULL代表生成下一個自增長值。 如果用戶希望插入的值為0，而該列又是自增長的，那么這個選項就有用了。 |
| NO_BACKSLASH_ESCAPES | 反斜杠“\”作為普通字符而非轉(zhuǎn)義符 >set sql_mode=''; >select '\\'; +---+ | \ | +---+ | \ | +---+ >SET sql_mode = 'NO_BACKSLASH_ESCAPES'; >select '\\'; +----+ | \\ | +----+ | \\| +----+ |
| NO_DIR_IN_CREATE | 在創(chuàng)建表時忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項。 > set sql_mode=''; > create table t1 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 說明：此時查看/tmp下面會有相關(guān)的ibd表空間文件
> set sql_mode='NO_DIR_IN_CREATE'; > create table t2 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 說明：此時查看 /tmp下面找不到相關(guān)的ibd表空間文件，表文件還是原來的datadir下面。 |
NO_ENGINE_SUBSTITUTION 不使用默認引擎替代 | 設(shè)置這個參數(shù)后，如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。 不設(shè)置這個參數(shù)的話，找不到指定的存儲引擎的話，會用默認的存儲引擎替代，但會有warning提示。 >set sql_mode=''; > create table t4 (`age` int(3))ENGINE=Inff; > show create table t4\G *************************** 1. row*************************** Table: t4 Create Table: CREATE TABLE `t4` ( `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 上面我們隨便用了個不存在的存儲引擎建表時候也沒報錯，只要有個warning提示，系統(tǒng)自帶用默認的存儲引擎替代了。
> set sql_mode='NO_ENGINE_SUBSTITUTION'; > create table t4 (`age` int(3))ENGINE=Inff; ERROR 1286 (42000): Unknown storage engine'Inff' 顯式設(shè)置了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參數(shù)的話，才能插入成功。 > 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這種正常的數(shù)據(jù)是沒問題的。 > set sql_mode=''; >insert into tb2 VALUES('2010-00-02'); 允許這種格式的日期插入 |
| ONLY_FULL_GROUP_BY | 對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個SQL是不合法的，因為列不在GROUP BY從句中。 |
| PAD_CHAR_TO_FULL_LENGTH | |
| PIPES_AS_CONCAT | 將"||"視為字符串的連接操作符而非或運算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似。 |
| REAL_AS_FLOAT | 實數(shù)是浮點數(shù)的同義詞。【默認情況下，MySQL把實數(shù)當(dāng)double同義詞】 double 8字節(jié) float 4字節(jié) real8字節(jié) |
| STRICT_ALL_TABLES | 嚴格模式 |
| STRICT_TRANS_TABLES | 嚴格模式 |
STRICT_ALL_TABLES和STRICT_TRANS_TABLES都表示啟用嚴格模式。但二者還有些區(qū)別：
嚴格模式控制MySQL如何處理非法或丟失的輸入值。有幾種原因可以使一個值為非法。例如，數(shù)據(jù)類型錯誤，不適合列，或超出范圍。當(dāng)新插入的行不包含某列的沒有顯示定義DEFAULT子句的值，則該值被丟失。
對于事務(wù)表，當(dāng)啟用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式時，如果語句中有非法或丟失值，則會出現(xiàn)錯誤。語句被放棄并滾動。
對于非事務(wù)表，如果插入或更新的第1行出現(xiàn)壞值，兩種模式的行為相同。語句被放棄，表保持不變。如果語句插入或修改多行，并且壞值出現(xiàn)在第2或后面的行，結(jié)果取決于啟用了哪個嚴格選項：
對于STRICT_ALL_TABLES，MySQL返回錯誤并忽視剩余的行。但是，在這種情況下，前面的行已經(jīng)被插入或更新。這說明你可以部分更新，這可能不是你想要的。要避免這點，最好使用單行語句，因為這樣可以不更改表即可以放棄。
對于STRICT_TRANS_TABLES，MySQL將非法值轉(zhuǎn)換為最接近該列的合法值并插入調(diào)整后的值。如果值丟失，MySQL在列中插入隱式默認值。在任何情況下，MySQL都會生成警告而不是給出錯誤并繼續(xù)執(zhí)行語句。
一般情況下，不要使用MySQL來檢查列的內(nèi)容。最安全的方式（通常也較快）是，讓應(yīng)用程序負責(zé)，僅將有效值傳遞給數(shù)據(jù)庫。
在嚴格模式下，我們?nèi)匀豢梢允褂?/span>[INSERT|UPDATE] IGNORE INTO TB_NAMEVALUES(xxxx); 將錯誤當(dāng)中警告對待，但是實際上不建議這樣做。
MySQL版本>=5.6.6
默認為: NO_ENGINE_SUBSTITUTION
可選參數(shù)和上面的一樣。就不一一解釋了。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
