时间戳

首页 > 解决方案 > 为什么添加 AUTO_INCREMENT 需要这么长时间？

mysql - 为什么添加 AUTO_INCREMENT 需要这么长时间？

问题描述

说我有一张桌子

CREATE TABLE `tab_sample` (
  `id` bigint(20) NOT NULL,
  `something` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想把它变成

CREATE TABLE `tab_sample` (
  `id` bigint(20) NOT NULL,
  `something` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

但

ALTER TABLE `tab_sample` MODIFY
  id bigint(20) NOT NULL AUTO_INCREMENT;

如果表很大，则需要永远，因为由于某种原因，MYSQL 决定必须重写整个事情（首先复制到临时表，...）。

有更好的方法吗？最后，这应该只是更改默认值的规则id，不是吗？

标签： mysqlauto-incrementalter

解决方案

您期望更改是仅元数据更改，但事实并非如此。

您可以通过请求将更改作为就地更改来进行测试，并在无法满足请求时查看错误。

mysql> alter table tab_sample modify id bigint not null auto_increment, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

InnoDB 具有有限的 ALTER TABLE 类型子集，可以作为就地或仅元数据更改来完成。见https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

即使您没有更改类型而仅更改 AUTO_INCREMENT 选项，您请求的更改也可能算作“更改列数据类型”。

我认为当您的列包含 NULL 或 0 时，MySQL 必须为边缘情况进行表复制。id在这种情况下，MySQL 将生成一个新的自动增量值并替换 NULL 或 0。

mysql> insert into tab_sample (id) values (10),(20),(30),(0)

mysql> ALTER TABLE `tab_sample` MODIFY   id bigint(20) NOT NULL AUTO_INCREMENT;
Query OK, 4 rows affected (0.06 sec)

mysql> select * from tab_sample;
+----+-----------+
| id | something |
+----+-----------+
|  1 | NULL      |
| 10 | NULL      |
| 20 | NULL      |
| 30 | NULL      |
+----+-----------+

看到我的 0 通过自动增量变为 1。

其他极端情况：该列可能不是唯一键，因此它可能包含许多 0 或 NULL 行，并且它们都将被赋予新的 id。

mysql> create table tab_sample2 ( id bigint, key(id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tab_sample2 values (10),(20),(30),(0),(0),(0),(NULL),(NULL),(NULL);
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> alter table tab_sample2 modify column id bigint auto_increment;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from tab_sample2;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
+----+

MySQL 不知道表中有多少行需要新的 id 值。它可能是表中的每一行。所以为了安全起见，它只会做一个表格复制，并根据需要在尽可能多的行上填写 auto-inc 值。

推荐阅读