MySQL中删除行后如何自动重排SQL表的自增ID？

你提供的代码是PostgreSQL的PL/pgSQL语法，MySQL不支持这种DO $$块结构，下面是适配MySQL的修改方案，两种实现方式供你选择：

方式一：使用存储过程（适合复杂表结构） #

DELIMITER //

CREATE PROCEDURE rearrange_table_id()
BEGIN
    DECLARE new_id INT DEFAULT 1;
    DECLARE done INT DEFAULT FALSE;
    -- 请根据你的表结构，声明所有需要迁移的列变量（示例仅做参考）
    DECLARE var_column1 VARCHAR(255);
    DECLARE var_column2 INT;
    DECLARE var_column3 DATETIME;

    -- 声明游标，按原ID顺序读取数据
    DECLARE data_cursor CURSOR FOR SELECT column1, column2, column3 FROM your_table ORDER BY id;
    -- 游标遍历完成时触发的处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 清空临时表旧数据
    TRUNCATE TABLE temp_table;

    OPEN data_cursor;
    read_loop: LOOP
        FETCH data_cursor INTO var_column1, var_column2, var_column3;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 插入临时表并赋值连续ID
        INSERT INTO temp_table (id, column1, column2, column3) 
        VALUES (new_id, var_column1, var_column2, var_column3);
        SET new_id = new_id + 1;
    END LOOP;
    CLOSE data_cursor;

    -- 替换原表数据：先清空原表，再从临时表导回
    TRUNCATE TABLE your_table;
    INSERT INTO your_table (id, column1, column2, column3) 
    SELECT id, column1, column2, column3 FROM temp_table;

    -- 重置自增序列，确保后续插入从连续ID开始
    ALTER TABLE your_table AUTO_INCREMENT = 1;
END //

DELIMITER ;

-- 执行存储过程
CALL rearrange_table_id();

方式二：使用用户变量（简洁高效，适合简单表结构） #

这种方式不需要游标，直接通过查询生成连续ID，效率更高：

-- 清空临时表
TRUNCATE TABLE temp_table;

-- 生成连续ID并插入临时表
SET @new_id = 0;
INSERT INTO temp_table (id, column1, column2, ...)
SELECT (@new_id := @new_id + 1) AS new_id, column1, column2, ...
FROM your_table ORDER BY id;

-- 覆盖原表数据
TRUNCATE TABLE your_table;
INSERT INTO your_table (id, column1, column2, ...)
SELECT id, column1, column2, ... FROM temp_table;

-- 重置自增序列
ALTER TABLE your_table AUTO_INCREMENT = 1;

重要注意事项 #

• 操作前务必备份数据：TRUNCATE和数据覆盖操作不可逆，一旦出错会丢失数据
• 外键关联问题：如果你的表被其他表通过外键引用，重排ID会导致外键失效，需要先执行SET FOREIGN_KEY_CHECKS = 0;禁用外键约束，操作完成后再执行SET FOREIGN_KEY_CHECKS = 1;恢复
• 业务影响：重排ID会破坏原有ID的业务关联（比如用户收藏、订单关联等依赖原ID的场景），非必要不建议这么做
• 大表性能：如果表数据量很大，操作会占用较多资源，建议在业务低峰期执行

内容的提问来源于stack exchange，提问作者Muhammad Rizwan 067