怎么設置MySQL SQL模式

這篇文章主要講解了“怎么設置MySQL SQL模式”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“怎么設置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模式的影響。

感謝各位的閱讀，以上就是“怎么設置MySQL SQL模式”的內容了，經過本文的學習后，相信大家對怎么設置MySQL SQL模式這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！