前言

今天同事使用數(shù)據(jù)庫(kù)時(shí)報(bào)錯(cuò),排查問(wèn)題時(shí)發(fā)現(xiàn)配置文件里的sql_mode配置被人注釋了,所以通過(guò)查詢(xún)資料對(duì)這個(gè)配置進(jìn)行了下了解。

介紹

mysql為了支持在不同的環(huán)境下運(yùn)行，允許我們給它設(shè)置不同的運(yùn)行模式（sql_mode）。
不同的運(yùn)行模式，mysql處理我們的sql語(yǔ)句也不相同（mysql提供了18個(gè)運(yùn)行模式，它們可任意組合使用）。

sql model 常用來(lái)解決下面幾類(lèi)問(wèn)題

• 通過(guò)設(shè)置sql mode, 可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn)，有效地保障數(shù)據(jù)準(zhǔn)備性。
• 通過(guò)設(shè)置sql model 為寬松模式，來(lái)保證大多數(shù)sql符合標(biāo)準(zhǔn)的sql語(yǔ)法，這樣應(yīng)用在不同數(shù)據(jù)庫(kù)之間進(jìn)行遷移時(shí)，則不需要對(duì)業(yè)務(wù)sql
  進(jìn)行較大的修改。
• 在不同數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移之前，通過(guò)設(shè)置SQL Mode 可以使MySQL 上的數(shù)據(jù)更方便地遷移到目標(biāo)數(shù)據(jù)庫(kù)中。

下面對(duì)18種運(yùn)行模式進(jìn)行簡(jiǎn)單介紹

1. STRICT_ALL_TABLES

控制是否所有的mysql存儲(chǔ)引擎均開(kāi)啟嚴(yán)格模式，默認(rèn)為OFF，建議ON；
嚴(yán)格模式開(kāi)啟后，mysql會(huì)對(duì)更改數(shù)據(jù)的行為做嚴(yán)格的校驗(yàn)，比如：日期的有效性等，一般和其他模式配合使用；

2. STRICT_TRANS_TABLES 嚴(yán)格模式

這個(gè)也是嚴(yán)格模式，不同的是上面的會(huì)對(duì)所有存儲(chǔ)引擎開(kāi)啟嚴(yán)格模式，而這個(gè)會(huì)對(duì)所有支持事務(wù)引擎開(kāi)啟，并盡可能對(duì)非事務(wù)引擎開(kāi)啟。
默認(rèn)為ON，建議ON；

3. ALLOW_INVALID_DATES

控制我們能否向date、datetime列插入2020-04-31這種數(shù)據(jù)（4月怎么會(huì)有31號(hào)呢？這是個(gè)非法數(shù)據(jù)）。
當(dāng)OFF時(shí)，不能插入；
當(dāng)ON時(shí)，能插入；
默認(rèn)為OFF，建議OFF；

4. ANSI_QUOTES

是否將雙引號(hào)也當(dāng)做對(duì)象名稱(chēng)的標(biāo)識(shí)，就像` 一樣。
當(dāng)為OFF時(shí)，" 用來(lái)包裹字符串，不能表示表名和列名等；
當(dāng)為ON時(shí)，"不能包裹字符串，但它可以包裹表名和列名等；

默認(rèn)為OFF，建議OFF；在mysql中我們可以?xún)H可以用`包裹對(duì)象標(biāo)識(shí)名（當(dāng)然也可以不包裹），單引號(hào)包裹字符串。

5. HIGH_NOT_PRECEDENCE

控制 NOT運(yùn)算符是否具有更高的優(yōu)先級(jí)。
當(dāng)OFF時(shí)，select 1 between -5 and 5等同select not (1 between -5 and 5)；
當(dāng)ON時(shí)，select 1 between -5 and 5等同select (not 1) between -5 and 5；
默認(rèn)為OFF，建議OFF；

6. IGNORE_SPACE

