如何实现插入新行时按删除顺序复用已删除的CommentNo

核心前提：现有表结构只有IsDeleted软删除标记，无法记录删除动作的先后顺序，必须先补充字段记录删除时间，才能实现「按删除先后顺序复用CommentNo」的需求。

步骤1：调整表结构，新增删除时间字段 #

执行以下SQL给评论表加删除时间戳字段，未删除的记录该字段为NULL：

ALTER TABLE 你的评论表名 
ADD COLUMN DeletedAt DATETIME NULL DEFAULT NULL COMMENT '记录删除时间，未删除则为NULL';

后续所有软删除操作，必须同步给该字段赋值当前时间，不要只修改IsDeleted状态，示例软删除SQL：

UPDATE 你的评论表名 
SET IsDeleted = 1, DeletedAt = NOW() 
WHERE CommentID = [待删除的记录ID];

按照给出的测试场景，先删CommentNo=1、再删CommentNo=2，那么这两条记录的DeletedAt字段会自动记录先后时间差，后续排序取号就有依据。

步骤2：编写插入新记录时的取号逻辑 #

插入新行前，先查询当前应该使用的CommentNo，逻辑优先级为：

1. 优先取最早被删除的记录对应的CommentNo（按DeletedAt升序排列取第一条）
2. 没有可复用的已删除编号时，取当前所有有效记录（IsDeleted=0）的最大CommentNo+1，空表默认从1开始

取号SQL如下：

SELECT COALESCE(
    -- 取最早删除的可复用编号
    (SELECT CommentNo FROM 你的评论表名 WHERE IsDeleted = 1 ORDER BY DeletedAt ASC LIMIT 1),
    -- 无复用编号时生成新的自增编号
    (SELECT IFNULL(MAX(CommentNo), 0) + 1 FROM 你的评论表名 WHERE IsDeleted = 0)
) AS target_comment_no;

步骤3：事务包裹完整插入流程，避免编号重复 #

为了防止并发场景下多个请求拿到同一个CommentNo，需要把「取号、清理已复用的旧删除记录、插入新行」三个操作放在同一个数据库事务中执行，流程如下：

• 开启事务
• 执行上面的取号SQL，拿到target_comment_no
• 如果target_comment_no是来自已删除的复用编号，执行物理删除对应旧记录的SQL，避免下次重复取到同一个编号：

  DELETE FROM 你的评论表名 WHERE IsDeleted = 1 AND CommentNo = [拿到的target_comment_no] LIMIT 1;
  

• 执行新记录插入操作，CommentNo字段填入拿到的target_comment_no
• 提交事务

效果验证 #

针对给出的测试数据：

1. 第一次插入新记录时，取到的最早删除编号是1，插入后新记录CommentNo为1，原CommentID=1的已删除记录被清理
2. 第二次插入新记录时，取到的最早删除编号是2，插入后新记录CommentNo为2，原CommentID=2的已删除记录被清理
3. 后续再插入新记录时，没有可复用的已删除编号，会依次生成5、6、7...的新CommentNo，符合预期。

注意事项 #

• 不要通过CommentNo的数值大小判断删除先后，必须以DeletedAt时间为准，避免出现先删大编号、后删小编号时取号顺序错误的问题
• 并发量高的场景下，可以给CommentNo加唯一索引，从数据库层面兜底防止编号重复。

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