設(shè)置和查詢MYSQL SQL模式方法

下面一起來(lái)了解下設(shè)置和查詢MYSQL SQL模式方法，相信大家看完肯定會(huì)受益匪淺，文字在精不在多，希望設(shè)置和查詢MYSQL SQL模式方法這篇短內(nèi)容是你想要的。

設(shè)置和查詢SQL模式

通過(guò)修改sql_mode變量的值來(lái)改變SQL模式。
SQL模式可以在全局級(jí)別下設(shè)置，也可以在會(huì)話級(jí)別下設(shè)置。在數(shù)據(jù)庫(kù)啟動(dòng)時(shí)和數(shù)據(jù)庫(kù)運(yùn)行時(shí)都可以對(duì)sql_mode的值進(jìn)行修改。

在數(shù)據(jù)庫(kù)啟動(dòng)時(shí)設(shè)置SQL模式

在命令行中使用--sql_mode='modes'選項(xiàng)，或者在配置文件中使用sql_mode="modes"。
modes是一個(gè)以逗號(hào)分隔的模式的列表。
要清除SQL模式，將它設(shè)置為一個(gè)空的字符串，例如sql_mode=""

在數(shù)據(jù)庫(kù)運(yùn)行時(shí)設(shè)置SQL模式

使用SET語(yǔ)句來(lái)更改sql_mode的值，例如：

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

設(shè)置全局變量的值需要SUPER權(quán)限，設(shè)置后應(yīng)用到之后所有客戶端連接的操作。
設(shè)置session變量只應(yīng)用于當(dāng)前客戶端，每個(gè)客戶端都可以在任何時(shí)候更改它的sessionSQL模式。

查詢SQL模式

要確定當(dāng)前使用的SQL模式，使用以下語(yǔ)句進(jìn)行查詢

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

主要的SQL模式

主要的sql_mode的值為以下幾種：

• ANSI
  這是一個(gè)組合模式，它似的語(yǔ)法和行為更符合標(biāo)準(zhǔn)的SQL
• STRICT_TRANS_TABLES
  對(duì)事務(wù)表的嚴(yán)格模式。在這種模式下，如果一個(gè)值不能被插入到事務(wù)表中，則終止該語(yǔ)句。對(duì)于非事務(wù)表，如果不能插入的值發(fā)生在單行語(yǔ)句或者多行語(yǔ)句的第一行，也會(huì)終止該語(yǔ)句。
• TRADITIONAL
  傳統(tǒng)模式，這也是一個(gè)組合模式。在這種模式下，當(dāng)插入一個(gè)不正確的值時(shí)，會(huì)給出錯(cuò)誤而不是警告。（在非事務(wù)性存儲(chǔ)引擎中，可能這不是我們想要的，因?yàn)樵诎l(fā)生錯(cuò)誤時(shí)語(yǔ)句會(huì)中斷，但是在錯(cuò)誤發(fā)生前進(jìn)行的數(shù)據(jù)修改不能夠回滾，從而導(dǎo)致部分更新。）

SQL模式的完整列表

sql模式可以大致分為以下幾類(lèi)

嚴(yán)格模式（包括STRICT_ALL_TABLES和STRICT_TRANS_TABLES）

• STRICT_ALL_TABLES
  對(duì)于所有存儲(chǔ)引擎啟用嚴(yán)格模式，無(wú)效的值會(huì)被拒絕。
• STRICT_TRANS_TABLES
  對(duì)于事務(wù)存儲(chǔ)引擎啟用嚴(yán)格模式，并在可能的情況下對(duì)飛事務(wù)存儲(chǔ)引擎啟用嚴(yán)格模式。

在MySQL5.7.4到MySQL5.7.7中，嚴(yán)格模式包括ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE的效果。

用來(lái)限制0值，和嚴(yán)格模式一同使用的

• NO_ZERO_DATE
  影響數(shù)據(jù)庫(kù)是否允許'0000-00-00'作為一個(gè)有效的日期。其效果還取決于是否啟用了嚴(yán)格模式
  如果啟用了該模式，允許'0000-00-00'值并且插入不會(huì)產(chǎn)生警告
  如果禁用了該模式，允許'0000-00-00'值但是插入會(huì)產(chǎn)生警告
  如果該模式和嚴(yán)格模式同時(shí)啟用，除非同時(shí)給出IGNORE，否則不允許'0000-00-00'插入并產(chǎn)生錯(cuò)誤
