How to recalculate primary index?

Question

I have table in mysql database with autoincrement PRIMARY KEY. On regular basis rows in this table are being deleted an added. So the result is that value of PK of the latest row is growing very fast, but there is not so much rows in this table.

What I want to do is to "recalculate" PK in this way, that the first row has PK = 1, second PK = 2 and so on. There are no external dependencies on PK of this table so it would be "safe".

Is there anyway it can be done using only mysql queries/tools? Or I have to do it from my code?

Answer 1

set @pk:=0;

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

In my opinion, having a big surrogate key is fine. You probably unlikely to use up all the max allowed integer. Consider you can double it up using unsigned.