快捷導(dǎo)航

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

更新時(shí)間：2024年07月05日 10:10:29 作者：碼農(nóng)老張Zy

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

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

sql_mode

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

如何查看當(dā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)

這是我們?cè)谔摂M機(jī)上通過(guò) RPM 安裝的 MySQL ，沒(méi)有對(duì) sql_mode 進(jìn)行其它的修改，因此這就是默認(rèn)的參數(shù)。默認(rèn)情況下，MySQL8 的 sql_mode 就是這些內(nèi)容。

設(shè)置 sql_mode

我們新建一個(gè)表，嘗試一下日期相關(guān)的操作。

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;

接下來(lái)，我們插入一條數(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

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

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

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

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ù)庫(kù)是老版本的，現(xiàn)在遷移到 MySQL8 的話，那么去掉 sql_mode 中的 NO_ZERO_IN_DATE 和 NO_ZERO_DATE 參數(shù)就可以了，NO_ZERO_DATE 代表是否允許 0000 這種格式，而 NO_ZERO_IN_DATE 則是針對(duì)日期和月份部分是否為 00 。

其它的參數(shù)其實(shí)通過(guò)名字我們也能看出來(lái)個(gè)大概，ERROR_FOR_DIVISION_BY_ZERO 表示除 0 相關(guān)的信息，如果插入或更新數(shù)據(jù)時(shí)有除 0 相關(guān)操作，比如 MOD(2,0) 這樣，就會(huì)報(bào)出警告。

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 中引用聚合列的查詢。看不懂沒(méi)關(guān)系，我們直接看下面的例子。

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

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)

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

除了上面這些內(nèi)容之外，sql_mode 還有很多設(shè)置，我們?cè)賮?lái)演示一個(gè) NO_AUTO_VALUE_ON_ZERO 。默認(rèn)情況下，我們進(jìn)行數(shù)據(jù)插入時(shí)，給自增長(zhǎng)列指定 null 或者 0 ，都會(huì)從1開(kāi)始正常自動(dòng)增長(zhǎng)，但現(xiàn)在我們讓 0 不是產(chǎn)生自增長(zhǎng)，而是確定的插入一個(gè) 0 ，就可以使用這個(gè)參數(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ù)，如果再次插入的話，就會(huì)報(bào)主鍵重復(fù)，現(xiàn)在 0 就會(huì)被當(dāng)成一個(gè)正常的數(shù)字 0 ，而不會(huì)轉(zhuǎn)化成為 null 的形式進(jìn)行自動(dòng)增長(zhǎng)操作。

總結(jié)

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

參考文檔：

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

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

您可能感興趣的文章: