前言

相信看過(guò)上一篇文章《MySQL案例：一個(gè)數(shù)據(jù)丟失慘案》的童鞋，都應(yīng)該意識(shí)到，sql_mode是一個(gè)非常關(guān)鍵的配置，接下來(lái)就帶來(lái)該配置項(xiàng)的詳細(xì)解析。

sql_mode詳解

sql_mode，會(huì)直接影響SQL語(yǔ)法支持和數(shù)據(jù)校驗(yàn)，它包含非常多的選項(xiàng)，其中5.7版本的默認(rèn)值是

“ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,；ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

，一般不建議進(jìn)行修改。

最重要的選項(xiàng)

sql_mode最重要的選項(xiàng)，包括以下3個(gè)：

（1）ANSI：該選項(xiàng)決定SQL語(yǔ)法支持，設(shè)置為ANSI，會(huì)更加遵守標(biāo)準(zhǔn)SQL語(yǔ)法。

（2）STRICT_TRANS_TABLES：該選項(xiàng)決定數(shù)據(jù)校驗(yàn)；對(duì)于事務(wù)性存儲(chǔ)引擎，當(dāng)出現(xiàn)非法值時(shí)，該事務(wù)會(huì)失敗并回滾；對(duì)于非事務(wù)性存儲(chǔ)引擎，如果非法值出現(xiàn)在第一行，那么該事務(wù)會(huì)失敗，如果非法值出現(xiàn)在中間，那么會(huì)調(diào)整非法值，并拋出告警。

（3）TRADITIONAL：該選項(xiàng)決定與傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)表現(xiàn)一致；對(duì)于一些非正常操作，直接報(bào)錯(cuò)失敗而不是告警提示。

全部選項(xiàng)

sql_mode還包括以下選項(xiàng)：

（4）ALLOW_INVALID_DATES：該選項(xiàng)決定不進(jìn)行嚴(yán)格的日期校驗(yàn)；它只校驗(yàn)月份范圍是否為1-12、日期范圍是否為1-31，不校驗(yàn)具體日期是否有效，比如2020-04-31這個(gè)非法日期是允許的。

（5）ANSI_QUOTES：該選項(xiàng)決定引用字符；它允許雙引號(hào)"作為引用字符，和反引號(hào)`一樣。

（6）ERROR_FOR_DIVISION_BY_ZERO：該選項(xiàng)決定被零整除的返回值；如果不啟用，那么被零整除的返回值為null且不告警；如果啟用但在非嚴(yán)格模式下，那么被零整除的返回值為null且產(chǎn)生告警；如果啟用且在嚴(yán)格模式下，那么被零整除會(huì)直接報(bào)錯(cuò)。

（7）HIGH_NOT_PRECEDENCE：該選項(xiàng)決定not操作的優(yōu)先級(jí)；啟用后，NOT a BETWEEN b AND c被解析為NOT (a BETWEEN b AND c)；在一些舊版本中，NOT a BETWEEN b AND c被解析為(NOT a) BETWEEN b AND c。

（8）IGNORE_SPACE：該選項(xiàng)決定忽略函數(shù)名和括號(hào)之間的空格；啟用后，比如count (*)也不會(huì)報(bào)錯(cuò)。

（9）NO_AUTO_CREATE_USER：該選項(xiàng)決定grant語(yǔ)句不會(huì)自動(dòng)創(chuàng)建用戶；已過(guò)時(shí)，grant語(yǔ)句也不會(huì)創(chuàng)建用戶。

（10）NO_AUTO_VALUE_ON_ZERO：該選項(xiàng)決定自增列的生成；一般來(lái)說(shuō)，向自增列插入0或null，系統(tǒng)會(huì)自動(dòng)生成下一個(gè)自增值插入；啟用后，向自增列插入0會(huì)保留原值0，插入null才會(huì)自動(dòng)生成下一個(gè)自增值插入

（11）NO_BACKSLASH_ESCAPES：該選項(xiàng)決定反斜杠\的作用；啟用后，反斜杠\不再作為轉(zhuǎn)義字符，而是用作普通字符。

（12）NO_DIR_IN_CREATE：該選項(xiàng)決定忽略創(chuàng)建表時(shí)，所有的INDEX DIRECTORY和DATA DIRECTORY指令；該選項(xiàng)只在從庫(kù)生效。

