如何重排MySQL表service_logs的主键log_id以消除数值间隙？

重要提示：以下操作仅为满足特殊离线导出需求，生产环境严禁修改主键字段值，否则会引发关联数据失效、业务逻辑错误等不可预估的问题

操作前必读 #

修改主键前必须完整备份service_logs表全量数据，确认该表没有和其他表建立外键关联，否则修改后所有关联关系会直接失效。如果只是为了获取连续ID给Excel使用，建议优先使用文末的无改动导出方案，完全不需要修改原表数据，风险最低。

操作步骤 #

方法1：临时表迁移（最稳妥，不会破坏原表结构） #

• 创建和原表结构完全一致的临时表

CREATE TABLE service_logs_temp LIKE service_logs;

• 按原log_id排序生成连续ID，写入临时表，注意把下面的「其他字段」替换为你表中除了log_id之外的真实字段名

INSERT INTO service_logs_temp (log_id, 其他字段1, 其他字段2, ...)
SELECT 
  ROW_NUMBER() OVER (ORDER BY log_id ASC) AS new_log_id,
  其他字段1,
  其他字段2,
  ...
FROM service_logs;

• 验证临时表数据和ID连续性无误后，替换原表

-- 备份原表
RENAME TABLE service_logs TO service_logs_old;
-- 临时表替换为正式表
RENAME TABLE service_logs_temp TO service_logs;
-- 恢复log_id的自增属性，设置自增起始值为当前最大ID+1
ALTER TABLE service_logs MODIFY COLUMN log_id INT AUTO_INCREMENT;
-- 执行下面语句拿到最大ID+1的值，替换后续语句中的X
SELECT MAX(log_id) + 1 FROM service_logs;
ALTER TABLE service_logs AUTO_INCREMENT = X;

方法2：直接更新原表（仅适合小表，风险略高） #

• 用变量生成连续ID直接更新原表log_id

SET @new_id = 0;
UPDATE service_logs SET log_id = (@new_id := @new_id + 1) ORDER BY log_id ASC;
-- 更新完成后重置自增起始值
ALTER TABLE service_logs AUTO_INCREMENT = @new_id + 1;

无改动导出连续ID方案（推荐） #

不需要修改原表任何数据，直接在导出查询语句中生成连续ID，导出结果即可直接给Excel使用：

• MySQL 8.0及以上版本

SELECT ROW_NUMBER() OVER (ORDER BY log_id ASC) AS continuous_id, * FROM service_logs;

• MySQL 5.x版本（不支持窗口函数）

SELECT (@id := @id +1) AS continuous_id, t.* FROM service_logs t, (SELECT @id := 0) AS init ORDER BY log_id ASC;

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