目录

  • 基本用法
  • 样例
  • 基本要求
  • 使用要点
  • 初始值
  • 自增列计数器
  • NULL的处理
  • 存储指定数值
  • 存0
  • 负数
  • 存储其他指定数值
  • 其他贴士
  • 查看自增列当前的最大值
  • MySQL重启后自增列的初始化
  • `replace into`可能改变自增列
  • 自增列超过最大值


MySQL可以通过AUTO_INCREMENT关键字，实现列数据自增。一般用于单表的唯一标识列，常见的有主键。每张表最多只能有一个自增列。

基本用法

样例

CREATE TABLE `test` (
  `k` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`k`),
  UNIQUE KEY `key_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

基本要求

  • 设置AUTO_INCREMENT的列，需为整数或浮点数据类型
  • 自增列需设置为NOT NULL，否则会直接存储NULL

使用要点


初始值

自增列默认是从1开始，即插入第一行数据默认是1。
可在建表时设置AUTO_INCREMENT=n，指定一个自增的初始值。

CREATE TABLEtest(...) AUTO_INCREMENT=10，表示自增列从10开始自增。

也可以通过ALTER TABLE test auto_increment = n来设置，但这样下次插入的值是n+1。

数据量大的时候性能比较差

注意：MySQL重启后，InnoDB引擎的表，自增列需要重新初始化。具体原因参见MySQL重启后自增列的初始化和自增列计数器。


自增列计数器

InnoDB引擎中，含自增列的表，在数据字典中会包含一个自动增量计数器，用于为该列分配新值，使用show create table看到的建表语句中，显示的AUTO_INCREMENT=n就是这个计数器值。

该计数器仅存储在内存里，而没有刷新到磁盘，重启后可能会丢失。参见MySQL重启后自增列的初始化

初始化这个计数器有几种方法：

  • 上面自增列计数器中介绍的CREATE TABLE或ALTER TABLE指定
  • SELECT MAX(ai_col) FROM table_name FOR UPDATE;

注意该方法有失效可能。如果SHOW TABLE STATUS语句在初始化之前检查了表，就不会递增这个值了。
这个方式是使用对表的常规互斥锁定读取，并且该锁定持续到事务结束。

  • 新创建表的计数器也是以类似原理处理的
  • 初始化自动增量计数器后，如果插入数据时自增列没有指定值，则InnoDB会递增计数器并将新值分配给该列。
  • 如果插入时给自增列指定了值，且该值大于当前计数器值，则计数器将设置为指定的列值。

只要服务器运行，InnoDB就会使用内存中的自动增量计数器。如前所述，当服务器停止并重新启动时，该表表的执行第一个INSERT时，InnoDB会重新初始化自增计数器。

注意：重启MySQL后，CREATE TABLE和ALTER TABLE时指定的AUTO_INCREMENT = N就失效了，需要的话要重新设置（MySQL重启后自增列的初始化）。

NULL的处理

自增列插入NULL时，将设置为下一个序列值，一般就是当前最大值+1

插入数据时，没有明确给该列设值，等同于NULL

存储指定数值

自增列需保证唯一，优先判断是否重复，重复则报错。

存0

存0和存NULL是相同效果，但0容易和真实值混淆，所以不建议这样用。

  • 如果在sql_mode中配置NO_AUTO_VALUE_ON_ZERO，0会直接存储、不会自动生成值
  • 如果不方便调sql_mode，还可以通过先插入、再更新成0的方式；
  • 但注意，较大值更新为较小值、不会更新自增序列。即如果是当前最大值(max)这行更新成0，尽管更新后最大值已经变成max - 1，但下一次插入数据，自动生成的主键仍然是max + 1而不是max

负数

不支持负数。

因此自增列一般要设置为UNSIGNED，这样可以将自增容量提升一倍。

存储其他指定数值

存储一个指定数值时(后面代称为value1)，会直接存储该值。

  • 如果该值大于当前最大值max，那么会重新排列，下次插入会以当前值(value1)为开始继续生成新值(value1 + 1)。效果上就是会跳过一些值。
  • 如果该值小于当前值，只判断是否重复。

其他贴士

查看自增列当前的最大值

一般会使用select max(col) from xxx的方式获取自增列最大值，但这个方式需要计算，而且会让MySQL更新计数器。

其实还有更简单的方式：show create table xxx，效果如下图

mysql 自增列sql mysql 自增列_mysql 自增列sql

MySQL重启后自增列的初始化

如自增列计数器所讲，MySQL通过维护这个计数器，实现自增值的维护和分配。但因为但该计数器仅存储在内存里，而没有刷新到磁盘，这就意味着，一旦MySQL重启，自增列会从初始值开始自增，而不是表中当前的最大值。

所以MySQL重启后，需要重新初始化计数器为自增列最大值。
在自增列计数器中介绍了初始化的几种方式：

  • ALTER TABLE test auto_increment = n
  • SELECT MAX(ai_col) FROM table_name FOR UPDATE;

replace into可能改变自增列

如果一个表同时存在自增主键和唯一键，replace into指定了新的主键值或不指定主键值，会导致主键被更新成新值。

自增列超过最大值

当AUTO_INCREMENT整数列的值用完时，后续INSERT操作将返回重复键错误。这是MySQL的标准处理模式。