重新索引MySQL INT主键和重设AUTO_INCREMENT [英] Re-Indexing MySQL INT Primary Keys & Reset AUTO_INCREMENT

问题描述

因此，我有一个与PHP站点一起使用的MySQL数据库.在数周的发展过程中，我得到了类似的东西:

So I have a MySQL database that I'm using with a PHP site. Over the course of weeks of development, I'm left with something like:

Table: users

id  | name
-----------
  12| Bob
  34| Jen
 155| John
 154| Kyle

除了这种情况会持续处理数百条记录，而且id会成千上万.

Except this goes on for hundreds of records and the ids are in the thousands.

我正在寻找一个脚本，我可以运行该脚本以将键重命名为其最低值(保留它们各自的行)，然后将AUTO_INCREMENT重置为下一个ID

I'm looking for a script I can run to re-name the keys to their lowest value (preserving their respective rows), and then reset the AUTO_INCREMENT to the next id

所需的输出将是:

Table: users

id  | name
-----------
   1| Bob
   2| Jen
   3| Kyle
   4| John

和ALTER TABLE users AUTO_INCREMENT = 5;

(注意Kyle和John)

我意识到我必须修复所有引用users.id的东西.

I realize I would have to fix anything referencing the users.id.

有人知道在MySQL或PHP脚本中执行此操作的方法吗?

Does anyone know a way to do this in MySQL or with a PHP script?

推荐答案

删除ID上的索引

做这样的事情:

SET @rank:=0;
update users
set id=@rank:=@rank+1
order by id;

在ID上添加索引

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