Mysql自增主键回溯的坑[数据库教程]

  • Z时代
  • 2024-01-10
  • 分类：综合

database

使用mysql的时候，很多时候用自增主键。正常使用一般是没有问题的，但是极小概率情况下会碰到主键回溯的问题。在业务上可能造成id一样，但是对应的业务数据不一样的问题。这个问题发生在Mysql 8.0版本之前。

出现场景： 插入一条数据返回主键3， 接着删除了主键为3的数据，这个时候突然数据库宕机了，重启之后再进行插入，返回的主键还是3。正常这个时候应该是返回主键4的，与预期不一致，对业务数据会造成不一致。

模拟场景：

1、建一张表：

mysql> CREATE TABLE t (
    ->      a INT AUTO_INCREMENT PRIMARY KEY,
    ->      b VARCHAR(255) default null
    ->  );
Query OK, 0 rows affected (0.03 sec)

2, 插入数据：

mysql> insert into t(`b`) values (‘xxx‘),(‘yyy‘),(‘zzz‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

3, 查看插入的数据

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 | xxx  |
| 2 | yyy  |
| 3 | zzz  |
+---+------+
3 rows in set (0.00 sec)

4, 删除主键为3的数据

mysql> delete from t where a=3;
Query OK, 1 row affected (0.01 sec)

5，这个时候如果直接插入数据，返回的主键是4，这是正常的行为。但是如果这个时候突然宕机了，重启再插入数据，返回的主键还是3。这里只模拟异常情况，将mysql服务器重启，然后再插入数据。

mysql> insert into t (`b`) values (‘new_line‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---+----------+
| a | b        |
+---+----------+
| 1 | xxx      |
| 2 | yyy      |
| 3 | new_line |
+---+----------+
3 rows in set (0.00 sec)

6、可以看到刚插入的数据，主键还是3。

原因解释： MySQL 8.0 版本前，自增不持久化，数据库重启时会发生主键回溯。

解决方法：

  • 升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；

  • 若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。

造成影响：如果前端插入一条user的数据，返回给前端一个id，这个时候如果有客户端删除了这个id对应的数据，刚好宕机，重启后又插入一条新的user数据，那么会覆盖掉之前的user。这是非常严重的业务数据错乱，造成生产事故的。

Mysql 自增主键回溯的坑

原文：https://www.cnblogs.com/xstar-website/p/14987476.html

以上是 Mysql自增主键回溯的坑[数据库教程] 的全部内容， 来源链接： utcz.com/z/535750.html

回到顶部