How to renumber primary index

I have got a simple MySQL table and primary index (id) is not numbered one by one 开发者_Python百科(1, 31, 35, 100 etc.). I want them to be numbered like (1, 2, 3, 4). Please tell me how to do it. I would also like to point that I am aware of possible consequences of the operation, but I just want to tidy up the table.

I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::

SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);

give a try to renumber method of dalmp (DALMP Database Abstraction Layer for MySQL using PHP.)

$db->renumber('table','uid');

basically it does this:

SET @var_name = 0;
UPDATE Tablename SET ID = (@var_name := @var_name +1);
ALTER TABLE tablename AUTO_INCREMENT = 1

The easiest Solution is:

• Rename the table to tablename_temp
• Create a new table with the old name and the same structure
• INSERT INTO tablename (id, field1, field2, field3) SELECT NULL, field1, field2, field3, ... FROM tablename_temp;
• DROP tablename_temp

This will destroy all of your data if you are using foreign keys and i strongly suggest you leave the ids as they are. A Database is not untidy because the primary keys are not in sequence, a Database is untidy if it's referential integrity (id values pointing to the wrong or non-existant row in other tables) is broken.

CREATE TABLE newtable 
  LIKE oldtable 
  SELECT NULL, col1, col2, col3, etc FROM oldtable;

Without including the primary key column in the select, but all the other columns.

And the primary key column should be first in the table, or you'll need to set the NULL to the position of the primary key column. And the column should have a "NOT NULL" setting. Otherwise it will just be set as null, and that's kind of pointless.

The reason this works is because MySQL likes to ignore NULL inserts on auto_increments, and instead create a new number for it (provided that null isn't a valid value on that column).

1. Delete the "id" column.
2. Create column named id with auto increment on(and do not allow nulls), it will have the new values you want.
3. Assign this new "id" column as PK.

edited later thanks to comments.

You don't want to renumber it. Primary key is not a number. It's just a black box unique identifier, that only resembles a continuous sequence. In reality you'll never have it intact, so you shouldn't bother making it "nice". While the only purpose of a primary key is to stick with database row forever, making it possible to reference rows from other tables.

Any prettifying such as adding cute numbers should be added with output only. Especially given the table may be ordered in thousands different ways.

Wanting to renumber the primary key is a typical newbie mistake, and it's a great pity that Stack Overflow endorses it with all those servile answers.

You have to define the auto-increment on your primary key (if it is a primary key)

Here's a link:

sql autoincrement

dump to datafile and reimport might work somehow

mysqldump db table |sed s/// > outfile.sql

then reimport