表的自增AUTO_INCREMENT超过数据中该字段的最大值加1

在数据表中会发现AUTO_INCREMENT的值不等于表中字段最大值+1，可能原因有以下几种：

• 如果步长不为1，则AUTO_INCREMENT=最大值+步长。关于步长不为1的参数说明，请参见14.1.62 表的自增AUTO_INCREMENT初值与步长。

  mysql> show variables like 'auto_inc%'; 
  +--------------------------+-------+ 
  | Variable_name            | Value | 
  +--------------------------+-------+ 
  | auto_increment_increment | 2     | 
  | auto_increment_offset    | 1     | 
  +--------------------------+-------+ 
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  2 | 
  |  4 | 
  |  6 | 
  |  8 | 
  +----+ 
  mysql> show create table auto_test1; 
  +------------+-----------------------------------------+ 
  | Table      | Create Table                            | 
  +------------+-----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 | 
  +------------+-----------------------------------------+

• 直接修改表的AUTO_INCREMENT，会导致AUTO_INCREMENT变化。

  mysql> select * from animals; 
  +----+-----------+ 
  | id | name      | 
  +----+-----------+ 
  |  1 | fish       | 
  |  2 | cat       | 
  |  3 | penguin   | 
  +----+-----------+ 
  mysql> show create table animals; 
  +---------+-----------------------------------------------------+ 
  | Table   | Create Table                                        | 
  +---------+-----------------------------------------------------+ 
  | animals | CREATE TABLE `animals` (   
  `id` mediumint NOT NULL AUTO_INCREMENT,   
  `name` char(30) NOT NULL,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8           | 
  +---------+-----------------------------------------------------+ 
  mysql> alter table animals AUTO_INCREMENT=100; 
  Query OK, 0 rows affected (0.04 sec) 
  Records: 0  Duplicates: 0  Warnings: 0 
  mysql> show create table animals; 
  +---------+-----------------------------------------------------+ 
  | Table   | Create Table                                        | 
  +---------+-----------------------------------------------------+ 
  | animals | CREATE TABLE `animals` (   
  `id` mediumint NOT NULL AUTO_INCREMENT,   
  `name` char(30) NOT NULL,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8         | 
  +---------+-----------------------------------------------------+

• 未提交的事务或回滚的事务，会导致AUTO_INCREMENT增长，但回滚后不会下降。

  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+ 
  1 row in set (0.00 sec)  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  +----+ 
  mysql> begin; 
  Query OK, 0 rows affected (0.02 sec)  
  mysql> insert into auto_test1 values (0),(0),(0); 
  Query OK, 3 rows affected (0.00 sec) 
  Records: 3  Duplicates: 0  Warnings: 0  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  |  4 | 
  |  5 | 
  |  6 | 
  +----+ 
  6 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+ 
  1 row in set (0.00 sec)  
  mysql> rollback; 
  Query OK, 0 rows affected (0.05 sec)  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  +----+ 
  3 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+

• 数据插入后，AUTO_INCREMENT变化，然后删除对应的数据行，AUTO_INCREMENT不会下降。

  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+ 
  1 row in set (0.00 sec)  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  +----+ 
  mysql> insert into auto_test1 values (0),(0),(0); 
  Query OK, 3 rows affected (0.00 sec) 
  Records: 3  Duplicates: 0  Warnings: 0  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  |  4 | 
  |  5 | 
  |  6 | 
  +----+ 
  6 rows in set (0.00 sec)  
  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+ 
  1 row in set (0.00 sec)  
  mysql> delete from auto_test1 where id>3;  
  mysql> select * from auto_test1; 
  +----+ 
  | id | 
  +----+ 
  |  1 | 
  |  2 | 
  |  3 | 
  +----+ 
  3 rows in set (0.00 sec)  mysql> show create table auto_test1; 
  +------------+----------------------------------------+ 
  | Table      | Create Table                           | 
  +------------+----------------------------------------+ 
  | auto_test1 | CREATE TABLE `auto_test1` (   
  `id` int NOT NULL AUTO_INCREMENT,   
  PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
  +------------+----------------------------------------+