文档首页/ 云数据库 RDS/ 故障排除/ RDS for MySQL/ 其他使用问题/ 表的自增AUTO_INCREMENT超过数据中该字段的最大值加1
更新时间:2023-04-19 GMT+08:00
分享

表的自增AUTO_INCREMENT超过数据中该字段的最大值加1

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

  • 如果步长不为1,则AUTO_INCREMENT=最大值+步长。关于步长不为1的参数说明,请参见14.1.62 表的自增AUTO_INCREMENT初值与步长
    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 | 
    +----+ 
    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> 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         | 
    +---------+-----------------------------------------------------+
  • 未提交的事务或回滚的事务,会导致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 | 
    +------------+----------------------------------------+
  • 数据插入后,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 | 
    +------------+----------------------------------------+

相关文档