MySQL sql_mode的使用詳解
前言
相信看過上一篇文章《MySQL案例:一個(gè)數(shù)據(jù)丟失慘案》的童鞋,都應(yīng)該意識(shí)到,sql_mode是一個(gè)非常關(guān)鍵的配置,接下來就帶來該配置項(xiàng)的詳細(xì)解析。
sql_mode詳解
sql_mode,會(huì)直接影響SQL語法支持和數(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語法支持,設(shè)置為ANSI,會(huì)更加遵守標(biāo)準(zhǔn)SQL語法。
(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ù)庫表現(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語句不會(huì)自動(dòng)創(chuàng)建用戶;已過時(shí),grant語句也不會(huì)創(chuàng)建用戶。
(10)NO_AUTO_VALUE_ON_ZERO:該選項(xiàng)決定自增列的生成;一般來說,向自增列插入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)只在從庫生效。
(13)NO_ENGINE_SUBSTITUTION:該選項(xiàng)決定創(chuàng)建表時(shí),如果指定一個(gè)不存在/不支持的存儲(chǔ)引擎,那么會(huì)自動(dòng)轉(zhuǎn)換為默認(rèn)存儲(chǔ)引擎。
(14)NO_FIELD_OPTIONS:已過時(shí)。
(15)NO_KEY_OPTIONS:已過時(shí)。
(16)NO_TABLE_OPTIONS:已過時(shí)。
(17)NO_UNSIGNED_SUBTRACTION:一般情況下,整數(shù)之間的減法(其中一個(gè)為無符號(hào)),結(jié)果為無符號(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é)
通過上面的學(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í)也帶來各種各樣的風(fēng)險(xiǎn);在MySQL廣泛應(yīng)用于各類重要系統(tǒng)的情況下,建議是要對(duì)sql_mode進(jìn)行嚴(yán)格審核設(shè)置,同時(shí)對(duì)開發(fā)代碼進(jìn)行規(guī)范化管理;其實(shí)這一點(diǎn),也可以從官方默認(rèn)值看出來,隨著MySQL版本的迭代,sql_mode的設(shè)置也是越來越嚴(yán)格。
以上就是MySQL sql_mode的使用詳解的詳細(xì)內(nèi)容,更多關(guān)于MySQL sql_mode的使用的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
Centos7下使用yum安裝mysql數(shù)據(jù)庫的詳細(xì)教程(增強(qiáng)版)
這篇文章主要介紹了Centos7下使用yum安裝mysql數(shù)據(jù)庫的詳細(xì)教程(增強(qiáng)版),非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2016-12-12MySQL中Binary Log二進(jìn)制日志文件的基本操作命令小結(jié)
這篇文章主要介紹了MySQL中Binary Log二進(jìn)制日志文件的基本操作小結(jié),包括利用二進(jìn)制日志恢復(fù)數(shù)據(jù)的方法,需要的朋友可以參考下2015-12-12一臺(tái)服務(wù)器部署兩個(gè)獨(dú)立的mysql數(shù)據(jù)庫操作實(shí)例
這篇文章主要給大家介紹了關(guān)于一臺(tái)服務(wù)器部署兩個(gè)獨(dú)立的mysql數(shù)據(jù)庫的相關(guān)資料,同一臺(tái)服務(wù)器裝兩個(gè)數(shù)據(jù)庫,可以通過虛擬化技術(shù)實(shí)現(xiàn),文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03MySQL超詳細(xì)實(shí)現(xiàn)用戶管理實(shí)例
MySQL 是一個(gè)多用戶數(shù)據(jù)庫,具有功能強(qiáng)大的訪問控制系統(tǒng),可以為不同用戶指定不同權(quán)限。在前面的章節(jié)中我們使用的是 root 用戶,該用戶是超級(jí)管理員,擁有所有權(quán)限,包括創(chuàng)建用戶、刪除用戶和修改用戶密碼等管理權(quán)限2022-06-06MySQL安裝常見報(bào)錯(cuò)處理方法總結(jié)大全
MySQL數(shù)據(jù)庫在安裝或卸載的過程中,常常會(huì)出現(xiàn)一些錯(cuò)誤,這是件讓我們頭疼的事,下面這篇文章主要給大家介紹了關(guān)于MySQL安裝常見報(bào)錯(cuò)處理方法的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-07-07mysql如何查詢當(dāng)前數(shù)據(jù)庫中不為空的表
這篇文章主要介紹了mysql如何查詢當(dāng)前數(shù)據(jù)庫中不為空的表問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10