如何更改行删除时的自动增量主键值？
我有一个问题，每当我删除一行时，与该行对应的行 ID 就会被删除，但我不希望这样。我想要的是，如果删除任何行，则该行之后的其他行应向上移动一个（删除的行数）位置。
示例：
假设有一个用户表（id 和 name）
id(auto incremented primary key) name
1 xyz
2 aaa
3 ray
4 mark
5 allen
现在删除带有 id=3 的行，并且表应该如下所示
id(auto incremented primary key) name
1 xyz
2 aaa
3 mark
4 allen
有什么方法可以完成此操作吗？
I have a problem that whenever I delete a row, the row ID corresponding to that row gets deleted, but I don't want this. What I want is if any row is deleted, then other rows after that row should shift one (the no. of rows deleted) position up.
Example:
Suppose there is a user table(id and name)
id(auto incremented primary key) name
1 xyz
2 aaa
3 ray
4 mark
5 allen
now delete row with id=3 and table should look like
id(auto incremented primary key) name
1 xyz
2 aaa
3 mark
4 allen
Is there any way to accomplish this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不！不要这样做！
您的自动增量 ID 是行的IDENTITY。其他表使用此 ID 来引用特定行。如果更新 ID，则必须更新引用该行的所有其他表，这根本不是关系数据库的重点。
此外，永远不需要这样做：您不会很快用完自动增量列（如果您这样做，只需选择更大的数据类型）。
自动增量 ID 是一个纯粹的技术数字，您的应用程序用户永远不应该看到或使用它。如果您想向用户显示标识符，请添加另一列！
No! Don't do this!
Your Autoincrement ID is the IDENTITY of a row. Other tables use this ID to refer to a certain row. If you update the ID, you would have to update all other tables referencing this row, which is not at all the point of a relational database.
Furthermore, there never is a need to do this: you won't run out of autoincrement columns fast (and if you do, just pick a bigger datatype).
An autoincrement ID is a purely technical number, your application users should never see or use it. If you want to display an identificator to your users, add another column!
你完全搞错了方向。不应更改自动编号，因为这会破坏任何其他引用表之间的链接。
听起来，您想要的是行计数器，而不是主键。
You've completely got the wrong end of the stick. Auto numbers should not be changed as this would break the link between any other referencing tables.
What you want, by the sounds of it, is a row counter, not a primary key.
虽然通常不建议更改这些值，但确实存在您可能需要更改它们的情况。如果您有适当的外键关系设置来级联更新，那么您可以执行此操作。当然，您需要 100% 所有 FK 关系都按预期定义。
While its generally not recommended to change these values, there do exists instances where you may need to change them. If you have the appropriate Foreign Key relationships setup to cascade on UPDATE then you could do this. Granted you need to be 100% all FK relationships are defined as expected.