MYSQL SQL模式 （未完成）

標籤：sql   str   dev   說明   glob   操作   tab   串連   pes   

SQL模式影響MySQL支援的SQL文法和執行的資料驗證檢查。
本篇內容根據官方手冊https://dev.mysql.com/doc/refman/5.7/en/sql-mode.htmlvgli 進行整理
已完成部分：設定和查詢SQL模式、MySQL5.7中SQL模式的完整列表
未完成部分：strict 模式的詳細描述、IGNORE關鍵字和strict 模式的關係、在MySQL5.7中SQL模式的更改

設定和查詢SQL模式

通過修改sql_mode變數的值來改變SQL模式。
SQL模式可以在全域層級下設定，也可以在會話層級下設定。在資料庫啟動時和資料庫運行時都可以對sql_mode的值進行修改。

在資料庫啟動時設定SQL模式

在命令列中使用--sql_mode=‘modes‘選項，或者在設定檔中使用sql_mode="modes"。
modes是一個以逗號分隔的模式的列表。
要清除SQL模式，將它設定為一個空的字串，例如sql_mode=""

在資料庫運行時設定SQL模式

使用SET語句來更改sql_mode的值，例如：

SET GLOBAL sql_mode = ‘modes‘;
SET SESSION sql_mode = ‘modes‘;

設定全域變數的值需要SUPER許可權，設定後應用到之後所有用戶端串連的操作。
設定session變數只應用於當前用戶端，每個用戶端都可以在任何時候更改它的sessionSQL模式。

查詢SQL模式

要確定當前使用的SQL模式，使用以下語句進行查詢

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

主要的SQL模式

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

• ANSI
  這是一個組合模式，它似的文法和行為更符合標準的SQL
• STRICT_TRANS_TABLES
  對事務表的strict 模式。在這種模式下，如果一個值不能被插入到事務表中，則終止該語句。對於非事務表，如果不能插入的值發生在單行語句或者多行語句的第一行，也會終止該語句。
• TRADITIONAL
  傳統模式，這也是一個組合模式。在這種模式下，當插入一個不正確的值時，會給出錯誤而不是警告。（在非事務性儲存引擎中，可能這不是我們想要的，因為在發生錯誤時語句會中斷，但是在錯誤發生前進行的資料修改不能夠復原，從而導致部分更新。）

SQL模式的完整列表

sql模式可以大致分為以下幾類

strict 模式（包括STRICT_ALL_TABLES和STRICT_TRANS_TABLES）

• STRICT_ALL_TABLES
  對於所有儲存引擎啟用strict 模式，無效的值會被拒絕。
• STRICT_TRANS_TABLES
  對於事務儲存引擎啟用strict 模式，並在可能的情況下對飛事務儲存引擎啟用strict 模式。

在MySQL5.7.4到MySQL5.7.7中，strict 模式包括ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE的效果。

用來限制0值，和strict 模式一同使用的

• NO_ZERO_DATE
  影響資料庫是否允許‘0000-00-00‘作為一個有效日期。其效果還取決於是否啟用了strict 模式
  如果啟用了該模式，允許‘0000-00-00‘值並且插入不會產生警告
  如果禁用了該模式，允許‘0000-00-00‘值但是插入會產生警告
  如果該模式和strict 模式同時啟用，除非同時給出IGNORE，否則不允許‘0000-00-00‘插入併產生錯誤
• NO_ZERO_IN_DATE
  影響資料庫是否允許在年份非0時，月份或日期為0。其效果還取決於是否啟用了strict 模式。
  如果啟用了該模式，允許包括0的日期值並且插入不會產生警告。
  如果禁用了該模式，允許包括0的日期值但是插入會產生警告。
  如果該模式和strict 模式同時使用，除非同時給出IGNORE,否則不允許插入包含0的日期值並且插入會產生錯誤。對於INSERT IGNORE和UPDATE IGNORE，包含0的日期值會作為‘0000-00-00‘插入併產生警告
• ERROR_FOR_DIVISION_BY_ZERO
  影響資料庫是否允許將0作為除數，包括MOD(N,0)。其效果還取決於是否啟用了strict 模式
  如果啟用了該模式，允許以0作為除數並且插入不會產生警告
  如果禁用了該模式，允許以0作為除數但是插入會產生警告
  如果該模式和strict 模式同時使用，除非同時給出IGNORE，否則不允許以0作為除數並且插入會產生錯誤。對於INSERT IGNORE和UPDATE IGNORE，以0作為除數會插入NULL併產生警告。

