更改表中一行的主键(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(女性，服饰)的条目现在具有主键8，然后将其他主键移动一个，例如，现在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屋！