MySQL 8 和 MySQL 5.7 在自增計數上的區別

發表於2023-09-28
MySql

MySQL 8 和 MySQL 5.7 在自增計數上的區別

作者：Arunjith Aravindan

本文來源：Percona 部落格，愛可生開源社群翻譯。

本文約 900 字，預計閱讀需要 2 分鐘。

Auto-Increment

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

MySQL 5.7 的自增

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

MySQL 8 的自增持久化

隨著 MySQL 8 的釋出，自增計數器機制有了顯著改進。在 MySQL 8 中，自增計數器的最大值現在可以在伺服器重啟後持久化。這意味著，即使伺服器重啟，自增計數器也會從上次結束的地方恢復，以確保自增主鍵的值保持連續。

示例對比

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

我們繼續刪除一條記錄並插入一條新記錄。

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)

總結

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

透過升級到 MySQL 8，開發者可以利用這個功能建立更加堅實的應用程式，可以管理不同的故障情況而不影響資料完整性。

https://www.percona.com/blog/auto-increment-counter-persisten...

相關文章