mysql主键索引int,重新索引MySQL INT主键和重设AUTO_INCREMENT

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

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

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

The desired output would be:

Table: users

id | name

-----------

1| Bob

2| Jen

3| Kyle

4| John

And ALTER TABLE users AUTO_INCREMENT = 5;

(Notice Kyle and John)

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

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

解决方案

remove index on id

do something like this:

SET @rank:=0;

update users

set id=@rank:=@rank+1

order by id;

add index on id