MySQL SQL模式

MySQL伺服器可以以不同的SQL模式來進行操作，並且依賴於sql_mode系統變數的值對不同的客戶端可以應用這些不同的SQL模式。DBA可以設定全域性SQL模式來匹配伺服器操作要求，並且每種應用程式可以設定它的會話SQL模式來滿足它的要求。

SQL模式會影響MySQL支援的SQL語法和資料驗證檢查。這可以在不同環境中讓MySQL與其它資料庫一起使用變得更容易。

當使用InnoDB表時，可以考慮使用innodb_strict_mode系統變數，它可以對InnoDB表啟用額外的錯誤檢查。

設定SQL模式
在MySQL 5.7中預設的SQL模式包含: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。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。

為了在伺服器啟動時設定SQL模式，可以在命令列中使用--sql-mode="modes"選項或在選項檔案比如Unix中的my.cnf或Windows上的my.ini檔案中使用sql-mode="modes"選項。modes是用逗號分的不同模式列表。為了顯式的清除SQL模式，可以在命令列中使用--sql-mode=""選項將SQL模式設定為空字串，或者在選項檔案中使用sql-mode=""。

MySQL安裝程式可以在安裝過程中會配置SQL模式。例如，mysql_install_db將在基本的安裝目錄中建立一個命名為my.cnf的預設選項檔案。這個檔案包含設定SQL模式的記錄。

如果SQL模式不同於預設SQL模式或你所期待的SQL模式，可以檢查伺服器在啟動時所讀取的選項檔案。

為了在執行時改變SQL模式，可以使用set語句來設定全域性或會話級的sql_mode系統變數:
set global sql_mode='modes';
set session sql_mode='modes';

設定global變數需要有super許可權並且影響所有連線的客戶端操作。設定session變數隻影響當前客戶端。每個客戶端可以在任何時間改變它會話的sql_mode值來達到改變SQL模式的目的。

為了判斷當前全域性或session級的sql_mode值，執行以下命令:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意在建立分割槽表並插入資料之後改變伺服器的SQL模式會對錶的行為造成重大改變並且可能導致丟失或損壞資料。所以強烈建議在建立使用者定義的分割槽表之後不要修改SQL模式。

當複製分割槽表時，在主從伺服器之間不同的SQL模式也會導致一些問題。所以最好的結果就是在主從伺服器上使用相同的SQL模式。

最重要的SQL模式
MySQL中最重要的sql_mode值可能是這些:
.ANSI
這種SQL模式改變語法和行為使其更接近標準SQL。它是一種特定的組合模式列表。

.STRICT_TRANS_TABLES
如果一個值不能以指定的方式插入到一個事務表，終止這個語句。對於非事務表，如果這個值在一個單行記錄語句或在多行記錄語句第一個出現時終止語句。從MySQL 5.7.5開始預設的SQL模式包括STRICT_TRANS_TABLES。

.TRADITIONAL
使用MySQL行為像一個傳統的SQL資料庫系統。簡單來說這種模式對於將一個不正確值插入到一個列中時丟擲一個錯誤來代替一個警告。它是特定組合模式中的一種。insert或update一旦發現錯誤會立即終止。如果您正在使用非事務性儲存引擎，這可能不是您想要的，因為在錯誤之前所做的資料更改可能無法回滾，從而導致“部分完成”的更新。

當提到"strict mode"嚴格模式時，它意味著是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一種或兩種都被啟用。

完整的SQL模式列表
下面是所有支援的SQL模式列表:
.ALLOW_INVALID_DATES
不對日期執行完全檢查。只對月份的範圍從1到12和日期的範圍從1到31執行檢查。這對於Web應用程式非常方便，您可以在三個不同的欄位中獲得年、月和日，並且希望準確地儲存使用者插入的內容(不執行日期校驗)。這種SQL模式應用於date和datetime列。它不應用於timestamp列，因為它總是請求一個合法的日期值。

