MySQL的SQL模式介紹

這篇文章主要講解了“MySQL的SQL模式介紹”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL的SQL模式介紹”吧！

MySQL服務器能夠在運行在不同的SQL模式下，并且能在不同客戶端下設置不同的SQL模式，可以通過sql_mode系統參數來設定SQL模式。
SQL模式會影響MySQL支持的SQL語法和數據的驗證。

MariaDB [test]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.08 sec)

NO_AUTO_CREATE_USER
防止通過GRANT語句創建沒有密碼的數據庫賬戶。在通過GRANT創建用戶時，必須在IDENTIFIED BY后指定非空的密碼。建議使用CREATE USER語句創建數據庫賬戶，盡量避免使用GRANT語句來創建數據庫賬戶。

NO_ENGINE_SUBSTITUTION
當執行CREATE TABLE 或 ALTER TABLE語句，指定了一個不支持或沒有編譯的存儲引擎時，控制如何自動替換默認的存儲引擎。
當NO_ENGINE_SUBSTITUTION參數沒有啟用時，對于CREATE TABLE語句，如果指定的存儲引擎不可用，會使用默認的存儲，且會產生一個警告。對于ALTER TABLE語句，會產生一個警告，且表的存儲引擎轉化失敗。
當NO_ENGINE_SUBSTITUTION參數啟用時，如果指定的存儲引擎不可用，會發生報錯，且表不會創建或更改成功。

--最重要的SQL模式

ANSI
這個模式改變SQL的語法和行為以更接近標準的SQL。

STRICT_TRANS_TABLES
如果值不能插入到事務表中，則退出這條語句；對于非事務表，如果插入或更新的值發生在一行的SQL或多行語句的第一行中，則退出語句。

TRADITIONAL
當插入一個錯誤的值到一列中時，MySQL會報錯而不是警告。


--SQL模式的結合

有一些特殊的SQL模式是一些SQL結合在一起的縮寫

例如，ANSI是REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,ONLY_FULL_GROUP_BY（MySQL 5.7.5）模式的結合；
DB2是PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS模式的結合。

--嚴格SQL模式

嚴格模式控制MySQL如何處理數據改變SQL語句（例如INSERT或UPDATE）中無效或丟失的值。插入或更新的值無效有許多原因，例如，有可能因為字段的數據類型，或值超過了字段的最大范圍值。對于一個非空字段，當要插入的一個值為空，且沒有默認值，這是值丟失。嚴格模式也會影響DDL語句，例如CREATE TABLE。

如果嚴格模式沒有生效，對于無效或丟失的值，MySQL會插入調整的值。在嚴格模式下，可以通過INSERT IGNORE或UPDATE IGNORE語句來跳過錯誤。

在嚴格模式下，對于類似SELECT這樣不改變數據的語句，無效的值會產生一個警告，而不是錯誤。在嚴格模式下，對于插入或更新超過字段最大長度的值，會產生一個錯誤；而在非嚴格模式下，會產生一個警告，并將截斷后的值插入或更新到表中。

嚴格模式不會影響是否外鍵驗證?？梢酝ㄟ^foreign_key_checks參數來驗證外鍵。

MariaDB [test]> show variables like '%foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)


--示例①
嚴格SQL模式

MariaDB [test]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.08 sec)

MariaDB [test]> create table t (name varchar(20), email varchar(40));
Query OK, 0 rows affected (0.21 sec)

MariaDB [test]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t;
+----------------------+----------------------------+
| name                 | email                      |
+----------------------+----------------------------+
| 12345678900000000000 | fire beijing @ hotmail.com |
+----------------------+----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');
ERROR 1406 (22001): Data too long for column 'name' at row 1


--示例②
ANSI模式和傳統模式的區別

MariaDB [test]> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> create table t(d datetime);
Query OK, 0 rows affected (0.21 sec)

MariaDB [test]> insert into t values('2007-04-31');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [test]> select * from t;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('2007-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

--示例③
ANSI模式和傳統模式的對比

MariaDB [test]> set sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.11 sec)

MariaDB [test]> create table t(i int);
Query OK, 0 rows affected (0.45 sec)

MariaDB [test]> insert into t values(9%0);
Query OK, 1 row affected (0.08 sec)

MariaDB [test]> select * from t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t values(9%0);
ERROR 1365 (22012): Division by 0

--示例④
使反斜線稱為普通字符

MariaDB [test]> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode                                          |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.14 sec)

MariaDB [test]> create table t (context varchar(20));
Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> insert into t values('\beijing');
Query OK, 1 row affected (0.07 sec)

MariaDB [test]> select * from t;
+---------+
| context |
+---------+
|eijing |
+---------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('\\beijing');
Query OK, 1 row affected (0.11 sec)

MariaDB [test]> select * from t;
+----------+
| context  |
+----------+
|eijing  |
| \beijing |
+----------+
2 rows in set (0.00 sec)

MariaDB [test]> set sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t values('\\beijing');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from t;
+-----------+
| context   |
+-----------+
|eijing   |
| \beijing  |
| \\beijing |
+-----------+
3 rows in set (0.00 sec)

--示例⑤
ANSI模式包含了PIPES_AS_CONCAT模式，會將||視為連接操作符

MariaDB [test]> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode                                          |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select 'beijing'||'2008';
+-------------------+
| 'beijing'||'2008' |
+-------------------+
| beijing2008       |
+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select 'beijing'||'2008';
+-------------------+
| 'beijing'||'2008' |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 2 warnings (0.02 sec)

MariaDB [test]> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'beijing' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'beijing'  |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

--示例⑥
在將MySQL數據庫遷移到其他數據時，可以設置sql_mode為NO_TABLE_OPTIONS模式，這樣可以去掉engine關鍵字

MariaDB [test]> show create table t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `context` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show create table t;
+-------+-----------------------------------------------------------+
| Table | Create Table                                              |
+-------+-----------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `context` varchar(20) DEFAULT NULL
) |
+-------+-----------------------------------------------------------+
1 row in set (0.00 sec)

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