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

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

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

Auto-Increment

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

MySQL 5.7 的自增

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

MySQL 8 的自增持久化

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

示例對比

讓我們用一個簡單的例子來說明 MySQL 5.7 和 MySQL 8 在持久自增計數器方面的區(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)

在表中插入三條數據，可以查看到。

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 的記錄和插入新記錄后，與預期一致，我們觀察到新記錄的 ID 為 4。

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

現在，我們從 users 表中刪除最后一條記錄（ID=4），重啟服務器，并檢查表內容。

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 中，重啟會導致自動增長計數器重置為較低的值，從而使新的記錄插入時采用 ID 3。

MySQL 8 的解決方案

MySQL 8 解決了在服務器重啟時 InnoDB 存儲引擎出現的自增計數器丟失的問題。這項增強可以確保自增計數器的值在服務器重啟后持久化，從而保證主鍵生成的一致性。
在 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)

在表中插入三條數據，可以查看到。

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）后，重啟服務器并查看表。

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 中，插入新記錄時，如預期那樣采用 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)