這種SQL模式要求月分與每天的取值是合法值，並且範圍分別不能超過1到12和1到31。當嚴格模式被禁用時，一個無效的日期值比如'2014-04-31'將被轉換成'0000-00-00'並且生成一個警告。當使用嚴格模式時，一個無效的日期值會生成一個錯誤。為了允許這樣的日期值，啟用ALLOW_INVALID_DATES模式。

.ANSI_QUOTES
將"作為標識引用字元(像`引用字元)而不是字串引用字元。當使用這種SQL模式時仍然可以使用`作為引用識別符號，當ANSI_QUOTES被使用時，不能使用雙引號來引用文字字串，因為它被解析作識別符號了。

.ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO模式影響對除以零的處理，這包含MOD(N,0)。對於資料修改操作(insert,update)，它的影響也依賴於是否啟用了嚴格SQL模式。
-如果這種模式沒有被啟用，除以零會插入NULL值並且沒有警告。
-如果這種模式被啟用，除以零會插入NULL值並生成警告。
-如果這種模式與嚴格SQL模式被啟用，除以堆生成一個錯誤，除非IGNORE也被指定。對於insert ignore和update ignore，除以零會插入NULL值並生成警告。

對於select語句，除以零會返回NULL值。啟用ERROR_FOR_DIVISION_BY_ZERO不管是否啟用嚴格SQL模式會導致生成一個警告。

在MySQL 5.7.4中，ERROR_FOR_DIVISION_BY_ZERO被丟棄，在MySQL 5.7.4到5.7.7中當顯式命名時ERROR_FOR_DIVISION_BY_ZERO不會做任何事。代替的是，它的影響被包含在嚴格SQL模式中。在MySQL 5.7.8和以後的版本中，當顯式命名時ERROR_FOR_DIVISION_BY_ZERO會有影響並且沒有包含在嚴格SQL模式中，就像MySQL5.7.4之前的版本一樣。然而預設情況下當嚴格模式啟用下應該與它聯合使用。如果ERROR_FOR_DIVISION_BY_ZERO被啟用而沒有啟用嚴格模式或者當啟用嚴格模式而沒有啟用ERROR_FOR_DIVISION_BY_ZERO時會出現這個警告。

因為ERROR_FOR_DIVISION_BY_ZERO被丟棄，它將在將來的版本中被刪除並作為一個單獨的模式名並且它的影響被包含在嚴格SQL模式。

.HIGH_NOT_PRECEDENCE
NOT操作的優先順序就像NOT a between b and c會被解析成NOT (a between b and c)一樣。在一些舊版本的MySQL中，表示式會被解析成(NOT a) between b and c。舊版本更高優先順序行為可以透過啟用HIGH_NOT_PRECEDENCE模式來獲得。

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

.IGNORE_SPACE
允許在函式名與(符號之間存在空格。這會造成內建函式名被作為保留關鍵字對待。因此識別符號與函式名相同必須有引號。例如，因為有一個count()函式，因此在下面的語句中使用count作為表名就會出錯:

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.13 sec)
mysql> drop table count cascade;
Query OK, 0 rows affected (0.09 sec)
mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1
mysql> create table `count` (i INT);
Query OK, 0 rows affected (0.16 sec)

IGNORE_SPACE模式應用到內建函式，而不是使用者定義的函式或儲存過程。它總是允許在使用者定義的函式或儲存過程名後有空格而不管是否啟用了IGNORE_SAPCE模式。

.NO_AUTO_CREATE_USER
除非指定了身份驗證資訊，否則將阻止GRANT語句自動建立新使用者帳戶。這個語句必須使用identified by來指定非空密碼或使用identified with來使用一種驗證外掛。

最好使用create user來建立MySQL賬號，然後使用Grant語句。NO_AUTO_CREATE_USER已經被丟棄並且預設的SQL模式包含了NO_AUTO_CREATE_USER模式。將sql_mode修改為NO_AUTO_CREATE_USER模式會生成一個警告，除了指定sql_mode為DEFAULT.NO_AUTO_CREATE_USER將會在將來的版本中被刪除，到時它的影響將會一直被啟用。

