'How to renumber primary index

I have got a simple MySQL table and primary index (id) is not numbered one by one (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.

sqlmysql


Solution 1:[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

Solution 2:[2]

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.

Solution 3:[3]

  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.

Solution 4:[4]

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).

Solution 5:[5]

You don't want it. Primary key is not a number. It's just meaningless unique sequence.

Solution 6:[6]

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

Here's a link:

sql autoincrement

Solution 7:[7]

dump to datafile and reimport might work somehow

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

then reimport

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nbari
Solution 2 Morfildur
Solution 3
Solution 4 Tor Valamo
Solution 5 Your Common Sense
Solution 6 theSpyCry
Solution 7 Suraj Rao

Related Questions