5.1 作用

某个字段的值自增

5.2 关键字

auto_increment

5.3 特点和要求

（1）一个表最多只能有一个自增长列

（2）当需要产生唯一标识符或顺序值时，可设置自增长

（3）自增长列约束的列必须是键列（主键列，唯一键列）

（4）自增约束的列的数据类型必须是整数类型

（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

错误演示：

  1. create table employee(
  2.     eid int auto_increment,
  3.     ename varchar(20)
  4. );
  5. # ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
  1. create table employee(
  2.     eid int primary key,
  3.     ename varchar(20) unique key auto_increment
  4. );
  5. # ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因为ename不是整数类型

5.4 如何指定自增约束

（1）建表时

  1. create table 表名称(
  2.     字段名  数据类型  primary key auto_increment,
  3.     字段名  数据类型  unique key not null,  
  4.     字段名  数据类型  unique key,
  5.     字段名  数据类型  not null default 默认值, 
  6. );
  7. create table 表名称(
  8.     字段名  数据类型 default 默认值 ,
  9.     字段名  数据类型 unique key auto_increment,  
  10.     字段名  数据类型 not null default 默认值,,
  11.     primary key(字段名)
  12. );
  1. create table employee(
  2.     eid int primary key auto_increment,
  3.     ename varchar(20)
  4. );
  1. mysql> desc employee;
  2. +-------+-------------+------+-----+---------+----------------+
  3. | Field | Type        | Null | Key | Default | Extra          |
  4. +-------+-------------+------+-----+---------+----------------+
  5. | eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
  6. | ename | varchar(20) | YES  |     | NULL    |                |
  7. +-------+-------------+------+-----+---------+----------------+
  8. 2 rows in set (0.00 sec)

（2）建表后

  1. alter table 表名称 modify 字段名 数据类型 auto_increment;

例如：

  1. create table employee(
  2.     eid int primary key ,
  3.     ename varchar(20)
  4. );
  1. alter table employee modify eid int auto_increment;
  1. mysql> desc employee;
  2. +-------+-------------+------+-----+---------+----------------+
  3. | Field | Type        | Null | Key | Default | Extra          |
  4. +-------+-------------+------+-----+---------+----------------+
  5. | eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
  6. | ename | varchar(20) | YES  |     | NULL    |                |
  7. +-------+-------------+------+-----+---------+----------------+
  8. 2 rows in set (0.00 sec)

5.5 如何删除自增约束

  1. #alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
  3. alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
  1. alter table employee modify eid int;
  1. mysql> desc employee;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type        | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | eid   | int(11)     | NO   | PRI | NULL    |       |
  6. | ename | varchar(20) | YES  |     | NULL    |       |
  7. +-------+-------------+------+-----+---------+-------+
  8. 2 rows in set (0.00 sec)

5.6 MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
下面通过案例来对比不同的版本中自增变量是否持久化。
在MySQL 5.7版本中，测试步骤如下：
创建的数据表中包含自增主键的id字段，语句如下：

  1. CREATE TABLE test1(
  2. id INT PRIMARY KEY AUTO_INCREMENT
  3. );

插入4个空值，执行如下：

  1. INSERT INTO test1
  2. VALUES(0),(0),(0),(0);

查询数据表test1中的数据，结果如下：

  1. mysql> SELECT * FROM test1;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. |  3 |
  8. |  4 |
  9. +----+
  10. 4 rows in set (0.00 sec)

删除id为4的记录，语句如下：

  1. DELETE FROM test1 WHERE id = 4;

再次插入一个空值，语句如下：

  1. INSERT INTO test1 VALUES(0);

查询此时数据表test1中的数据，结果如下：

  1. mysql> SELECT * FROM test1;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. |  3 |
  8. |  5 |
  9. +----+
  10. 4 rows in set (0.00 sec)

从结果可以看出，虽然删除了id为4的记录，但是再次插入空值时，并没有重用被删除的4，而是分配了5。
删除id为5的记录，结果如下：

  1. DELETE FROM test1 where id=5;

重启数据库，重新插入一个空值。

  1. INSERT INTO test1 values(0);

再次查询数据表test1中的数据，结果如下：

  1. mysql> SELECT * FROM test1;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. |  3 |
  8. |  4 |
  9. +----+
  10. 4 rows in set (0.00 sec)

从结果可以看出，新插入的0值分配的是4，按照重启前的操作逻辑，此处应该分配6。出现上述结果的主要原因是自增主键没有持久化。
在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

在MySQL 8.0版本中，上述测试步骤最后一步的结果如下：

  1. mysql> SELECT * FROM test1;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. |  3 |
  8. |  6 |
  9. +----+
  10. 4 rows in set (0.00 sec)

从结果可以看出，自增变量已经持久化了。

MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。