控制是否忽略函數(shù)名和(之間的空格。
當(dāng)OFF時(shí)，函數(shù)名和(之間不能有空格；
當(dāng)ON時(shí)，函數(shù)名和(之間可以有空格；
默認(rèn)OFF，建議OFF；

7. NO_AUTO_VALUE_ON_ZERO

控制當(dāng)把0插入到自增列時(shí)是否觸發(fā)自增。
當(dāng)為OFF時(shí)，將0或null插入到自增列都會(huì)觸發(fā)自增；
當(dāng)為ON時(shí)，將0插入到自增列不會(huì)觸發(fā)自增，因?yàn)?會(huì)被當(dāng)做正常數(shù)據(jù)對(duì)待，直接插入進(jìn)去（插入null依然會(huì)觸發(fā)自增）；
默認(rèn)為OFF，建議OFF；

8. NO_BACKSLASH_ESCAPES

控制是否將反斜杠識(shí)別為轉(zhuǎn)義字符。
當(dāng)為OFF時(shí)，反斜杠是轉(zhuǎn)義字符；
當(dāng)為ON時(shí)，反斜杠就是普通字符，不會(huì)對(duì)其后的字符轉(zhuǎn)義，此時(shí)mysql中也就沒(méi)有了轉(zhuǎn)移字符；
默認(rèn)為OFF，建議OFF；

9. NO_DIR_IN_CREATE

控制是否忽略create table時(shí)后面的 DATA DIRECTORY 和 INDEX DIRECTORY 聲明。

DATA DIRECTORY 和 INDEX DIRECTORY 聲明是當(dāng)mysql的datedir存儲(chǔ)空間不夠時(shí)允許用戶(hù)為新建的表指定新的存儲(chǔ)地址，如：
CREATE TABLE t_1 engine=innodb DATA DIRECTORY=“/data/lottery/” INDEX DIRECTORY=“/data/lottery/”

當(dāng)為OFF時(shí)，DATA DIRECTORY 和 INDEX DIRECTORY 繼續(xù)有效；
當(dāng)為ON時(shí)，DATA DIRECTORY 和 INDEX DIRECTORY 會(huì)被忽略；

默認(rèn)為OFF，建議OFF；

10. NO_ENGINE_SUBSTITUTION

控制是否自動(dòng)將用戶(hù)建表時(shí)聲明的不可用的存儲(chǔ)引擎替換成可用的存儲(chǔ)引擎。
比如：當(dāng)我們建表時(shí)聲明使用某一個(gè)存儲(chǔ)引擎，但是由于服務(wù)器問(wèn)題，這個(gè)引擎暫時(shí)不可用，那么此時(shí)mysql應(yīng)該怎么做呢？

當(dāng)為OFF時(shí)，mysql會(huì)使用可用的默認(rèn)殷勤替代；
當(dāng)為ON時(shí)，mysql會(huì)直接報(bào)錯(cuò)；

默認(rèn)ON，建議ON；

11. NO_UNSIGNED_SUBTRACTION

控制當(dāng)我們對(duì)unsigned int 等做減法時(shí)，是否將結(jié)果轉(zhuǎn)為對(duì)應(yīng)的非unsigned修飾的類(lèi)型。
當(dāng)為OFF時(shí)，unsigned int與int相減結(jié)果仍為unsigned int；
當(dāng)為ON時(shí)，unsigned int與int相減結(jié)果為int；
默認(rèn)OFF，建議OFF；

12. NO_ZERO_DATE

控制是否判別 ‘0000-01-01’(年份為0)為合法的日期，配合StrictMode作用。
當(dāng)為OFF時(shí)， '0000-01-01’是合法日期；
當(dāng)為ON時(shí)， '0000-01-01’不是合法日期；
默認(rèn)為OFF，建議ON；

13. NO_ZERO_IN_DATE

和NO_ZERO_DATE相似，不同的是前者控制的是年份，后者控制的月份和天（如：2022-13-32）。

默認(rèn)為OFF，建議ON；

建議設(shè)置為：set sql_mode=“STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE”

14. ONLY_FULL_GROUP_BY

控制mysql中 select … group by 語(yǔ)句的寫(xiě)法。

原因是這樣的，在sql92標(biāo)準(zhǔn)的時(shí)候，規(guī)定如果使用了group by子句，那么select子句的列一定被包含在group by 子句中，或者使用sum等聚合函數(shù)包裹。
然而，到了sql99的時(shí)候，規(guī)定select 子句的列也可以不被包含在group by 子句中，如：select t_id,t_name,t_age from test group by t_id是可以的，因?yàn)?t_id 是主鍵。

15. PAD_CHAR_TO_FULL_LENGTH

控制讀取char(M)類(lèi)型的列值時(shí)，當(dāng)實(shí)際值的長(zhǎng)度不到M時(shí)，是否在默認(rèn)添加空格以補(bǔ)齊M的長(zhǎng)度。
例如，我們向char(10)中插入字符串’abc ‘（abc+3個(gè)空格），那么我們讀取時(shí)：
當(dāng)為OFF時(shí)，讀取到的是’abc’，后面沒(méi)有空格，因?yàn)閙ysql會(huì)自動(dòng)去掉末尾的空格。
當(dāng)為ON時(shí)，讀取到的是’abc '（abc+7個(gè)空格），mysql會(huì)自動(dòng)補(bǔ)齊到長(zhǎng)度10;
默認(rèn)OFF，建議OFF。另外，這是一個(gè)即將被棄用的模式。

16. PIPES_AS_CONCAT

控制是否將 ‘||’ 作為 string之間的鏈接符號(hào)，就像 concat(string1,string2) 函數(shù)一樣。
當(dāng)為OFF時(shí)，|| 是 or 的同義詞，不過(guò)mysql建議使用 or 而不是 || ；
當(dāng)為ON時(shí)，|| 和concat函數(shù)類(lèi)似；
默認(rèn)OFF，建議OFF；

17. REAL_AS_FLOAT

控制是否將 real 當(dāng)做float的同義詞，
當(dāng)為OFF時(shí)，real是double的同義詞；
當(dāng)為ON時(shí)，real是float的同義詞；
默認(rèn)OFF，建議OFF；

18. TIME_TRUNCATE_FRACTIONAL

控制當(dāng)時(shí)間的毫秒精度超出時(shí)，是進(jìn)行四設(shè)五入，還是直接舍棄多余的位數(shù)。
舉個(gè)例子，當(dāng)將’14:52:12.15’插入到time(1)的時(shí)候，是插入’14:52:12.2’還是插入’14:52:12.1’
當(dāng)為OFF時(shí)，進(jìn)行四設(shè)五入，插入14:52:12.2；
當(dāng)為ON時(shí)，舍棄多余的位數(shù)，插入14:52:12.1；
默認(rèn)為OFF，建議OFF。

到此這篇關(guān)于MySQL中sql_mode模式的使用的文章就介紹到這了,更多相關(guān)MySQL sql_mode模式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論