how to repopulate/recreate autoincrement in mysql

Question

I want to know if I can repopulate the autoincrement value in mysql.

Because, I have records that look similar:

ID Name
1  POP
3  OLO
12 lku

Basically , I want a way to update the ID to this

ID Name
1  POP
2  OLO
3  lku

Is there any way to do this in mysql?

Thanks.

Answer 1

It's not best practice to fiddle your primary keys - better to let your DB handle it itself. There can be issues if, in between the UPDATE and ALTER, another record is added. Because of this, you must LOCK the table, which might hang other queries and spike load on a busy production server.

LOCK TABLES table WRITE
UPDATE table SET id=3 WHERE id=12;
ALTER TABLE table AUTO_INCREMENT=4;
UNLOCK TABLES 

OR - for thousands of rows (with no foriegn key dependencies):

CREATE TEMPORARY TABLE nameTemp( name varchar(128) not null )
INSERT INTO name SELECT name FROM firstTable
TRUNCATE firstTable
INSERT INTO firstTable SELECT name FROM nameTemp

The latter method will only work where you have no foreign keys. If you do, you'll require a lookup table.

CREATE TEMPORARY TABLE lookup( newId INTEGER AUTO_INCREMENT, oldId INTEGER, PRIMARY KEY newId( newId ) );
INSERT INTO lookup (oldId) SELECT id FROM firstTable
[do temp table queries above]

You now have a lookup table with the old to new ids which you can use to update the foreign key dependencies on your other tables (on a test server!)

Answer 2

Changing the primary key is a very bad idea as it endangers your referential integrity (what if another table uses the id without having a foreign key with proper "on change"?).

If you really, really have to do it and don't care about bad side-effects:

• Create a second table with identical structure
• INSERT INTO new_table (id, [other fields]) SELECT NULL, [other fields] FROM old_table;
• DROP old_table;
• RENAME new_table old_table;

Warning:
This will damage every other table that has foreign keys on this table (but if you had such then you wouldn't be doing this anyways).

Answer 3

You may want to try something like...

Create Temporary table MyBackup
(  ID as your autoincrement,
   OldID as Int for backlinking/retention,
   RestOfFields as their type )

insert into MyBackup
(    OldID
     RestOfFields )
select 
     ID as OldID,
     RestOfFields
  from
     YourOriginalTable
  order by
     ID  (this is your original ID)

Then you'll have a new table with an autoincrement with new IDs assigned, yet have a full copy of their original ID. Then, you can do correlated updates against other tables and set the ID = ID where ID = OldID. By keeping your insert via order by the original ID, it will keep the numbers from replacing out of sequence.. Ex: if your table was orderd as Old ID = 3, new ID = 1 Old ID = 1, new ID = 3 Old ID = 12, new ID = 2

Your old 3's will become 1's, then the 1's would become 3's, and 12's become 2's

Old ID = 1, new ID = 1 Old ID = 3, new ID = 2 Old ID = 12, new ID = 3

your 3's won't overwrite the higher number, and the 12's won't conflict with the 3's since the threes were already lowered to 2's.