更改表中一行的主键 (id) 并将其他行向下移动 [英] Change primary key (id) of a row in a table and shift the others downwards

问题描述

我有一个像这样的 MySQL 表:

I have a MySQL table that looks like this:

我想更改为 id 为 15 (women,dress) 的条目现在的主键为 8，然后其他条目移动 1，因此例如 Kids Tops 现在将是 9 等.是否有通过 phpmyadmin 或 SQL 查询执行此操作的简单方法是什么?也因为 id 15 已经被用作其他地方的外键，我希望这个更改在其他表的所有地方都反映出来.

I wanted to change such that the entry with id 15 (women, dress) now has a primary key of 8 and then the others are shifted by one, so for example Kids Tops now will be 9, etc. Is there an easy way to do this via phpmyadmin or a SQL query? Also because id 15 is already being used as a foreign key somewhere else, I wanted this change to be reflected all over the place in other tables.

推荐答案

永远不要更改主键.此外，将 PK 视为非数字值可能是有益的.假设您使用自动生成的 GUID 作为主键.

One should not change the primary key - ever. Moreover, it may be beneficial to think about PKs as non-numeric values. Imagine that you use autogenerated GUIDs for your primary keys.

如果您想对您的项目重新编号，那么您要更改的列应该是一个单独的数字列，您可以明确地将其视为排序的序列号.然后你可以用三个 UPDATE 语句做你想做的事:

If you want to renumber your items, then the column that you want to change should be a separate numeric column, which you treat explicitly as a sequence number of sorts. Then you can do what you want with three UPDATE statements:

update mytable set sequence = -sequence where sequence = 15 -- hide the original 15
update mytable set sequence = sequence+1 where sequence >= 8
update mytable set sequence = 8 where sequence = -15

这篇关于更改表中一行的主键 (id) 并将其他行向下移动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！