• NO_ZERO_IN_DATE
  影響數(shù)據(jù)庫(kù)是否允許在年份非0時(shí)，月份或日期為0。其效果還取決于是否啟用了嚴(yán)格模式。
  如果啟用了該模式，允許包括0的日期值并且插入不會(huì)產(chǎn)生警告。
  如果禁用了該模式，允許包括0的日期值但是插入會(huì)產(chǎn)生警告。
  如果該模式和嚴(yán)格模式同時(shí)使用，除非同時(shí)給出IGNORE,否則不允許插入包含0的日期值并且插入會(huì)產(chǎn)生錯(cuò)誤。對(duì)于INSERT IGNORE和UPDATE IGNORE，包含0的日期值會(huì)作為'0000-00-00'插入并產(chǎn)生警告
• ERROR_FOR_DIVISION_BY_ZERO
  影響數(shù)據(jù)庫(kù)是否允許將0作為除數(shù)，包括MOD(N,0)。其效果還取決于是否啟用了嚴(yán)格模式
  如果啟用了該模式，允許以0作為除數(shù)并且插入不會(huì)產(chǎn)生警告
  如果禁用了該模式，允許以0作為除數(shù)但是插入會(huì)產(chǎn)生警告
  如果該模式和嚴(yán)格模式同時(shí)使用，除非同時(shí)給出IGNORE，否則不允許以0作為除數(shù)并且插入會(huì)產(chǎn)生錯(cuò)誤。對(duì)于INSERT IGNORE和UPDATE IGNORE，以0作為除數(shù)會(huì)插入NULL并產(chǎn)生警告。

在MySQL5.7.4以前版本中，以上三個(gè)模式被棄用
在MySQL5.7.4到MySQL5.7.7中，以上三個(gè)模式不產(chǎn)生作用，他們的效果包含在嚴(yán)格模式中。
在MySQL5.7.8及以后版本中，以上三個(gè)模式才有自己?jiǎn)为?dú)的作用，而不是嚴(yán)格模式的一部分。但是，他們應(yīng)該和嚴(yán)格模式一起使用，并且默認(rèn)情況下他們都是開(kāi)啟的。如果使用嚴(yán)格模式而不使用上述模式會(huì)產(chǎn)生警告，如果使用上述模式中的任何一個(gè)但是不啟用嚴(yán)格模式也會(huì)產(chǎn)生警告。
由于以上三個(gè)模式以棄用，在后續(xù)的MySQL版本中，他們作為一個(gè)單獨(dú)的模式名會(huì)被刪除，并且他們的效果將包含在嚴(yán)格模式中。

用來(lái)說(shuō)明符號(hào)的作用的

• ANSI_QUOTES
  將"作為標(biāo)識(shí)符（與`相同）而不是作為字符串的引用符號(hào)。在啟用此模式的情況下，仍然可以使用`作為引用標(biāo)識(shí)符，但是不能使用雙引號(hào)來(lái)引用文本字符串。
• PIPES_AS_CONCAT
  將||作為字符串連接操作符（與CONCAT()相同），而不是作為OR的同義詞
• REAL_AS_FLOAT
  將REAL作為FLOAT的同義詞。默認(rèn)情況下，MySQL將REAL視為DOUBLE的同義詞。
• NO_BACKSLASH_ESCAPES
  禁用反斜杠字符()作為字符串中的轉(zhuǎn)義字符。在啟用此模式后，反斜杠就變成了一個(gè)普通字符。

  影響語(yǔ)句的方式或結(jié)果的

• NO_UNSIGNED_SUBTRACTION
  對(duì)于整數(shù)之間的減法，如果一個(gè)值的類(lèi)型是UNSIGNED，默認(rèn)生成一個(gè)無(wú)符號(hào)整型的結(jié)果，但是如果結(jié)果是個(gè)負(fù)數(shù)，就會(huì)出現(xiàn)錯(cuò)誤
  如果啟用NO_UNSIGNED_SUBTRACTION，結(jié)果為負(fù)時(shí)不會(huì)報(bào)錯(cuò)

• IGNORE_SPACE
  在函數(shù)名和(中間允許空格。這將導(dǎo)致內(nèi)置函數(shù)名被當(dāng)作保留字處理。因此，與函數(shù)名相同的標(biāo)識(shí)符必須被引用。
  例如，因?yàn)榇嬖贑OUNT()函數(shù)，直接使用count作為表名會(huì)產(chǎn)生錯(cuò)誤

  mysql> CREATE TABLE count (i INT);
  ERROR 1064 (42000): You have an error in your SQL syntax

  應(yīng)該將表名引用起來(lái)：

  mysql> CREATE TABLE count (i INT);
  Query OK, 0 rows affected (0.00 sec)

