更改MySQL自动增量ID列值 [英] Change mysql auto increment id column value

问题描述

我有一个带有 gpid AUTO_INCREMENT NOT NULL UNIQUE 列的mysql表.

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

填充此表(具有50M +条目)后，我已经意识到，如果由于IntegrityError导致事务失败，mysql仍会增加 AUTO_INCREMENT 列.

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.

结果是 AUTO_INCREMENT 列中的间隔，其中 gpid 跳转值(例如，从 gpid == 3 到 gpid == 5 在两个连续的行之间).尽管这对机器来说不是问题，但对我和我的同事来说都是一个问题.除其他外，此列的目的是代表表中确切的行数.

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.

是否有一种方法可以将所有 gpid 列的值更改为一个合适的范围(从1到53926669)，而不必考虑删除行或重新填充表的行插入顺序?加气会花费我很长时间.

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.

谢谢.

推荐答案

MySQL像其他数据库一样，不能保证自动增量列中没有空格.对表重新编号可能会在特定时间点解决问题，但将来可能(并且很可能)会出现空白.

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.

如果您想要单调递增的序列，则不需要自动递增.相反，您可以例如创建一个视图，并使用 row_number():

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

这篇关于更改MySQL自动增量ID列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！