之前，在NO_AUTO_CREATE_USER被丟棄之前，一個不啟用它的原因是它是不安全的複製。現在它可以被啟用並且使用create user if not exists,drop user if exists和alter user if exists而不是grant語句來執行安全複製管理。這些語句當從伺服器相對於主伺服器有不同的授權時可以啟用安全複製。

.NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影響對AUTO_INCREMENT列的處理。正常來說透過向訪列插入NULL或0來為列生成下一個序列值。NO_AUTO_VALUE_ON_ZERO會抑制0的行為因此只有插入NULL時會生成下一個序列值。如果0已經被儲存到表的AUTO_INCREMENT列中這個SQL模式可能是有用的。(儲存0是不建議的)例如，如果使用mysqldump來dump表並且然後再載入它，當遇到值0時MySQL正常來說會生成一個新的序列值，因此表的內容不同於被dump的內容。在載入dump檔案之前啟用NO_AUTO_VALUE_ON_ZERO來解決這個問題。mysqldump現在在它的輸出中自動包含一個語句來啟用NO_AUTO_VALUE_ON_ZERO來避免這個問題。

.NO_BACKSLASH_ESCAPES
禁用在字串中使用反斜槓字元(\)作為跳脫字元。啟用此模式後，反斜槓將成為與其他字元一樣的普通字元。

.NO_DIR_IN_CREATE
建立表時，忽略所有索引目錄和資料目錄指令。此選項在從複製伺服器上非常有用。

.NO_ENGINE_SUBSTITUTION
當一個語句比如create table或alter table指定一個儲存引擎已經被禁用或沒有被編譯時控制預設儲存引擎的自動替換。預設的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因為儲存引擎可以在執行時被附加進來，不可以儲存引擎也以相同方式被對待:
當NO_ENGINE_SUBSTITUTION被禁用，對於create table的預設儲存引擎被使用並且如果期待的儲存引擎不可用會出現一個警告。對於alter table，會出現一個警告並且表不能被修改。
當NO_ENGINE_SUBSTITUTION被啟用時，如果期待的儲存引擎不可用會出現一個警告並且表不會被建立或被修改。

.NO_FIELD_OPTIONS
在show create table輸出中不列印特定MySQL列選項。這種SQL模式被mysqldump以可移植模式來使用。

.NO_KEY_OPTIONS
在show create table輸出中不列印特定MySQL索引選項。這種SQL模式被mysqldump以可移植模式來使用。

.NO_TABLE_OPTIONS
在show create table輸出中不列印特定MySQL表選項(比如ENGINE)。這種SQL模式被mysqldump以可移植模式來使用。

.NO_UNSIGNED_SUBTRACTION
兩個整數相減，這裡一種型別UNSIGNED，預設情況下生成一個沒有符號的結果。如果結果出現負數將會出現錯誤:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

如果NO_UNSIGNED_SUBTRACTION模式被啟用，結果將是負數:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

如果使用此類操作的結果更新無符號整數列，則將該結果裁剪為該列型別的最大值，如果啟用no_unsigned_subtract，則將其裁剪為0。如果嚴格SQL模式被啟用，則會出現錯誤並且列會保持不變。

當no_unsigned_subtraction被啟用時，就算任何運算元據是無符號的相減的結果是有符號的。例如比較表t1中的c2列與表t2中的c2列:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.20 sec)
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

這意味著bigint unsigned在所有上下文中不是100%可以使用。

.NO_ZERO_DATE
NO_ZERO_DATE模式影響伺服器是否允許'0000-00-00'作為一種有效的日期。它的影響也依賴於是否啟用了嚴格SQL模式。
-如果模式沒有被啟用，'0000-00-00'被允許並且插入不會產生警告。
-如果模式被啟用，'0000-00-00'被允許並且插入會產生警告。
-如果模式和嚴格SQL模式被啟用，'0000-00-00'不被允許並且插入會出現錯誤，除非指定IGNORE選項。對於insert ignore和update ignore來說，'0000-00-00'被允許並且插入會產生警告。

