基本使用类 自增字段值跳变的原因 本节介绍了自增字段相关问题与处理方法。 数据表中的自增字段取值不是连续的,自增值跳变。 出现表中的自增字段取值不连续的情况,可能原因有以下几种: 初值与步长问题,步长不为1会导致自增字段取值不连续。 进入原库 use ta; 列出原库的所有表名 Show tables; 查看原库的创建语句 Show create database ta; 使用原库的创建语句创建新库(只改库名,其他参数照抄,这样能尽量保证新库与原库的各类参数相同) create database tb; 将原库所有表移动至新库 rename table da.ta to db.ta; rename table da.tb to db.tb; rename table da.tc to db.tc; … 删除原库 Drop database ta; 直接修改表的AUTOINCREMENT,会导致自增字段取值跳变。 进入原库 use ta; 列出原库的所有表名 Show tables; 查看原库的创建语句 Show create database ta; 使用原库的创建语句创建新库(只改库名,其他参数照抄,这样能尽量保证新库与原库的各类参数相同) create database tb; 将原库所有表移动至新库 rename table da.ta to db.ta; rename table da.tb to db.tb; rename table da.tc to db.tc; … 删除原库 Drop database ta; 插入数据时直接指定自增字段的取值,会导致自增字段取值跳变。 mysql> select from animals; ++ id name ++ 1 dog 2 cat 3 penguin ++ mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit'); Query OK, 1 row affected (0.00 sec) mysql> select from animals; ++ id name ++ 1 dog 2 cat 3 penguin 100 rabbit ++ 9 rows in set (0.00 sec) 未提交的事务或回滚的事务,会导致AUTOINCREMENT增长,但回滚后不会下降。后续如果再次插入数据就会导致数据中的自增字段发生跳变。 mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT4 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> select from autotest1; + id + 1 2 3 + mysql> begin; Query OK, 0 rows affected (0.02 sec) mysql> insert into autotest1 values (0),(0),(0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from autotest1; + id + 1 2 3 4 5 6 + 6 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT7 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.05 sec) mysql> select from autotest1; + id + 1 2 3 + 3 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT7 DEFAULT CHARSETutf8 ++ mysql> insert into autotest1 values (0),(0),(0); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from autotest1; + id + 1 2 3 7 8 9 + 6 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT10 DEFAULT CHARSETutf8 ++ 数据插入后,AUTOINCREMENT变化,然后删除对应的数据行,AUTOINCREMENT不会下降,后续如果再次插入数据就会导致数据中的自增字段发生跳变。 mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT4 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> select from autotest1; + id + 1 2 3 + mysql> insert into autotest1 values (0),(0),(0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from autotest1; + id + 1 2 3 4 5 6 + 6 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT7 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> delete from autotest1 where id>3; mysql> select from autotest1; + id + 1 2 3 + 3 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT7 DEFAULT CHARSETutf8 ++ mysql> insert into autotest1 values (0),(0),(0); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select from autotest1; + id + 1 2 3 7 8 9 + 6 rows in set (0.00 sec) mysql> show create table autotest1; ++ Table Create Table ++ autotest1 CREATE TABLE autotest1 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT10 DEFAULT CHARSETutf8 ++ 因为一些原因(比如唯一键冲突),使得插入数据最终未成功的,有可能导致AUTOINCREMENT跳变。 mysql> create table autotest7(id int NOT NULL AUTOINCREMENT, credid int UNIQUE, PRIMARY KEY (id)); Query OK, 0 rows affected (0.64 sec) mysql> insert into autotest7 values(null, 1); Query OK, 1 row affected (0.03 sec) mysql> show create table autotest7; ++ Table Create Table ++ autotest7 CREATE TABLE autotest7 ( id int NOT NULL AUTOINCREMENT, credid int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY credid (credid)) ENGINEInnoDB AUTOINCREMENT2 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> insert into autotest7 values(null, 1); ERROR 1062 (23000): Duplicate entry '1' for key 'autotest7.credid' mysql> show create table autotest7; ++ Table Create Table ++ autotest7 CREATE TABLE autotest7 ( id int NOT NULL AUTOINCREMENT, credid int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY credid (credid)) ENGINEInnoDB AUTOINCREMENT3 DEFAULT CHARSETutf8 ++ 批量插入数据时(如insert...select、load file等),自增键的申请是分批申请的,每批申请2的n次方个序号,用完继续申请,没用完也不会退回,所以可能会导致AUTOINCREMENT跳变。 mysql> create table autotest7(id int NOT NULL AUTOINCREMENT, credid int UNIQUE, PRIMARY KEY (id)); Query OK, 0 rows affected (0.64 sec) mysql> insert into autotest7 values(null, 1); Query OK, 1 row affected (0.03 sec) mysql> show create table autotest7; ++ Table Create Table ++ autotest7 CREATE TABLE autotest7 ( id int NOT NULL AUTOINCREMENT, credid int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY credid (credid)) ENGINEInnoDB AUTOINCREMENT2 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) mysql> insert into autotest7 values(null, 1); ERROR 1062 (23000): Duplicate entry '1' for key 'autotest7.credid' mysql> show create table autotest7; ++ Table Create Table ++ autotest7 CREATE TABLE autotest7 ( id int NOT NULL AUTOINCREMENT, credid int DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY credid (credid)) ENGINEInnoDB AUTOINCREMENT3 DEFAULT CHARSETutf8 ++