AUTO_INCREMENT超过表中该字段的最大值 本文针对AUTOINCREMENT超过表中该字段的最大值时产生的问题及应对方案进行了说明。 表的自动新增AUTOINCREMENT超过数据中该字段的最大值 在数据表中会发现AUTOINCREMENT的值不等于表中字段最大值+1,可能原因有以下几种: 1.步长不为1,则AUTOINCREMENT最大值+步长。 mysql> show variables like 'autoinc%'; ++ Variablename Value ++ autoincrementincrement 10 autoincrementoffset 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 AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT35 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) 2.直接修改表的AUTOINCREMENT,会导致AUTOINCREMENT变化。 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 AUTOINCREMENT, user char(10) COLLATE utf8mb4bin NOT NULL, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT100 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) mysql> alter table tianyiyun AUTOINCREMENT99; 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 AUTOINCREMENT, user char(10) COLLATE utf8mb4bin NOT NULL, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT100 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) 3.未提交的事务或回滚的事务,会导致AUTOINCREMENT增长,但回滚后不会下降。 mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT7 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) mysql> select from tiayiyuntest; + 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 tiayiyuntest values (0),(0),(0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from tiayiyuntest; + uid + 1 2 3 4 5 6 15 25 35 + 9 rows in set (0.00 sec) mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT45 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select from tiayiyuntest; + uid + 1 2 3 4 5 6 + 6 rows in set (0.00 sec) mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT45 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) 4.数据插入后,AUTOINCREMENT变化,然后删除对应的数据行,AUTOINCREMENT不会下降。 mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT45 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) mysql> select from tiayiyuntest; + uid + 1 2 3 4 5 6 + 6 rows in set (0.00 sec) mysql> insert into tiayiyuntest values (0),(0),(0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from tiayiyuntest; + uid + 1 2 3 4 5 6 45 55 65 + 9 rows in set (0.00 sec) mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT75 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec) mysql> delete from tiayiyuntest where uid>3; Query OK, 6 rows affected (0.00 sec) mysql> select from tiayiyuntest; + uid + 1 2 3 + 3 rows in set (0.00 sec) mysql> show create table tiayiyuntest; ++ Table Create Table ++ tiayiyuntest CREATE TABLE tiayiyuntest ( uid int(11) NOT NULL AUTOINCREMENT, PRIMARY KEY (uid) ) ENGINEInnoDB AUTOINCREMENT75 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4bin ++ 1 row in set (0.00 sec)