华为云云数据库rds自增字段值跳变的原因_云淘科技
出现表中的自增字段取值不连续的情况，可能原因有以下几种：
初值与步长问题，步长不为1会导致自增字段取值不连续。
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 | +----+
直接修改表的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 | +---------+-----------------------------------------------------+ mysql> INSERT INTO animals (id,name) VALUES(0,'rabbit'); Query OK, 1 row affected (0.00 sec) mysql> select * from animals; +-----+-----------+ | id | name | +-----+-----------+ | 1 | fish | | 2 | cat | | 3 | penguin | | 100 | rabbit | +-----+-----------+ 9 rows in set (0.00 sec)
插入数据时直接指定自增字段的取值，会导致自增字段取值跳变。
mysql> select * from animals; +----+-----------+ | id | name | +----+-----------+ | 1 | fish | | 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 | fish | | 2 | cat | | 3 | penguin | | 100 | rabbit | +-----+-----------+ 9 rows in set (0.00 sec)
未提交的事务或回滚的事务，会导致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 | +------------+----------------------------------------+ mysql> insert into auto_test1 values (0),(0),(0); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from auto_test1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 7 | | 8 | | 9 | +----+ 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=10 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 | +------------+----------------------------------------+ mysql> insert into auto_test1 values (0),(0),(0); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from auto_test1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 7 | | 8 | | 9 | +----+ 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=10 DEFAULT CHARSET=utf8 | +------------+-----------------------------------------+
因为一些原因（比如唯一键冲突），使得插入数据最终未成功的，有可能导致AUTO_INCREMENT跳变。
mysql> create table auto_test7(`id` int NOT NULL AUTO_INCREMENT, cred_id int UNIQUE, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.64 sec) mysql> insert into auto_test7 values(null, 1); Query OK, 1 row affected (0.03 sec) mysql> show create table auto_test7; +------------+-------------------------------+ | Table | Create Table | +------------+-------------------------------+ | auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 | +------------+--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into auto_test7 values(null, 1); ERROR 1062 (23000): Duplicate entry '1' for key 'auto_test7.cred_id' mysql> show create table auto_test7; +------------+--------------------------------------------------------------+ | Table | Create Table | +------------+--------------------------------------------------------------+ | auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | +------------+---------------------------------------------------------------+
批量插入数据时（如insert…select、load file等），自增键的申请是分批申请的，每批申请2的n次方个序号，用完继续申请，没用完也不会退回，所以可能会导致AUTO_INCREMENT跳变。
mysql> create table auto_test5_tmp(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.08 sec) mysql> select * from auto_test5; +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | X | | 5 | Y | | 6 | Z | | 8 | A | | 9 | B | | 10 | C | | 11 | X | | 12 | Y | | 13 | Z | +----+------+ 12 rows in set (0.00 sec) mysql> insert into auto_test5_tmp select 0,name from auto_test5; Query OK, 12 rows affected (0.01 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> select * from auto_test5_tmp; +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | X | | 5 | Y | | 6 | Z | | 7 | A | | 8 | B | | 9 | C | | 10 | X | | 11 | Y | | 12 | Z | +----+------+ 12 rows in set (0.00 sec) mysql> show create table auto_test5_tmp; +----------------+-------------------------------------------------------+ | Table | Create Table | +----------------+-------------------------------------------------------+ | auto_test5_tmp | CREATE TABLE `auto_test5_tmp` ( `id` tinyint NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 | +----------------+-------------------------------------------------------+
父主题： 其他使用问题
同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）
内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家