在MySQL 5.7.4中，NO_ZERO_DATE被丟棄。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE當顯式指定時不會工作。代替地是它的影響已經被包含在嚴格SQL模式中。在MySQL 5.7.8和以後的版本中，當顯式地指定NO_ZERO_DATE模式時它會工作並且它不是嚴格SQL模式的一部分就像MySQL5.7.4版本之前作用一樣。然而，預設情況下它應該與嚴格SQL模式聯合使用。如果啟用NO_ZERO_DATE而沒有啟用嚴格SQL模式就是出現警告或者反之亦然。

因為NO_ZERO_DATE已經被丟棄，因此它會在將來的版本中作為單獨的模式名被刪除並且它的影響會包含在嚴格SQL模式中。

.NO_ZERO_IN_DATE
NO_ZERO_IN_DATE模式影響伺服器是否允許日期中的年部分為非0但月或日部分為0。(這種模式影響日期比如'2010-00-01'或'2010-01-00'，而不是'0000-00-00'。為了控制伺服器是否允許'0000-00-00'，使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影響也依賴於是否啟用了嚴格SQL模式。
-如果這種模式沒有啟用，有為0部分折日期被允許並且插入不產生警告。
-如果這種模式被啟用，有為0部分的日期值將以'0000-00-00'格式被插入並且生成一個警告。
-如果這種模式與嚴格SQL模式被啟用，有為0部分的日期不被允許並且插入會產生一個錯誤，除非你指定ignore。對於insert ignore和update ignore來說，有為0部分的日期將以'0000-00-00'格式被插入並且生成一個警告。

在MySQL 5.7.4中，NO_ZERO_IN_DATE被丟棄。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE當顯式指定時不會工作。代替地是它的影響已經被包含在嚴格SQL模式中。在MySQL 5.7.8和以後的版本中，當顯式地指定NO_ZERO_IN_DATE模式時它會工作並且它不是嚴格SQL模式的一部分就像MySQL5.7.4版本之前作用一樣。然而，預設情況下它應該與嚴格SQL模式聯合使用。如果啟用NO_ZERO_IN_DATE而沒有啟用嚴格SQL模式就是出現警告或者反之亦然。

因為NO_ZERO_IN_DATE已經被丟棄，因此它會在將來的版本中作為單獨的模式名被刪除並且它的影響會包含在嚴格SQL模式中。

.ONLY_FULL_GROUP_BY
拒絕那些select list、HAVING condition或ORDER BY list引用非聚合列的查詢，這些列既不在GROUP BY子句中命名，也不依賴於GROUP BY列(由GROUP BY列唯一確定)。

從MySQL 5.7.5開始，預設的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前，MySQL沒有檢測到功能依賴並且預設情況下ONLY_FULL_GROUP_BY模式沒有啟用。

MySQL擴充套件了標準SQL來允許在having子句中引用select列表中的別名表示式。在MySQL 5.7.5之前，啟用ONLY_FULL_GROUP_BY模式會禁用這種擴充套件，因此要求having子句以非別名表示式來書寫。從MySQL5.7.5開始，這種限制被取消了因此having子句可以引用別名而不用管ONLY_FULL_GROUP_BY模式是否啟用了。

.PAD_CAHR_TO_FULL_LENGTH
預設情況下，在檢索時從CHAR列值中裁剪尾隨空格。如果PAD_CHAR_TO_FULL_LENGTH被啟用，裁剪不會發生並且在檢索CHAR列值時填充到它的完整長度。這種模式不應用於varchar列。檢索時為其保留尾隨空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.03 sec)
mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

.PIPES_AS_CONCAT
將||作為字串連線運算子(與concat()一樣)而不是作為OR的同義詞。

.REAL_AS_FLOAT
將REAL作為FLOAT的同義詞。預設情況下，MySQL將REAL作為DOUBLE的同義詞。

.STRICT_ALL_TABLES
對所有的儲存引擎啟用嚴格SQL模式。無效的資料會被拒絕。從MySQL 5.7.4到5.7.7，STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影響。

.STRICT_TRANS_TABLES
對事務型儲存引擎啟用嚴格SQL模式並且在可能的情況下使用非事務型儲存引擎。從MySQL 5.7.4到5.7.7，STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影響。