How to recalculate the primary index?

Question

I have a table in mysql database with auto increments PRIMARY KEY. On a regular basis, rows in this table are deleted by appending. Thus, the PK result of the last row grows very fast, but there are not many rows in this table.

What I want to do is “recount” PK so that the first line has PK = 1, the second line PK = 2, and so on. There are no external dependencies on PK of this table, therefore it will be "safe".

Can this be done using only mysql queries / tools? Or should I do this from my code?

Answer 1

set @pk:=0;

update 
  your_table
  set pk=@pk:=@pk+1
  order by pk;       <-- order by original pk

-, . , . , , unsigned.

Answer 2

AUTO_INCREMENT :

mysql> SELECT @pk;
+------+
| @pk  |
+------+
| 9911 |
+------+
1 row in set (0.00 sec)


ALTER TABLE your_table AUTO_INCREMENT = 9912;

Answer 3

This should solve your problem: http://www.liewcf.com/mysql-reset-auto-increament-number-115/ In short: you can use ALTER TABLE table_tablename AUTO_INCREMENT = 1. To reset it to 1, you must first delete records from the table or change their identifier to the minimum value.