分享到: 分享到QQ  分享到Twitter

作者: BigLoser    访问次数: 513 创建时间: 2023-10-06 19:07:13 更新时间: 2024-06-18 03:48:00

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,开发者可以利用这个功能创建更加坚实的应用程序,可以管理不同的故障情况而不影响数据完整性。

季度最有价值文章

月度最有价值文章

投票统计

是否原创: 0 %

0 % Complete (success)

是否有价值: 0 %

0% Complete

是否有素质: 0 %

0% Complete (warning)

是否合法: 0 %

0% Complete

   群组工具

   外部链接