mysql - 如果自增值达到极限怎么办？

Question

我正在对有一天可能出现的问题进行一些研究。假设您有一个带有 id 和 name 字段的 InnoDB MySQL 表。 id 字段具有 BIGINT(20) 并且是 AUTO_INCRMENT 加上它的主键。

如果这个表已满，这意味着我们已经达到了 id 的限制，并且无法再生成自动递增编号，那么该怎么办？

Answer 1

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

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

和INSERT查询如下:

INSERT INTO tbl(id) VALUES (NULL);

在实际代码中，表中还有其他列，它们也存在于 INSERT 查询中，但我们可以安全地忽略它们，因为它们不会给这个特定问题带来任何值(value)。

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

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

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

<小时/>

无论如何，如果您的AUTO_INCRMENT列的类型是BIGINT，您不必担心。

假设代码每秒插入一百万条记录(这被高度高估了，但并不是说不可能)，id 列有足够的值用于下一个 half of million years 。或者，如果该列不是 UNSIGNED，则仅为 292,277 年。

<小时/>

我亲眼目睹了使用INT(11)(而不是UNSIGNED)作为AUTO_INCRMENTed PK的实时网络服务器上的行为获取记录有关网站访问信息的表。在平稳运行了几年之后，当访问量达到 2^31(2 十亿左右)时，它在半夜失败了。

将列类型从 INT 更改为 BIGINT 对于 20 亿条记录的表来说并不是一个解决方案(需要很长时间才能完成，并且当系统上线时，时间永远不够)。解决方案是创建一个具有相同结构的新表，但使用 BIGINT 作为 PK 列，并为 AUTO_INCRMENT 列指定初始值，然后切换表:

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`;