MySQL配置sql_mode的參數(shù)屬性作用

更新時間：2024年07月05日 10:10:29 作者：碼農老張Zy

,通過設置sql_mode,可以完成不同嚴格程度的數(shù)據(jù)校驗,本文主要介紹了MySQL配置sql_mode的作用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

不知道你有沒有踫到過這種問題，在 MySQL8 默認的情況下，我們之前習慣的為 DateTime 類型指定的 0000-00-00 這種格式是無法插入或者修改數(shù)據(jù)的。其實這種情況就是 MySQL 模式設置的問題，也就是我們今天要講的 sql_mode 這個參數(shù)屬性的作用。

sql_mode

根據(jù)官網(wǎng)的解釋，MySQL 服務器是可以在不同的 SQL 模式中運行的，這個模式會影響 MySQL 支持的 SQL 語法及其執(zhí)行的數(shù)據(jù)驗證檢查。通過模式的設置，可以讓不同環(huán)境中使用 MySQL 以及其他數(shù)據(jù)庫服務器一起使用 MySQL 變得更加容易。并且，MySQL 可以將這些模式分別運用于不同的客戶端，也就是說，它是有 SESSION 會話設置能力的一個系統(tǒng)變量。

如何查看當前系統(tǒng)的 sql_mode 呢？和查看系統(tǒng)變量是一樣的。

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.19 sec)

這是我們在虛擬機上通過 RPM 安裝的 MySQL ，沒有對 sql_mode 進行其它的修改，因此這就是默認的參數(shù)。默認情況下，MySQL8 的 sql_mode 就是這些內容。

設置 sql_mode

我們新建一個表，嘗試一下日期相關的操作。

CREATE TABLE `test_mode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

接下來，我們插入一條數(shù)據(jù)。

mysql> insert into test_mode values(null,'0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

很明顯，默認情況下，0000 這種形式插入日期是不行的，這時我們就可以修改 sql_mode ，讓它回到 MySQL5 的時代，可以直接插入這種形式的日期數(shù)據(jù)。

[server]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

我們可以通過上面的方式直接修改 my.cnf 文件，當然也可以通過 SET sql_mode=xxx 的形式在命令行中動態(tài)修改，如果是修改的 my.cnf 文件，則需要重啟 MySQL 服務，再次執(zhí)行插入語句。

mysql> insert into test_mode values(null,'0000-00-00 00:00:00');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_mode;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

如果你之前的數(shù)據(jù)庫是老版本的，現(xiàn)在遷移到 MySQL8 的話，那么去掉 sql_mode 中的 NO_ZERO_IN_DATE 和 NO_ZERO_DATE 參數(shù)就可以了，NO_ZERO_DATE 代表是否允許 0000 這種格式，而 NO_ZERO_IN_DATE 則是針對日期和月份部分是否為 00 。

其它的參數(shù)其實通過名字我們也能看出來個大概，ERROR_FOR_DIVISION_BY_ZERO 表示除 0 相關的信息，如果插入或更新數(shù)據(jù)時有除 0 相關操作，比如 MOD(2,0) 這樣，就會報出警告。

mysql> insert into test_mode values(mod(2,0),'0000-00-00 00:00:00');
ERROR 1365 (22012): Division by 0

ONLY_FULL_GROUP_BY 表示拒絕在 SELECT、HAVING 或 GROUP BY 中引用聚合列的查詢?？床欢疀]關系，我們直接看下面的例子。

-- 有 ONLY_FULL_GROUP_BY
mysql> select sum(id), created_at from test_mode;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'ma_test.test_mode.created_at'; this is incompatible with sql_mode=only_full_group_by

-- 刪掉 ONLY_FULL_GROUP_BY
mysql> select sum(id), created_at from test_mode;
+---------+---------------------+
| sum(id) | created_at          |
+---------+---------------------+
|       6 | 0000-00-00 00:00:00 |
+---------+---------------------+
1 row in set (0.00 sec)

STRICT_TRANS_TABLES 表示為事務性存儲引擎啟用嚴格的SQL模式，如果可能，也為非事務性存儲引擎啟用嚴格的SQL模式。這里提到了一個嚴格模式的概念，嚴格模式控制 MySQL 如何處理 INSERT 或 UPDATE 等更改語句中的無效或缺失值，比如上面我們說過的日期和除零問題，如果沒有 STRICT_TRANS_TABLES 的話，即使有 NO_ZERO_DATE 和 ERROR_FOR_DIVISION_BY_ZERO 參數(shù)，也不會出現(xiàn)錯誤信息。此外，嚴格模式還會影響到建表和修改表的語句，也就是 CREATE 和 ALTER 語句。

mysql> set sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+--------------------------------------------------------------------------------+
| Variable_name | Value                                                                          |
+---------------+--------------------------------------------------------------------------------+
| sql_mode      | NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test_mode values(mod(2,0) ,'0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.29 sec)

最后一個 NO_ENGINE_SUBSTITUTION 則是在創(chuàng)建或者修改表時，如果指定了一個不存在的表引擎，是報錯還是使用默認引擎替換并警告。

除了上面這些內容之外，sql_mode 還有很多設置，我們再來演示一個 NO_AUTO_VALUE_ON_ZERO 。默認情況下，我們進行數(shù)據(jù)插入時，給自增長列指定 null 或者 0 ，都會從1開始正常自動增長，但現(xiàn)在我們讓 0 不是產(chǎn)生自增長，而是確定的插入一個 0 ，就可以使用這個參數(shù)。

mysql> set sql_mode='xxxxxxx,NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_mode values(0, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_mode values(0, null);
ERROR 1062 (23000): Duplicate entry '0' for key 'test_mode.PRIMARY'

mysql> select id, created_at from test_mode;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  0 | NULL                |
|  1 | 0000-00-00 00:00:00 |
|  2 | 0000-00-00 00:00:00 |
|  3 | 0000-00-00 00:00:00 |
|  4 | NULL                |
|  5 | NULL                |
|  6 | 0000-00-00 00:00:00 |
|  7 | 0000-00-00 00:00:00 |
|  8 | 0000-00-00 00:00:00 |
|  9 | NULL                |
| 10 | NULL                |
+----+---------------------+
11 rows in set (0.00 sec)

可以看到在數(shù)據(jù)中，有了一條 id 為 0 的數(shù)據(jù)，如果再次插入的話，就會報主鍵重復，現(xiàn)在 0 就會被當成一個正常的數(shù)字 0 ，而不會轉化成為 null 的形式進行自動增長操作。

總結

今天的內容簡單地介紹了一下 sql_mode 這個屬性相關的作用以及一些常用的參數(shù)設置。另外還有一部分設置可能使用得比較少，而且大部分情況下我們也不太會去修改這一塊的配置，所以大家了解一下即可。在轉移或升級到 MySQL8 之后，其實最常見的問題就是上面說過的日期問題，0格式日期這種形式其實是已經(jīng)過時的方式了，也是不推薦的方式，所以在 MySQL8 中會默認在嚴格模式下禁用這種形式的日期存儲，這一點也是大家需要注意的，能使用正常日期或者使用 null 最好，另外數(shù)字時間戳存 0 也是可以表示這類空日期格式的，具體需求還是看你的業(yè)務情況來具體分析哦。

參考文檔：

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

到此這篇關于MySQL配置sql_mode的作用的文章就介紹到這了,更多相關MySQL配置sql_mode內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: