解决数据库删除数据后id自增连续起来的问题

前言

在MySQL数据库设计中，使用AUTO_INCREMENT属性的列作为主键是一种常见的实践。当设置一个字段为自增长时，每次插入新记录时，该字段的值会自动递增。然而，在实际操作过程中，如果我们删除了表中的某些行，再进行插入操作时，可能会发现主键ID并不连续，这是由自增长机制决定的。本文将深入探讨这一现象，并提供几种应对策略。

现象描述

假设你有一个名为“图书馆管理”的MySQL表，其中包含一个自增长主键id。当你删除了几条记录后，再次插入新数据时，MySQL不会重新分配已被删除记录的ID值，而是继续从当前最大的id值加1开始。这导致即使在物理顺序上存在空缺，逻辑上的主键序列也不再连续。

以下是创建数据库表的代码：

CREATE TABLE 图书馆管理 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    书名 VARCHAR(255) NOT NULL,
    作者 VARCHAR(255) NOT NULL,
    出版社 VARCHAR(255),
    出版日期 DATE,
    价格 DECIMAL(10, 2),
    库存数量 INT
);

  在插入四段数据：

INSERT INTO 图书馆管理 (书名,作者,出版社,出版日期,价格,库存数量)
VALUES ('计算机科学导论', '张三', '清华大学出版社', '2020-01-01', 59.00, 100);

INSERT INTO 图书馆管理 (书名,作者,出版社,出版日期,价格,库存数量)
VALUES ('计算机科学导论', '李四', '清华大学出版社', '2020-01-01', 19.00, 1200);

INSERT INTO 图书馆管理 (书名,作者,出版社,出版日期,价格,库存数量)
VALUES ('微积分', '王五', '清华大学出版社', '2024-01-01', 19.00, 1200);

INSERT INTO 图书馆管理 (书名,作者,出版社,出版日期,价格,库存数量)
VALUES ('数学建模', '陈子', '清华大学出版社', '2020-01-01', 19.00, 1200);

   如果你想删除一段数据：

DELETE FROM 图书馆管理 WHERE id=3;

  删除后id=2后直接是id=4，通常情况下，主键ID的连续性对于数据库的正常运行和功能实现并无直接影响。只要主键能够保持唯一性，即可满足其主要作用——确保每一行的唯一标识。 数据库系统的设计并不以维持主键ID的连续性为目标，因为这种特性对查询性能、索引效率以及事务处理没有显著提升。因此，在生产环境中，一般不建议特意追求主键ID的连续性。                 

解决方案：

重新整理并更新id

-- 假设你的表名为 图书馆管理，id为主键列
-- 取消主键和自增属性
ALTER TABLE 图书馆管理 MODIFY id INT NOT NULL;
ALTER TABLE 图书馆管理 DROP PRIMARY KEY;

-- 重新生成连续ID（极端危险操作，可能造成数据混乱）
SET @i = 0;
UPDATE 图书馆管理 SET id = (@i := @i + 1);

-- 重新设置主键和自增
ALTER TABLE 图书馆管理 ADD PRIMARY KEY (id);
ALTER TABLE 图书馆管理 MODIFY id INT AUTO_INCREMENT;

这样过后就可以了~