如果自动增量值达到极限，该怎么办? [英] What to do if the auto-increment value reaches its limit?

问题描述

我正在做一些可能会出现的问题的研究. 假设您有一个带有ID和名称字段的InnoDB MySQL表. id字段具有BIGINT(20)，并且为AUTO_INCREMENT加上其主键.

I am doing a little research for a problem that might occur someday. Lets say you have an InnoDB MySQL table with an id and a name field. the id field has BIGINT(20) and is AUTO_INCREMENT plus its the primary key.

在此表已满的情况下该怎么办，这意味着我们已达到ID的限制，并且无法再生成自动递增编号.

What do you do in a case that this table is full which means we have reached the limit on the id and no auto increment number can be generated anymore.

推荐答案

让我们假设一个表结构如下:

Let's assume a table structure like:

CREATE TABLE `tbl` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

和INSERT查询，例如:

INSERT INTO tbl(id) VALUES (NULL);

在实际代码中，表中还有其他列，它们也出现在INSERT查询中，但是我们可以放心地忽略它们，因为它们不会给此特定问题带来任何价值.

In the real code there are also other columns in the table and they are also present in the INSERT query but we can safely ignore them because they don't bring any value to this specific issue.

当列id的值达到最大值时，无法使用上述查询在表中插入更多行.下一个INSERT失败，并显示以下错误:

When the value of column id reaches its maximum value no more rows can be inserted in the table using the query above. The next INSERT fails with the error:

SQL错误(167):列'id'的值超出范围.

SQL Error (167): Out of range value for column 'id'.

如果id列的值之间存在间隙，那么您仍然可以插入使用表中不存在的值的行，但必须在INSERT查询中指定id的值.

If there are gaps in the values of the id column then you can still insert rows that use values not present in the table but you have to specify the values for id in the INSERT query.

无论如何，如果AUTO_INCREMENT列的类型为BIGINT，则不必担心.

Anyway, if the type of your AUTO_INCREMENT column is BIGINT you don't have to worry.

假设代码每秒插入一百万条记录(这被高估了，要说不可能)，那么id列的值就足够用于下一个

Assuming the code inserts one million records each second (this is highly overrated, to not say impossible), there are enough values for the id column for the next half of million years. Or just 292,277 years if the column is not UNSIGNED.

我目睹了实时Web服务器上的行为，该服务器使用INT(11)(而不是UNSIGNED)作为记录表中有关网站访问信息的表的AUTO_INCREMENT ed PK.经过数年的平稳运行后，当访问人数达到2^31(数十亿美元左右)时，它在深夜失败了.

I witnessed the behaviour on a live web server that was using INT(11) (and not UNSIGNED) as the AUTO_INCREMENTed PK for a table that records information about the visits of the web site. It failed in the middle of the night, after several years of running smoothly, when the visits number reached 2^31 (2 billions and something).

将列类型从INT更改为BIGINT并不是解决20亿条记录表的解决方案(需要很长时间才能完成，并且当系统处于运行状态时，永远没有足够的时间).解决方案是创建一个具有相同结构的新表，但PK列的BIGINT和AUTO_INCREMENT列的初始值，然后切换表:

Changing the column type from INT to BIGINT is not a solution on a 2-billion records table (it takes ages to complete and when the system is live, there is never enough time). The solution was to create a new table with the same structure but with BIGINT for the PK column and an initial value for the AUTO_INCREMENT column and then switch the tables:

CREATE TABLE `tbl_new` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) AUTO_INCREMENT=2200000000;

RENAME TABLE `tbl` TO `tbl_old`, `tbl_new` TO `tbl`;

这篇关于如果自动增量值达到极限，该怎么办?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！