如何删除指定记录并重新排列剩余记录的ID？

问题描述 #

我需要编写一段代码，实现删除选中的数据库记录后重新排列剩余记录的ID。例如：原ID序列为1-2-3-5-6，删除ID为2、3的记录后，剩余记录ID变为1-2-3。我尝试了以下代码，但当删除的记录数量超过2条时无法正常工作：

module.exports.deleteSelected = function(ids, callback) {
    for (let i = 0; i < ids.length; i++) {
        db.run(`DELETE FROM mean_t WHERE id = ?`, [ids[i]], function(err) {
            if (err) {
                return callback({ success: false, message: 'حدث خطأ أثناء عملية الحذف' });
            }

            if (i === ids.length - 1) {
                db.run(`UPDATE or IGNORE  mean_t SET id = id - 1 WHERE id > ?`, [ids[ids.length - 1]], function(err) {
                    if (err) {
                        return callback({ success: false, message: 'تم حذف السجلات، ولكن حدث خطأ أثناء تحديث الـ ID للسجلات المتبقية' });
                    }

                    return callback({ success: true, message: 'تم حذف السجلات بنجاح' });
                });
            }
        });
    }
};

问题分析 #

1. 异步执行顺序混乱：循环内的db.run是异步操作，循环会一次性发起所有删除请求，而非等待前一个删除完成再执行下一个。这会导致最后一步的ID更新可能在所有删除操作完成前触发，同时i的判断逻辑会因为循环提前结束而失效。
2. ID更新逻辑错误：当前仅给大于最后一个删除ID的记录减1，但如果删除多条记录（比如ID2、3），大于3的记录实际需要减2而非1，这会导致ID序列出现断层或重复。

解决方案 #

以下提供两种基于SQLite的修正方案，均使用事务保证操作的原子性（删除和更新要么全部成功，要么全部回滚）：

方案1：事务+窗口函数重新分配ID（适合SQLite 3.25+，支持窗口函数）

module.exports.deleteSelected = function(ids, callback) {
    // 开启事务
    db.run('BEGIN TRANSACTION', function(err) {
        if (err) {
            return callback({ success: false, message: 'حدث خطأ أثناء فتح المعاملة' });
        }

        // 第一步：批量删除选中记录
        const placeholders = ids.map(() => '?').join(',');
        db.run(`DELETE FROM mean_t WHERE id IN (${placeholders})`, ids, function(err) {
            if (err) {
                db.run('ROLLBACK', () => {});
                return callback({ success: false, message: 'حدث خطأ أثناء عملية الحذف' });
            }

            // 第二步：创建临时表，用ROW_NUMBER生成连续新ID
            db.run('CREATE TEMP TABLE temp_mean_t AS SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS new_id FROM mean_t', function(err) {
                if (err) {
                    db.run('ROLLBACK', () => {});
                    return callback({ success: false, message: 'حدث خطأ أثناء إنشاء الجدول المؤقت' });
                }

                // 第三步：清空原表
                db.run('DELETE FROM mean_t', function(err) {
                    if (err) {
                        db.run('ROLLBACK', () => {});
                        return callback({ success: false, message: 'حدث خطأ أثناء مسح الجدول الأصلي' });
                    }

                    // 第四步：将临时表数据导回原表，替换为新ID
                    // 注意：替换下面的字段列表为你的表实际字段
                    db.run(`INSERT INTO mean_t (id, col1, col2) SELECT new_id, col1, col2 FROM temp_mean_t`, function(err) {
                        if (err) {
                            db.run('ROLLBACK', () => {});
                            return callback({ success: false, message: 'حدث خطأ أثناء إعادة إدخال البيانات' });
                        }

                        // 第五步：清理临时表并提交事务
                        db.run('DROP TABLE temp_mean_t', function(err) {
                            if (err) {
                                db.run('ROLLBACK', () => {});
                                return callback({ success: false, message: 'حدث خطأ أثناء حذف الجدول المؤقت' });
                            }

                            db.run('COMMIT', function(err) {
                                if (err) {
                                    return callback({ success: false, message: 'حدث خطأ أثناء تأكيد المعاملة' });
                                }
                                return callback({ success: true, message: 'تم حذف السجلات وتحديث الـ ID بنجاح' });
                            });
                        });
                    });
                });
            });
        });
    });
};

方案2：事务+分段偏移更新（适合不支持窗口函数的旧版SQLite）

module.exports.deleteSelected = function(ids, callback) {
    // 先对删除ID排序，保证更新顺序正确
    const sortedIds = [...ids].sort((a, b) => a - b);
    const deleteCount = sortedIds.length;

    db.run('BEGIN TRANSACTION', function(err) {
        if (err) {
            return callback({ success: false, message: 'حدث خطأ أثناء فتح المعاملة' });
        }

        // 批量删除选中记录
        const placeholders = sortedIds.map(() => '?').join(',');
        db.run(`DELETE FROM mean_t WHERE id IN (${placeholders})`, sortedIds, function(err) {
            if (err) {
                db.run('ROLLBACK', () => {});
                return callback({ success: false, message: 'حدث خطأ أثناء عملية الحذف' });
            }

            // 分段更新ID：每删除一个ID，大于它的记录减1
            let step = 0;
            function updateNext() {
                if (step >= deleteCount) {
                    // 所有更新完成，提交事务
                    db.run('COMMIT', function(err) {
                        if (err) {
                            return callback({ success: false, message: 'حدث خطأ أثناء تأكيد المعاملة' });
                        }
                        return callback({ success: true, message: 'تم حذف السجلات وتحديث الـ ID بنجاح' });
                    });
                    return;
                }

                const currentId = sortedIds[step];
                db.run(`UPDATE mean_t SET id = id - 1 WHERE id > ?`, [currentId], function(err) {
                    if (err) {
                        db.run('ROLLBACK', () => {});
                        return callback({ success: false, message: 'حدث خطأ أثناء تحديث الـ ID' });
                    }
                    step++;
                    updateNext();
                });
            }

            updateNext();
        });
    });
};

注意事项 #

• 如果你的表存在外键关联，修改ID会导致外键失效，这种场景下不建议调整ID，建议使用逻辑删除（添加is_deleted字段）替代物理删除。
• 事务的作用是避免删除成功但ID更新失败的情况，保证数据一致性。

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