重新排列主键 [英] Rearranging primary keys

问题描述

我有一个 PHP 页面，它与这个具有简单结构的小型 MySQL 数据库表进行交互.ID、Name、Address 是字段.ID 是主键.

I have a PHP page which interacts with this small MySQL database table with a simple structure. ID, Name, Address are the fields. ID is the primary key.

+------+------------+-------------+
|  ID  |   Name     |   Country   |
|______|____________|_____________|
| E001 |  Stephen   |   America   |
| E002 |  John      |   Britain   |
| E003 |  Kate      |   Canada    |
| E004 |  Carlos    |   Spain     |
| E005 |  James     |   Australia |
|______|____________|_____________|

如果我删除 E003 处的记录，它看起来像这样.

If I remove the record at E003, it looks like this.

+------+------------+-------------+
|  ID  |   Name     |   Country   |
|______|____________|_____________|
| E001 |  Stephen   |   America   |
| E002 |  John      |   Britain   |
| E004 |  Carlos    |   Spain     |
| E005 |  James     |   Australia |
|______|____________|_____________|

E002 和 E004 之间出现间隙.

A gap appears between E002 and E004.

有没有办法填补这个空白?像这样

Is there a way to fill in that gap? Like this

+------+------------+-------------+
|  ID  |   Name     |   Country   |
|______|____________|_____________|
| E001 |  Stephen   |   America   |
| E002 |  John      |   Britain   |
| E003 |  Carlos    |   Spain     |
| E004 |  James     |   Australia |
|______|____________|_____________|

具有E004的较早记录，落入E003的位置.以前的 E005 获得编号 E004，依此类推.就像记录退一步.

Earlier record which had E004, fall into the place of E003. Former E005 gets the number E004 and so on. Like the records take a step back.

有没有办法以编程方式做到这一点?

Is there a way to do this programmatically?

推荐答案

您是否需要重新编号?关键值不应该真正重要.如果他们这样做(即，它们用于外部目的)，请考虑将外部键作为表中的常规列，并让主键作为自增整数字段.即使您这样做，您仍然可能不应该重新编号键.这意味着所有外部依赖项也需要更新.这样做没有多大价值.

Is there a reason you need to renumber them? The key values shouldn't really matter. If they do (i.e., they are used for outside purposes), consider having the external key be a regular column in the table and let the primary key be an auto-increment integer field. Even if you do this, you still probably shouldn't be renumbering keys. That would mean that all external dependencies would need to be updated as well. There's just not much value in doing it.

但是，如果你必须……

如果键是自动递增的(应该是)，你可以这样做:

If the key is auto-increment (which it should be), you can do this:

ALTER TABLE MyTable DROP ID;
ALTER TABLE MyTable ADD COLUMN ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

如果不是，那么您可以做几件事，但最简单的方法可能是使用变量作为计数器.为方便起见，请不要使用字符串主键，而应使用纯数字主键.

If it's not, then you can do several things, but the easiest might be to use variables to act as a counter. To make this easier, please don't use a string primary key, but instead use a plain numeric primary key.

SET @id = 0;

UPDATE MyTable
SET ID = (@id := @id + 1)
ORDER BY ID;

这篇关于重新排列主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！