在MySQL5.7.4以前版本中，以上三個模式被棄用
在MySQL5.7.4到MySQL5.7.7中，以上三個模式不產生作用，他們的效果包含在strict 模式中。
在MySQL5.7.8及以後版本中，以上三個模式才有自己單獨的作用，而不是strict 模式的一部分。但是，他們應該和strict 模式一起使用，並且預設情況下他們都是開啟的。如果使用strict 模式而不使用上述模式會產生警告，如果使用上述模式中的任何一個但是不啟用strict 模式也會產生警告。
由於以上三個模式以棄用，在後續的MySQL版本中，他們作為一個單獨的模式名會被刪除，並且他們的效果將包含在strict 模式中。

用來說明符號的作用的

• ANSI_QUOTES
  將"作為標識符（與相同）而不是作為字串的引用符號。在啟用此模式的情況下，仍然可以使用作為引用標識符，但是不能使用雙引號來引用文本字串。
• PIPES_AS_CONCAT
  將||作為字串串連操作符（與CONCAT()相同），而不是作為OR的同義字
• REAL_AS_FLOAT
  將REAL作為FLOAT的同義字。預設情況下，MySQL將REAL視為DOUBLE的同義字。
• NO_BACKSLASH_ESCAPES
  禁用反斜線字元()作為字串中的逸出字元。在啟用此模式後，反斜線就變成了一個一般字元。影響語句的方式或結果的

• NO_UNSIGNED_SUBTRACTION
  對於整數之間的減法，如果一個值的類型是UNSIGNED，預設產生一個無符號整型的結果，但是如果結果是個負數，就會出現錯誤
  如果啟用NO_UNSIGNED_SUBTRACTION，結果為負時不會報錯

• IGNORE_SPACE
  在函數名和(中間允許空格。這將導致內建函數名被當作保留文書處理。因此，與函數名相同的標識符必須被引用。
  例如，因為存在COUNT()函數，直接使用count作為表名會產生錯誤

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

  應該將表名引用起來：

  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的計算順序為，NOT (a BETWEEN b AND c)
  啟用HIGH_NOT_PRECEDENCE後，該順序更改為(NOT a) BETWEEN b AND c

用來限制SHOW CREATE TABLE語句的輸出結果的

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

影響資料庫的行為的

• NO_AUTO_CREATE_USER
  如果不指定身分識別驗證資訊，GRANT語句不會自動建立使用者。GRANT語句必須使用IDENTIFIED BY語句指定一個非空的密碼或者使用IDENTIFIED WITH語句指定認證外掛程式。
  建議使用CREATE USER語句來建立使用者
• NO_AUTO_VALUE_ON_ZERO
  NO_AUTO_VALUE_ON_ZERO 影響對於自動成長列的處理。通常，通過插入NULL或者0來產生下一個序號。NO_AUTO_VALUE_ON_ZERO允許在自動成長列中插入0值，這樣只有插入NULL才能產生下一個序號。
• NO_ENGINE_SUBSTITUTION
  當一個語句，例如CREATE TABLE或ALTER TABLE指定一個禁用或者未編譯的儲存引擎時，自動替換為預設儲存引擎。當沒有啟用NO_ENGINE_SUBSTITUTION時。如果指定的儲存引擎不可用，對於CREATE TABLE，使用預設儲存引擎並產生警告，對於ALTER TABLE，產生警告並且不會對錶進行修改。
  當啟用NO_ENGINE_SUBSTITUTION時，如果指定的儲存引擎不可用，無論是建立表還是修改表都會導致錯誤。
• PAD_CHAR_TO_FULL_LENGTH
  預設情況下，在查詢時，CHAR列末尾的空格會自動刪除。啟用PAD_CHAR_TO_FULL_LENGTH,則不會刪除空格，將查詢到的CHAR值補全到完整的列長度。
• NO_DIR_IN_CREATE
  在建立表時，忽略所有INDEX DIRECTORY和DATA DIRECTORY指令。這個選項在複製的從庫中很有用。
• ONLY_FULL_GROUP_BY
  在SELECT HAVING或者ORDER BY列表中不能包含沒有在GROUP BY子句中命名或者不能通過GROUP BY子句唯一確定的列
  請參考http://www.ywnds.com/?p=8184
• ALLOW_INVALID_DATES
  允許無效的日期，只檢查月份在1-12之間和日期在1-31之間，不對日期進行完整的檢查。這個模式只應用與DATE和DATETIME列。在strict 模式禁用的情況下，諸如"2018-02-31"這樣的無效日期會被轉換為‘0000-00-00‘併產生警告，如果啟用了strict 模式，這樣的無效日期會產生錯誤。

SQL模式的組合

以下模式是對上述SQL模式完整列表中的部分組合的縮寫

名稱	完整列表
ANSI	REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,和 (在MySQL 5.7.5) ONLY_FULL_GROUP_BY
DB2	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
MSSQL	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
POSTGRESQL	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
ORACLE	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
MAXDB	PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
TRADITIONAL	STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

MYSQL SQL模式 （未完成）