詳見MySQL5.6手冊(cè)的"Server SQL Modes"章節(jié)。
目前創(chuàng)新互聯(lián)建站已為成百上千的企業(yè)提供了網(wǎng)站建設(shè)、域名、虛擬空間、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計(jì)、欽州網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。
地址：http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
參考了好幾個(gè)網(wǎng)站的博文，出處不太好找了，對(duì)大家的分享表示感謝。
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
等價(jià)于：REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
更改語法和行為，使其更符合標(biāo)準(zhǔn)SQL。
STRICT_TRANS_TABLES
等價(jià)于：STRICT_TRANS_TABLES
如果不能將給定的值插入到事務(wù)表中，則放棄該語句。對(duì)于非事務(wù)表，如果值出現(xiàn)在單行語句或多行語句的第1行，則放棄該語句。
TRADITIONAL
等價(jià)于：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)。該模式的簡(jiǎn)單描述是當(dāng)在列中插入不正確的值時(shí)“給出錯(cuò)誤而不是警告”。注釋：一旦發(fā)現(xiàn)錯(cuò)誤立即放棄INSERT/UPDATE。如果你使用非事務(wù)存儲(chǔ)引擎，這種方式不是你想要的，因?yàn)槌霈F(xiàn)錯(cuò)誤前進(jìn)行的數(shù)據(jù)更改不會(huì)“滾動(dòng)”，結(jié)果是更新“只進(jìn)行了一部分”。
如果把sql_mode的值設(shè)置成后面的兩個(gè)值（STRICT_TRANS_TABLES或者TRADITIONAL，也就是我們說的嚴(yán)格模式），那么當(dāng)在列中插入或更新不正確的值時(shí)，mysql將會(huì)給出錯(cuò)誤，并且放棄insert/update操作。
在我們的一般應(yīng)用中建議使用STRICT_TRANS_TABLES或者TRADITIONAL這兩種模式，而不是使用默認(rèn)的空或ANSI模式。
但是需要注意的問題是，如果數(shù)據(jù)庫運(yùn)行在嚴(yán)格模式下，并且你的存儲(chǔ)引擎不支持事務(wù)(現(xiàn)在基本上都是用的InnoDB引擎了,不支持事務(wù)的較少見)，那么有數(shù)據(jù)不一致的風(fēng)險(xiǎn)存在，比如一組sql中有兩個(gè)dml語句，如果后面的一個(gè)出現(xiàn)了問題，但是前面的已經(jīng)操作成功，那么mysql并不能回滾前面的操作。因此說設(shè)置sql_mode需要應(yīng)用人員權(quán)衡各種得失，從而得到一個(gè)合適的選擇。
下面的內(nèi)容翻譯自官方文檔，有些參數(shù)翻譯不上，搜了下網(wǎng)上也很少提到，翻譯時(shí)候直接忽略了。
全部sql_mode
MySQL版本<=5.6.5
默認(rèn)為空
可選值如下：
| 參數(shù) | 含義及示例 |
| ALLOW_INVALID_DATES | 允許無效的日期插入到數(shù)據(jù)庫(無效的日期插入時(shí)候會(huì)有warning，但仍能插入，但是日期會(huì)變成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) 提示插入成功并有一個(gè)警告 該模式僅對(duì)DATE和DATETIME類型有效，而對(duì)TIMESTAMP無效，因?yàn)門IMESTAMP總是要求一個(gè)合法的輸入。 |
| ANSI_QUOTES | 啟用ANSI_QUOTES后，不能用雙引號(hào)來引用字符串，因?yàn)樗唤忉尀樽R(shí)別符。 >set session sql_mode='ANSI_QUOTES'; >insert into teachersvalues(22,'lee',12,"M"); ERROR1054 (42S22): Unknown column 'M' in 'field list' 用了雙引號(hào)的話，直接報(bào)錯(cuò)了 |
| ERROR_FOR_DIVISION_BY_ZERO | 在INSERT或UPDATE過程中， 該模式未啟用的話，如果數(shù)據(jù)被零除，最終結(jié)果插入NULL并且不提示warnings 該模式啟用的話，如果數(shù)據(jù)被零除，最終結(jié)果插入NULL并且會(huì)提示warnings >set session sql_mode='STRICT_TRANS_TABLES'; >insert into t1 VALUES(5/0); Query OK, 1 row affected (0.00 sec) 插入時(shí)候沒有警告，結(jié)果是個(gè)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) 插入時(shí)候有警告，結(jié)果是個(gè)NULL |
| HIGH_NOT_PRECEDENCE | 設(shè)置優(yōu)先級(jí)關(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ù)名和括號(hào)“(”之間有空格。除了增加一些煩惱，這個(gè)選項(xiàng)好像沒有任何好處，要訪問保存為關(guān)鍵字的數(shù)據(jù)庫、表或列名，用戶必須引用該選項(xiàng)。 例如某個(gè)表中有user這一列，而MySQL數(shù)據(jù)庫中又有user這個(gè)函數(shù)， user會(huì)被解釋為函數(shù)，如果想要選擇user這一列，則需要引用。 >createtable `partition` (i int(3)); 對(duì)于表名或者其它與保留關(guān)鍵字的地方或者內(nèi)置函數(shù)同名稱的話，，必須加反引號(hào)，不然MySQL報(bào)錯(cuò)。 |
| 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 | 該選項(xiàng)影響列為自增長(zhǎng)的插入。 在默認(rèn)設(shè)置下，插入0或NULL代表生成下一個(gè)自增長(zhǎng)值。 如果用戶希望插入的值為0，而該列又是自增長(zhǎng)的，那么這個(gè)選項(xiàng)就有用了。 |
| NO_BACKSLASH_ESCAPES | 反斜杠“\”作為普通字符而非轉(zhuǎn)義符 >set sql_mode=''; >select '\\'; +---+ | \ | +---+ | \| +---+ >SET sql_mode = 'NO_BACKSLASH_ESCAPES'; >select '\\'; +----+ | \\ | +----+ | \\| +----+ |
| NO_DIR_IN_CREATE | 在創(chuàng)建表時(shí)忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項(xiàng)。 > set sql_mode=''; > create table t1 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 說明：此時(shí)查看/tmp下面會(huì)有相關(guān)的ibd表空間文件
> set sql_mode='NO_DIR_IN_CREATE'; > create table t2 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 說明：此時(shí)查看 /tmp下面找不到相關(guān)的ibd表空間文件，表文件還是原來的datadir下面。 |
NO_ENGINE_SUBSTITUTION 不使用默認(rèn)引擎替代 | 設(shè)置這個(gè)參數(shù)后，如果需要的存儲(chǔ)引擎被禁用或未編譯，那么拋出錯(cuò)誤。 不設(shè)置這個(gè)參數(shù)的話，找不到指定的存儲(chǔ)引擎的話，會(huì)用默認(rèn)的存儲(chǔ)引擎替代，但會(huì)有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 上面我們隨便用了個(gè)不存在的存儲(chǔ)引擎建表時(shí)候也沒報(bào)錯(cuò)，只要有個(gè)warning提示，系統(tǒng)自帶用默認(rèn)的存儲(chǔ)引擎替代了。
> 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'后，建表時(shí)候指定一個(gè)不存在的存儲(chǔ)引擎后，就直接報(bào)錯(cuò)了。 |
| NO_FIELD_OPTIONS | |
| NO_KEY_OPTIONS | |
| NO_TABLE_OPTIONS | |
| NO_UNSIGNED_SUBTRACTION | |
| NO_ZERO_DATE | 啟用的話，表示允許0000-00-00這種日期的插入，但是會(huì)有warnings 不啟用的話，表示允許0000-00-00這種日期的插入，不會(huì)有warnings
如果啟動(dòng)NO_ZERO_DATE模時(shí),還啟用了strict模式，則0000-00-00這種不允許插入, 顯式使用IGNORE參數(shù)的話，才能插入成功。 > setsql_mode='NO_ZERO_DATE,.STRICT_TRANS_TABLES'; > insert into tb2 VALUES('0000-00-00'); 這樣插入會(huì)報(bào)錯(cuò)的 >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 這種非法日期，但是實(shí)際上插入的是0000-00-00。插入2013-02-01這種正常的數(shù)據(jù)是沒問題的。 > set sql_mode=''; >insert into tb2 VALUES('2010-00-02'); 允許這種格式的日期插入 |
| ONLY_FULL_GROUP_BY | 對(duì)于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個(gè)SQL是不合法的，因?yàn)榱胁辉贕ROUP BY從句中。 |
| PAD_CHAR_TO_FULL_LENGTH | |
| PIPES_AS_CONCAT | 將"||"視為字符串的連接操作符而非或運(yùn)算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似。 |
| REAL_AS_FLOAT | 實(shí)數(shù)是浮點(diǎn)數(shù)的同義詞。【默認(rèn)情況下，MySQL把實(shí)數(shù)當(dāng)double同義詞】 double 8字節(jié) float 4字節(jié) real8字節(jié) |
| STRICT_ALL_TABLES | 嚴(yán)格模式 |
| STRICT_TRANS_TABLES | 嚴(yán)格模式 |
STRICT_ALL_TABLES和STRICT_TRANS_TABLES都表示啟用嚴(yán)格模式。但二者還有些區(qū)別：
嚴(yán)格模式控制MySQL如何處理非法或丟失的輸入值。有幾種原因可以使一個(gè)值為非法。例如，數(shù)據(jù)類型錯(cuò)誤，不適合列，或超出范圍。當(dāng)新插入的行不包含某列的沒有顯示定義DEFAULT子句的值，則該值被丟失。
對(duì)于事務(wù)表，當(dāng)啟用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式時(shí)，如果語句中有非法或丟失值，則會(huì)出現(xiàn)錯(cuò)誤。語句被放棄并滾動(dòng)。
對(duì)于非事務(wù)表，如果插入或更新的第1行出現(xiàn)壞值，兩種模式的行為相同。語句被放棄，表保持不變。如果語句插入或修改多行，并且壞值出現(xiàn)在第2或后面的行，結(jié)果取決于啟用了哪個(gè)嚴(yán)格選項(xiàng)：
對(duì)于STRICT_ALL_TABLES，MySQL返回錯(cuò)誤并忽視剩余的行。但是，在這種情況下，前面的行已經(jīng)被插入或更新。這說明你可以部分更新，這可能不是你想要的。要避免這點(diǎn)，最好使用單行語句，因?yàn)檫@樣可以不更改表即可以放棄。
對(duì)于STRICT_TRANS_TABLES，MySQL將非法值轉(zhuǎn)換為最接近該列的合法值并插入調(diào)整后的值。如果值丟失，MySQL在列中插入隱式默認(rèn)值。在任何情況下，MySQL都會(huì)生成警告而不是給出錯(cuò)誤并繼續(xù)執(zhí)行語句。
一般情況下，不要使用MySQL來檢查列的內(nèi)容。最安全的方式（通常也較快）是，讓應(yīng)用程序負(fù)責(zé)，僅將有效值傳遞給數(shù)據(jù)庫。
在嚴(yán)格模式下，我們?nèi)匀豢梢允褂肹INSERT|UPDATE] IGNOREINTO TB_NAMEVALUES(xxxx);將錯(cuò)誤當(dāng)中警告對(duì)待，但是實(shí)際上不建議這樣做。
MySQL版本>=5.6.6
默認(rèn)為: NO_ENGINE_SUBSTITUTION
可選參數(shù)和上面的一樣。就不一一解釋了。
重慶分公司
重慶分公司
