MySQL的SQL模式介紹

180次閱讀

共計 9601 個字符，預計需要花費 25 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 的 SQL 模式介紹”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“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 參數來驗證外鍵。

– 示例①
嚴格 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] 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] 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)

– 示例⑤
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)

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

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

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

正文完

發表至：數據庫

2023-08-01

轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。

Mybatis分頁插件的示例分析

如何解決MongoDB謹防索引seek的效率問題

Redis的QPS浮動是多少

MySQL索引及優化的知識點有哪些

MySQL常用的命令操作大全

久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

MySQL的SQL模式介紹