MySQL 更新 AUTO_INCRMENT pk 以保留行排序

Question

我有一个包含三列的表格:id、foreign_id 和 tag。对该表的查询首先按 foreign_id 排序，然后按 tag 排序，但我们希望弃用 tag 列，转而使用更可靠且更可靠的列。自动生成的id。为此，我们还需要保留存储在 tag 列中的排序数据，而不保留 tag 。此排序仅在 foreign_id 列的范围内才有意义。

为了解决这个问题，我们决定更新每个 foreign_id 范围内的 id，以便 id 的顺序保留 标记订单信息。

如何更新 AUTO_INCRMENT 主键列，以便在不更改行的其余部分的情况下为其分配计数器中的下一个值？

或者，如何将整行(减去 pk)复制到新行并删除旧行？

Answer 1

SELECT MAX(id) INTO @maxID FROM the_table;
UPDATE the_table SET id = id + @maxID;
SET @i := 0;
UPDATE the_table SET id = (@i := @i + 1) ORDER BY foreign_id, tag;

我并不 100% 肯定这会起作用；我通常不会用 UPDATE 语句做这种事情。或者，您可以将最后一次更新替换为:

INSERT INTO the_table(id, foreign_id, tag) 
SELECT (@i := @i + 1) AS `new_id`, foreign_id, tag 
FROM the_table 
ORDER BY foreign_id, tag
;
DELETE FROM the_table 
WHERE id >= @maxID
;

无论哪种情况，都假设当前 id 值始终 >= 0。