• HIGH_NOT_PRECEDENCE
  在MySQL5.7中，NOT a BETWEEN b AND C的計(jì)算順序?yàn)?，NOT (a BETWEEN b AND c)
  啟用HIGH_NOT_PRECEDENCE后，該順序更改為(NOT a) BETWEEN b AND c

用來(lái)限制SHOW CREATE TABLE語(yǔ)句的輸出結(jié)果的

• NO_FIELD_OPTIONS
  在SHOW CREATE TABLE的輸出中不顯示特定與MySQL的列選項(xiàng)
• NO_KEY_OPTIONS
  在SHOW CREATE TABLE的輸出中不顯示特定與MySQL的索引選項(xiàng)
• NO_TABLE_OPTIONS
  在SHOW CRETAE TABLE的輸出中不顯示特定與MySQL的表選項(xiàng)

影響數(shù)據(jù)庫(kù)的行為的

• NO_AUTO_CREATE_USER
  如果不指定身份驗(yàn)證信息，GRANT語(yǔ)句不會(huì)自動(dòng)創(chuàng)建用戶。GRANT語(yǔ)句必須使用IDENTIFIED BY語(yǔ)句指定一個(gè)非空的密碼或者使用IDENTIFIED WITH語(yǔ)句指定認(rèn)證插件。
  建議使用CREATE USER語(yǔ)句來(lái)創(chuàng)建用戶
• NO_AUTO_VALUE_ON_ZERO
  NO_AUTO_VALUE_ON_ZERO 影響對(duì)于自動(dòng)增長(zhǎng)列的處理。通常，通過(guò)插入NULL或者0來(lái)生成下一個(gè)序列號(hào)。NO_AUTO_VALUE_ON_ZERO允許在自動(dòng)增長(zhǎng)列中插入0值，這樣只有插入NULL才能生成下一個(gè)序列號(hào)。
• NO_ENGINE_SUBSTITUTION
  當(dāng)一個(gè)語(yǔ)句，例如CREATE TABLE或ALTER TABLE指定一個(gè)禁用或者未編譯的存儲(chǔ)引擎時(shí)，自動(dòng)替換為默認(rèn)存儲(chǔ)引擎。當(dāng)沒(méi)有啟用NO_ENGINE_SUBSTITUTION時(shí)。如果指定的存儲(chǔ)引擎不可用，對(duì)于CREATE TABLE，使用默認(rèn)存儲(chǔ)引擎并生成警告，對(duì)于ALTER TABLE，生成警告并且不會(huì)對(duì)表進(jìn)行修改。
  當(dāng)啟用NO_ENGINE_SUBSTITUTION時(shí)，如果指定的存儲(chǔ)引擎不可用，無(wú)論是創(chuàng)建表還是修改表都會(huì)導(dǎo)致錯(cuò)誤。
• PAD_CHAR_TO_FULL_LENGTH
  默認(rèn)情況下，在查詢時(shí)，CHAR列末尾的空格會(huì)自動(dòng)刪除。啟用PAD_CHAR_TO_FULL_LENGTH,則不會(huì)刪除空格，將查詢到的CHAR值補(bǔ)全到完整的列長(zhǎng)度。
• NO_DIR_IN_CREATE
  在創(chuàng)建表時(shí)，忽略所有INDEX DIRECTORY和DATA DIRECTORY指令。這個(gè)選項(xiàng)在復(fù)制的從庫(kù)中很有用。
• ONLY_FULL_GROUP_BY
  在SELECT HAVING或者ORDER BY列表中不能包含沒(méi)有在GROUP BY子句中命名或者不能通過(guò)GROUP BY子句唯一確定的列
  請(qǐng)參考http://www.ywnds.com/?p=8184
• ALLOW_INVALID_DATES
  允許無(wú)效的日期，只檢查月份在1-12之間和日期在1-31之間，不對(duì)日期進(jìn)行完整的檢查。這個(gè)模式只應(yīng)用與DATE和DATETIME列。在嚴(yán)格模式禁用的情況下，諸如"2018-02-31"這樣的無(wú)效日期會(huì)被轉(zhuǎn)換為'0000-00-00‘并產(chǎn)生警告，如果啟用了嚴(yán)格模式，這樣的無(wú)效日期會(huì)產(chǎn)生錯(cuò)誤。

SQL模式的組合

以下模式是對(duì)上述SQL模式完整列表中的部分組合的縮寫(xiě)

看完設(shè)置和查詢MYSQL SQL模式方法這篇文章后，很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容，如需獲取更多的行業(yè)信息，可以關(guān)注我們的行業(yè)資訊欄目。