How to re-populate / recreate auto-increment in mysql

Question

I want to know if I can refill auto-increment value in mysql.

Because I have entries similar to:

ID Name
1  POP
3  OLO
12 lku

Basically, I want to update the identifier to this

ID Name
1  POP
2  OLO
3  lku

Is there a way to do this in mysql?

Thank.

Answer 1

This is not the best practice to focus your primary keys - it is better to let your database process it yourself. Problems may arise if another record is added between UPDATEand ALTER. Because of this, you must LOCKuse a table that could hang other requests 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 lines (without 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

, . , .

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]

lookup , ( !)

Answer 2

- , (, , " "?).

:

• INSERT INTO new_table (id, [ ]) SELECT NULL, [ ] FROM old_table;
• DROP old_table;
• RENAME new_table old_table;

:
, ( , ).

Answer 3

- ...

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)

, . ID = ID, ID = OldID. , . : ID = 3, ID = 1 ID = 1, ID = 3 ID = 12, ID = 2

3 1, 1 3, 12 2

= 1, ID = 1 ID = 3, ID = 2 ID = 12, ID = 3

3 , 12 3, 2.