AUTO_INCREMENT超过表中该字段的最大值

表的自动新增AUTOINCREMENT超过数据中该字段的最大值

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

1.步长不为1，则AUTO_INCREMENT=最大值+步长。

mysql> show variables like 'auto_inc%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | auto_increment_increment | 10    |
 | auto_increment_offset    | 5     |
 +--------------------------+-------+
 2 rows in set (0.00 sec)
 mysql> select * from tianyiy;
 +-----+
 | uid |
 +-----+
 |   5 |
 |  15 |
 |  25 |
 +-----+
 3 rows in set (0.00 sec)
 mysql> show create table tianyiy;
 +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table   | Create Table                                                                                                                                                          |
 +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiy | CREATE TABLE `tianyiy` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

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

mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 | -30 | k    |
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 |   7 | g    |
 |   8 | h    |
 |  30 | j    |
 |  99 | i    |
 +-----+------+
 11 rows in set (0.00 sec)
 mysql> show create table tianyiyun;
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                  |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> alter table tianyiyun AUTO_INCREMENT=99;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 mysql> show create table tianyiyun;
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                  |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

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

mysql> show create table tiayiyun_test;
 +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                               |
 +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 |   4 |
 |   5 |
 |   6 |
 +-----+
 6 rows in set (0.00 sec)
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 mysql> insert into tiayiyun_test values (0),(0),(0);
 Query OK, 3 rows affected (0.00 sec)
 Records: 3  Duplicates: 0  Warnings: 0
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 |   4 |
 |   5 |
 |   6 |
 |  15 |
 |  25 |
 |  35 |
 +-----+
 9 rows in set (0.00 sec)
 mysql> show create table tiayiyun_test;
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                                |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> rollback;
 Query OK, 0 rows affected (0.01 sec)
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 |   4 |
 |   5 |
 |   6 |
 +-----+
 6 rows in set (0.00 sec)
 mysql> show create table tiayiyun_test;
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                                |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

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

mysql> show create table tiayiyun_test;
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                                |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 |   4 |
 |   5 |
 |   6 |
 +-----+
 6 rows in set (0.00 sec)
 mysql> insert into tiayiyun_test values (0),(0),(0);
 Query OK, 3 rows affected (0.00 sec)
 Records: 3  Duplicates: 0  Warnings: 0
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 |   4 |
 |   5 |
 |   6 |
 |  45 |
 |  55 |
 |  65 |
 +-----+
 9 rows in set (0.00 sec)
 mysql> show create table tiayiyun_test;
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                                |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> delete from tiayiyun_test where uid>3;
 Query OK, 6 rows affected (0.00 sec)
 mysql> select * from tiayiyun_test;
 +-----+
 | uid |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 +-----+
 3 rows in set (0.00 sec)
 mysql> show create table tiayiyun_test;
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table         | Create Table                                                                                                                                                                |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tiayiyun_test | CREATE TABLE `tiayiyun_test` (
   `uid` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)