如何在SQL/OLEDB表中移动行以填补缺失ID及增量值空缺

核心思路 #

先调整后续记录的recNum，再删除目标行，最后重新生成连续的ID，确保两个字段都无空缺。

操作步骤（以数据库操作为例） #

1. 定位目标图片的recNum #

先获取要删除图片对应的记录编号，比如示例中secondImage.png对应的recNum=2：

SELECT DISTINCT recNum FROM your_table WHERE image = 'secondImage.png';

2. 更新后续记录的recNum #

将所有recNum大于目标值的记录编号减1：

UPDATE your_table 
SET recNum = recNum - 1 
WHERE recNum > (SELECT DISTINCT recNum FROM your_table WHERE image = 'secondImage.png');

3. 删除目标图片的所有行 #

移除对应图片的冗余行：

DELETE FROM your_table WHERE image = 'secondImage.png';

4. 重新生成连续的ID #

删除后重置ID为连续序列（以MySQL 8.0+为例）：

UPDATE your_table t1
JOIN (
    SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS new_id
    FROM your_table
) t2 ON t1.ID = t2.ID
SET t1.ID = t2.new_id;

操作步骤（以Python Pandas处理内存数据为例） #

import pandas as pd

# 读取数据
df = pd.DataFrame([
    {"ID":1, "recNum":1, "image":"firstImage.png"},
    {"ID":2, "recNum":1, "image":"firstImage.png"},
    {"ID":3, "recNum":1, "image":"firstImage.png"},
    {"ID":4, "recNum":2, "image":"secondImage.png"},
    {"ID":5, "recNum":2, "image":"secondImage.png"},
    {"ID":6, "recNum":2, "image":"secondImage.png"},
    {"ID":7, "recNum":3, "image":"thirdImage.png"},
    {"ID":8, "recNum":3, "image":"thirdImage.png"},
    {"ID":9, "recNum":3, "image":"thirdImage.png"},
])

target_image = "secondImage.png"
# 获取目标recNum
target_recnum = df[df["image"] == target_image]["recNum"].iloc[0]

# 更新后续recNum并删除目标行
df = df[df["image"] != target_image]
df.loc[df["recNum"] > target_recnum, "recNum"] -= 1

# 重新生成连续ID
df["ID"] = range(1, len(df)+1)

# 输出结果
print(df)

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