（13）NO_ENGINE_SUBSTITUTION：該選項(xiàng)決定創(chuàng)建表時(shí)，如果指定一個(gè)不存在/不支持的存儲(chǔ)引擎，那么會(huì)自動(dòng)轉(zhuǎn)換為默認(rèn)存儲(chǔ)引擎。

（14）NO_FIELD_OPTIONS：已過(guò)時(shí)。

（15）NO_KEY_OPTIONS：已過(guò)時(shí)。

（16）NO_TABLE_OPTIONS：已過(guò)時(shí)。

（17）NO_UNSIGNED_SUBTRACTION：一般情況下，整數(shù)之間的減法(其中一個(gè)為無(wú)符號(hào))，結(jié)果為無(wú)符號(hào)，如果結(jié)果為負(fù)數(shù)則報(bào)錯(cuò)；啟用該選項(xiàng)后，負(fù)數(shù)則可以正常處理。

（18）NO_ZERO_DATE：該選項(xiàng)決定‘0000-00-00'是否可以插入；如果不啟用，那么‘0000-00-00'可以插入且不告警；如果啟用但在非嚴(yán)格模式下，那么‘0000-00-00'可以插入但會(huì)產(chǎn)生告警；如果啟用且在非嚴(yán)格模式下，那么‘0000-00-00'不能插入會(huì)直接報(bào)錯(cuò)。

（19）NO_ZERO_IN_DATE：該選項(xiàng)決定月份和日期是否可以為00；如果不啟用，那么月份和日期可以為00且不告警；如果啟用但在非嚴(yán)格模式下，那么月份和日期可以為00但會(huì)產(chǎn)生告警；如果啟用且在非嚴(yán)格模式下，那么月份和日期不能為00會(huì)直接報(bào)錯(cuò)。

（20）ONLY_FULL_GROUP_BY：該選項(xiàng)決定select/having/order by后面的非聚合字段，必須出現(xiàn)在group by字句中。

（21）PAD_CHAR_TO_FULL_LENGTH：一般情況下，查詢char類型的字段，后面的空洞數(shù)據(jù)會(huì)被裁剪；啟用該選項(xiàng)后，查詢char類型的字段，后面的空洞數(shù)據(jù)不會(huì)被裁剪。

（22）PIPES_AS_CONCAT：該選項(xiàng)決定將||符號(hào)當(dāng)作字符串的連接操作符，而不是當(dāng)作OR同義詞。

（23）REAL_AS_FLOAT：該選項(xiàng)決定將REAL當(dāng)作FLOAT同義詞，而不是DOUBLE同義詞。

（24）STRICT_ALL_TABLES：該選項(xiàng)決定數(shù)據(jù)校驗(yàn)；對(duì)于事務(wù)性存儲(chǔ)引擎，當(dāng)出現(xiàn)非法值時(shí)，該事務(wù)會(huì)失敗并回滾；對(duì)于非事務(wù)性存儲(chǔ)引擎，如果非法值出現(xiàn)在第一行，那么該事務(wù)會(huì)失敗，如果非法值出現(xiàn)在中間，那么前面操作會(huì)成功、后面操作會(huì)直接報(bào)錯(cuò)，出現(xiàn)事務(wù)部分成功部分失敗的情況。

總結(jié)

通過(guò)上面的學(xué)習(xí)，相信大家對(duì)sql_mode各選項(xiàng)，都有較為詳細(xì)的了解；關(guān)于sql_mode如何設(shè)置，個(gè)人建議如下：

（1）對(duì)于5.5/5.6版本，sql_mode建議參照5.7默認(rèn)值進(jìn)行設(shè)置；

（2）對(duì)于5.7版本，sql_mode保持默認(rèn)值即可；

（3）對(duì)于8.0版本，sql_mode也保持默認(rèn)值即可。

sql_mode的設(shè)置，可以讓MySQL非常靈活地運(yùn)行在各種不同模式下，但與此同時(shí)也帶來(lái)各種各樣的風(fēng)險(xiǎn)；在MySQL廣泛應(yīng)用于各類重要系統(tǒng)的情況下，建議是要對(duì)sql_mode進(jìn)行嚴(yán)格審核設(shè)置，同時(shí)對(duì)開(kāi)發(fā)代碼進(jìn)行規(guī)范化管理；其實(shí)這一點(diǎn)，也可以從官方默認(rèn)值看出來(lái)，隨著MySQL版本的迭代，sql_mode的設(shè)置也是越來(lái)越嚴(yán)格。

以上就是MySQL sql_mode的使用詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL sql_mode的使用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論