前言

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

介紹

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

sql model 常用來解決下面幾類問題

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

下面對18種運行模式進行簡單介紹

1. STRICT_ALL_TABLES

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

2. STRICT_TRANS_TABLES 嚴格模式

這個也是嚴格模式，不同的是上面的會對所有存儲引擎開啟嚴格模式，而這個會對所有支持事務(wù)引擎開啟，并盡可能對非事務(wù)引擎開啟。
默認為ON，建議ON；

3. ALLOW_INVALID_DATES

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

4. ANSI_QUOTES

是否將雙引號也當做對象名稱的標識，就像` 一樣。
當為OFF時，" 用來包裹字符串，不能表示表名和列名等；
當為ON時，"不能包裹字符串，但它可以包裹表名和列名等；

默認為OFF，建議OFF；在mysql中我們可以僅可以用`包裹對象標識名（當然也可以不包裹），單引號包裹字符串。

5. HIGH_NOT_PRECEDENCE

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

6. IGNORE_SPACE

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

7. NO_AUTO_VALUE_ON_ZERO

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

8. NO_BACKSLASH_ESCAPES

控制是否將反斜杠識別為轉(zhuǎn)義字符。
當為OFF時，反斜杠是轉(zhuǎn)義字符；
當為ON時，反斜杠就是普通字符，不會對其后的字符轉(zhuǎn)義，此時mysql中也就沒有了轉(zhuǎn)移字符；
默認為OFF，建議OFF；

9. NO_DIR_IN_CREATE

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

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

當為OFF時，DATA DIRECTORY 和 INDEX DIRECTORY 繼續(xù)有效；
當為ON時，DATA DIRECTORY 和 INDEX DIRECTORY 會被忽略；

默認為OFF，建議OFF；

10. NO_ENGINE_SUBSTITUTION

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

當為OFF時，mysql會使用可用的默認殷勤替代；
當為ON時，mysql會直接報錯；

默認ON，建議ON；

11. NO_UNSIGNED_SUBTRACTION

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

12. NO_ZERO_DATE

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

13. NO_ZERO_IN_DATE

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

默認為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 語句的寫法。

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

15. PAD_CHAR_TO_FULL_LENGTH

控制讀取char(M)類型的列值時，當實際值的長度不到M時，是否在默認添加空格以補齊M的長度。
例如，我們向char(10)中插入字符串’abc ‘（abc+3個空格），那么我們讀取時：
當為OFF時，讀取到的是’abc’，后面沒有空格，因為mysql會自動去掉末尾的空格。
當為ON時，讀取到的是’abc '（abc+7個空格），mysql會自動補齊到長度10;
默認OFF，建議OFF。另外，這是一個即將被棄用的模式。

16. PIPES_AS_CONCAT

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

17. REAL_AS_FLOAT

控制是否將 real 當做float的同義詞，
當為OFF時，real是double的同義詞；
當為ON時，real是float的同義詞；
默認OFF，建議OFF；

18. TIME_TRUNCATE_FRACTIONAL

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

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

最新評論