如何使用PHP为MySQL表中的每行重新分配AUTO_INCREMENT列

Question

我有一个图片库，网站成员可以上传图片。当一张图片被上传时，会写入一个MySQL行，其中包含有关该图片、会员等的各种信息。此行使用AUTO_INCREMENT创建其ID，以便getimage.php?id=XX可以获取要显示的图片。

我使用for循环遍历这些ID，以在画廊中显示图像。

例如，如果我删除第5行/图像，则AUTO_INCREMENT从123456789变为12346789。

我想重新为MySQL表中的每一行分配ID，从下往上开始。因此，12346789变为12345678。我该如何实现这一点？

- Drew

---

这完全是处理已删除图片的错误方式。只需选择所需的行并循环遍历即可。 - Sam Dufel

你为什么想要这样做呢？这也意味着你必须更新所有链接到这些图片，以确保你仍然得到正确的图片。 - Arjan

据我所知，如果您正在使用自动递增，无论如何都无法这样做。 - Matt

@Sam Dufel，比如说我只想选择第1到20行并循环它们，我该怎么做？ - Drew

由于我没有使用这些自增ID的另一个表，我发现以下操作也很有帮助：'ALTER TABLE mytable DROP id; ALTER TABLE upload ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1; ' - Drew

Answer 1

我发现这个方法完美地工作，并且非常快速，因此在此分享：

ALTER TABLE tablename DROP id

ALTER TABLE tablename ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1

我知道这不是正确的方法，但对于我的具体情况来说，这正是所需要的。我使用的表格中没有任何与另一个表相关联的内容。

- Drew

---

这个解决方案真是太棒了，简单明了！能否讲解一下它为什么有效以及如何实现的呢？FIRST是什么意思？哦，我建议将INT改为INT UNSIGNED... - Domi

完美的例子是“有时候最简单的解决方案是最好的，以至于很难相信”。谢谢@Drew :D - Mihir Bhende

@Domi FIRST 将该列放置在表的第一位置。 - mikl

Answer 2

如果你希望自增id始终连续，那就放弃吧，这没有什么用。

如果你只是想重新编号一次，那很简单：

创建一个与原表结构相同的新表：

CREATE TABLE mytable2 LIKE oldtable;

将所有行复制到新表中，记得不要选择auto_increment的id，否则旧编号会被保留。

INSERT INTO mytable2 (field2, field3, field4)
  SELECT field2, field3, field4 FROM oldtable ORDER BY oldtable.id;

RENAME oldtable archive;

RENAME mytable2 oldtable;

DROP archive;

现在您已经拥有连续编号。

- Johan

---

这是一个不错的方法。它比使用可能会向您的累积表添加其他活动的实时表的已接受解决方案更安全。无论哪种情况，如果有很多其他活动，将其包装在不可中断的事务中是明智的。对于此解决方案，RENAME oldtable archivedtable; 比 DROP 更好。一旦确定所有内容都正常工作，您可以将其删除。 - Mark Goldfain

Answer 3

使用用户自定义变量：

SET @id = 0;
UPDATE table SET id = @id := @id + 1 ORDER BY id;
SET @alt = CONCAT('ALTER TABLE table AUTO_INCREMENT = ', @id + 1);
PREPARE aifix FROM @alt;
EXECUTE aifix;
DEALLOCATE PREPARE aifix;

Example use 例子用法

• http://www.paulwhippconsulting.com.au/webdevelopment/31-renumbering-an-qorderingq-field-in-mysql
• http://www.it-iss.com/mysql/mysql-renumber-field-values/

示例用途

- Palec

Answer 4

正如其他人已经说明的那样，这种方法是错误的，但是如果您需要重新编号（例如对于排序列有效），则可以使用以下查询：

UPDATE tabaleName SET fieldName = fieldName - 1 WHERE fieldName > 5 ORDER BY fieldName ASC

而且，由于您正在使用自增，因此必须将其重置

ALTER TABLE tableName AUTO_INCREMENT=10000

但请注意，我仅出于教育目的发布此内容。这是错误的方法！在点击下一个/上一个时，不要在查询中提供精确的ID，而应该

SELECT * FROM tableName WHERE fieldName > _the_previous_id_ 
ORDER BY fieldName ASC LIMIT 1

或者更好的是，选择专辑中的所有记录并循环它们。

- Maxim Krizhanovsky

---

已经注意到了。我该如何循环遍历表中的记录？ - Drew

啊好，这应该行得通：SELECT * FROM tableName WHERE fieldName > the_previous_id ORDER BY fieldName ASC LIMIT 1。the_previous_id 是一个常量还是我要用我的变量 $id 替换它？ - Drew