Change mysql auto increment id column value

I have a mysql table with an gpid AUTO_INCREMENT NOT NULL UNIQUE column.

After filling this table (which has 50M+ entries), I have realized that mysql still increments AUTO_INCREMENT columns if transaction fails because of an IntegrityError, and understandably so.

The results are gaps in AUTO_INCREMENT columns, with gpid jumping values (for exemple from gpid == 3 to gpid == 5 between two consecutive rows). While this is not an issue for machines, it is one for my coworkers and I. The purpose of this column was, among other things, to represent the exact number of rows in the table.

Is there a way to change all gpid column values to a nice range (from 1 to 53926669), respecting the order the rows were inserted in the table, without having to drop/refill the table? Refilling would take me a long time.

Thank you.

Answer

MySQL, just like other databases, does not guarantee the absence of gaps in auto-increment column. Renumbering the table may fix the issue at a particular point in time, but gaps may (and most likely) will appear in the future.

If you want a monotically increasing sequence, then auto-increment is not what you need. Instead, you can for example create a view, and use row_number():

create view myview as
select t.*, row_number() over(order by gpid) as new_gpid
from mytable t