MySQL 自增键

MySQL 自增键

特点

  • 自增键默认从 1 开始，即第一条记录 insert 结束之后，它的键是 1
  • insert ... on duplicate key，可能会导致，系统自动生成的自增键，在更新阶段用不上。

自增 ID

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。


create table t(
    id int unsigned auto_increment primary key
) auto_increment=4294967295;

insert into t values(null);
-- Duplicate entry '4294967295' for key 'PRIMARY'
insert into t values(null);

第一个 insert 语句插入数据成功后，这个表的 AUTO_INCREMENT 没有改变（还是 4294967295），就导致了第二个 insert 语句又拿到相同的自增 id 值，再试图执行插入语句，报主键冲突错误。

row_id

如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

实际上，在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是，InnoDB 在设计时，给 row_id 留的只是 6 个字节的长度，这样写到数据表中时只放了最后 6 个字节，所以 row_id 能写到数据表中的值，就有两个特征：

  • row_id 写入表中的值范围，是从 0 到 2^48-1；
  • 当 dict_sys.row_id=2^48 时，如果再有插入数据的行为要来申请 row_id，拿到以后再取最后 6 个字节的话就是 0。

也就是说，写入表的 row_id 是从 0 开始到 2^48-1。达到上限后，下一个值就是 0，然后继续循环。

当然，2^48-1 这个值本身已经很大了，但是如果一个 MySQL 实例跑得足够久的话，还是可能达到这个上限的。在 InnoDB 逻辑里，申请到 row_id=N 后，就将这行数据写入表中；如果表中已经存在 row_id=N 的行，新写入的行就会覆盖原有的行。

从这个角度看，我们还是应该在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。