AUTO_INCREMENT Value Exceeding the Maximum Value for This Field in the Table
If the value of AUTO_INCREMENT is not equal to the maximum value of this field plus 1 in a data table, the possible causes are as follows:
1. If the increment is not 1, the value of AUTO_INCREMENT is equal to the maximum value of the filed plus the 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. The value of AUTO_INCREMENT in the table is directly changed.
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. A transaction is not committed or is rolled back. The value of AUTO_INCREMENT increases but does not go back down after the rollback.
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. After data is inserted, the value of AUTO_INCREMENT changes, but when the corresponding data row is deleted, the value of AUTO_INCREMENT does not decrease.
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) |