快捷導(dǎo)航

淺談MySQL8和MySQL5.7在自增計數(shù)上的區(qū)別

更新時間：2023年10月08日 10:03:53 作者：愛可生開源社區(qū)

MySQL數(shù)據(jù)庫是一款非常流行的開源數(shù)據(jù)庫,其版本升級迅速,在使用過程中也發(fā)現(xiàn)了不同版本之間存在著一些區(qū)別,本文主要介紹了MySQL8和MySQL5.7在自增計數(shù)上的區(qū)別,感興趣的可以了解一下

Auto-Increment

自增（Auto-Increment）計數(shù)功能可以為主鍵列生成唯一值，這是數(shù)據(jù)庫的一種設(shè)計。與 MySQL 5.7 相比，MySQL 8 為自增功能做了一項重要的升級。這個升級可以確保自增計數(shù)器的最大值在服務(wù)器重啟后保持不變，從而為數(shù)據(jù)一致性和可靠性提供了更好的保障。在本文中，我們將對比 MySQL 5.7 和 MySQL 8 的不同之處，并提供實際示例來展示兩者的區(qū)別。

MySQL 5.7 的自增

在 MySQL 5.7 中，自動增計數(shù)器的工作機(jī)制如下：當(dāng)向包含自增列的表中插入新的一行數(shù)據(jù)時，計數(shù)器會自動加 1，生成的數(shù)值會作為插入行的主鍵使用。這個計數(shù)器值僅保存在內(nèi)存中，在服務(wù)器重啟后無法持久化。因此，如果服務(wù)器崩潰或重啟，計數(shù)器可能會重置為一個較低的值。

MySQL 8 的自增持久化

隨著 MySQL 8 的發(fā)布，自增計數(shù)器機(jī)制有了顯著改進(jìn)。在 MySQL 8 中，自增計數(shù)器的最大值現(xiàn)在可以在服務(wù)器重啟后持久化。這意味著，即使服務(wù)器重啟，自增計數(shù)器也會從上次結(jié)束的地方恢復(fù)，以確保自增主鍵的值保持連續(xù)。

示例對比

讓我們用一個簡單的例子來說明 MySQL 5.7 和 MySQL 8 在持久自增計數(shù)器方面的區(qū)別。我們將創(chuàng)建一個名為 users 的表，用于存儲用戶信息。

在 MySQL 5.7 中建表。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42-46 |
+-----------+
mysql> CREATE TABLE users (
? ? id INT AUTO_INCREMENT PRIMARY KEY,
? ? username VARCHAR(50) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)

在表中插入三條數(shù)據(jù)，可以查看到。

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
| ?3 | user3 ? ?|
+----+----------+
3 rows in set (0.00 sec)

我們繼續(xù)刪除一條記錄并插入一條新記錄。

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

刪除 ID 為 3 的記錄和插入新記錄后，與預(yù)期一致，我們觀察到新記錄的 ID 為 4。

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

現(xiàn)在，我們從 users 表中刪除最后一條記錄（ID=4），重啟服務(wù)器，并檢查表內(nèi)容。

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.00 sec)
service mysql restart
mysql> select * from users;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: ? ?2
Current database: db1
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.01 sec)

表中只剩下兩條記錄。我們插入第五條記錄，判斷它是否采用 ID 5，還是回退為ID 3。

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
| ?3 | user5 ? ?|
+----+----------+
3 rows in set (0.00 sec)

因此，在 MySQL 5.7 中，重啟會導(dǎo)致自動增長計數(shù)器重置為較低的值，從而使新的記錄插入時采用 ID 3。

MySQL 8 的解決方案

MySQL 8 解決了在服務(wù)器重啟時 InnoDB 存儲引擎出現(xiàn)的自增計數(shù)器丟失的問題。這項增強(qiáng)可以確保自增計數(shù)器的值在服務(wù)器重啟后持久化，從而保證主鍵生成的一致性。
在 MySQL 8 中建表。

mysql> select version();
+-------------------------+
| version() ? ? ? ? ? ? ? |
+-------------------------+
| 8.0.33-0ubuntu0.22.04.2 |
+-------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE users (
? ? -> ? ? id INT AUTO_INCREMENT PRIMARY KEY,
? ? -> ? ? username VARCHAR(50) NOT NULL
? ? -> );
Query OK, 0 rows affected (0.04 sec)

在表中插入三條數(shù)據(jù)，可以查看到。

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
| ?3 | user3 ? ?|
+----+----------+
3 rows in set (0.00 sec)

接下來，刪除一條并插入一條。

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

刪除 ID 為 3 的記錄和插入新記錄采用 ID 為 4。

mysql>  select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

刪除最后一條記錄（ID=4）后，重啟服務(wù)器并查看表。

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> ?select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.00 sec)
service mysql restart
mysql> select * from users;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: ? ?8
Current database: db1
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
+----+----------+
2 rows in set (0.02 sec)

重啟后，users 表中只保留兩條記錄。在 MySQL 8 中，插入新記錄時，如預(yù)期那樣采用 ID=5。

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.01 sec)
mysql> ?select * from users;
+----+----------+
| id | username |
+----+----------+
| ?1 | user1 ? ?|
| ?2 | user2 ? ?|
| ?5 | user5 ? ?|
+----+----------+
3 rows in set (0.00 sec)

總結(jié)

MySQL 8 之前版本中的 InnoDB 存儲引擎報告的自增計數(shù)器問題可能會導(dǎo)致困惑和數(shù)據(jù)不一致，特別是在服務(wù)器重啟期間。計數(shù)器的值可能丟失，導(dǎo)致自動生成的主鍵值不匹配。MySQL 8 通過保證自增計數(shù)器在服務(wù)器重啟之間持久化來解決這個問題。

通過升級到 MySQL 8，開發(fā)者可以利用這個功能創(chuàng)建更加堅實的應(yīng)用程序，可以管理不同的故障情況而不影響數(shù)據(jù)完整性。

https://www.percona.com/blog/auto-increment-counter-persistence-in-mysql-8-comparing-the-evolution-from-mysql-5-7/

到此這篇關(guān)于淺談MySQL8和MySQL5.7在自增計數(shù)上的區(qū)別的文章就介紹到這了,更多相關(guān)MySQL8和MySQL5.7自增計數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: