SQL表删除行后如何调整identity自增ID列实现ID连续无跳号

首先明确：identity（自增）列的设计目的是生成唯一不重复的行标识，本身不保证连续性，删除行后出现跳号是其正常特性。如果强需求ID始终连续，可参考以下实现方案：

方案1：取消identity属性，自行维护ID生成逻辑 #

• 先删除原ID列的identity属性，修改为普通INT类型
• 删除目标行后，执行更新语句将所有大于被删除ID的行的ID统一减1，示例语句（假设表名为your_table，被删除行的ID为4）：

UPDATE your_table SET ID = ID - 1 WHERE ID > 4;

• 新增行时查询当前表最大ID加1作为新行的ID值，示例语句：

INSERT INTO your_table (RowName, ID) 
SELECT 'Row6', COALESCE(MAX(ID), 0) + 1 FROM your_table;

• 注意：高并发场景下需要将上述操作包裹在事务中，或加锁避免生成重复ID

方案2：保留自增列，新增专用连续序号列 #

如果不想改动原有自增主键的逻辑，可以单独新增一个serial_num字段作为业务场景使用的连续序号，删除行后仅同步更新该字段的数值即可，不影响自增主键的原有能力，稳定性更高。

方案3：用触发器实现自动更新（以MySQL为例） #

如果需要删除行后自动完成后续ID的更新，无需手动执行语句，可以创建AFTER DELETE触发器：

DELIMITER //
CREATE TRIGGER update_id_after_delete
AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
    UPDATE your_table SET ID = ID - 1 WHERE ID > OLD.ID;
END //
DELIMITER ;

• 注意：如果表数据量较大，删除操作会触发大量行更新，会明显拉低删除操作的性能，仅适合小数据量表使用。

内容的提问来源于stack exchange，提问作者hamed algazaly