文档首页> 云数据库 RDS> 故障排除> RDS for MySQL> 其他使用问题> 自增主键达到上限,无法插入数据
更新时间:2022-09-13 GMT+08:00
分享

自增主键达到上限,无法插入数据

场景现象

插入数据时报错:ERROR 1062 (23000): Duplicate entry 'xxx' for key 'xxx'

原因分析

自增主键的字段取值达到上限,无法继续增长,导致新插入的数据生成的自增主键值与表中上一条数据相同,因为自增主键的值不可重复,插入失败报错。

mysql> create table auto_test5(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`)); 
Query OK, 0 rows affected (0.06 sec) mysql> insert into auto_test5(name) values('A'),('B'),('C'); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test5; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | A    | 
|  2 | B    | 
|  3 | C    | 
+----+------+ 
3 rows in set (0.00 sec)  
mysql> alter table auto_test5 AUTO_INCREMENT=125; 
Query OK, 0 rows affected (0.05 sec) 
Records: 0  Duplicates: 0  Warnings: 0  
mysql> show create table auto_test5; 
+------------+-------------------------------------------------------------+ 
| Table      | Create Table                                                | 
+------------+-------------------------------------------------------------+ 
| auto_test5 | CREATE TABLE `auto_test5` (   
`id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 | 
+------------+-------------------------------------------------------------+ 
mysql> insert into auto_test5(name) values('X'),('Y'),('Z'); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test5; 
+-----+------+ 
| id  | name | 
+-----+------+ 
|   1 | A    | 
|   2 | B    | 
|   3 | C    | 
| 125 | X    | 
| 126 | Y    | 
| 127 | Z    | 
+-----+------+ 
6 rows in set (0.00 sec) 
mysql> show create table auto_test5; 
+------------+-------------------------------------------------------------+ 
| Table      | Create Table                                                | 
+------------+-------------------------------------------------------------+ 
| auto_test5 | CREATE TABLE `auto_test5` (   `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8 | 
+------------+-------------------------------------------------------------+ 
mysql> insert into auto_test5(name) values('D'); 
ERROR 1062 (23000): Duplicate entry '127' for key 'auto_test5.PRIMARY'

解决方案

  • 如果数据变化较多,表中实际数据量远小于自增主键的容量,则可以考虑将该表的数据全量导入新表,删除原表,然后rename将新表名改回原表名。(数据导入导出的方法有多种,此处仅为示例)
    mysql>  create table auto_test5_tmp(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`)); 
    Query OK, 0 rows affected (0.07 sec) 
    mysql> insert into auto_test5_tmp select 0,name from auto_test5; 
    Query OK, 6 rows affected (0.01 sec) 
    Records: 6  Duplicates: 0  Warnings: 0 
    mysql> select * from auto_test5_tmp; 
    +----+------+ 
    | id | name | 
    +----+------+ 
    |  1 | A    | 
    |  2 | B    | 
    |  3 | C    | 
    |  4 | X    | 
    |  5 | Y    | 
    |  6 | Z    | 
    +----+------+ 
    mysql> drop table auto_test5; 
    mysql> rename table auto_test5_tmp to auto_test5; 
    Query OK, 0 rows affected (0.12 sec)  
    mysql> select * from auto_test5; 
    +----+------+ 
    | id | name | 
    +----+------+ 
    |  1 | A    | 
    |  2 | B    | 
    |  3 | C    | 
    |  4 | X    | 
    |  5 | Y    | 
    |  6 | Z    | 
    +----+------+ 
    6 rows in set (0.01 sec) 
    mysql> show create table auto_test5; 
    +------------+-----------------------------------------------------------+ 
    | Table      | Create Table                                              | 
    +------------+-----------------------------------------------------------+ 
    | auto_test5 | CREATE TABLE `auto_test5` (   
    `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | 
    +------------+-----------------------------------------------------------+
  • 如果确实是自增主键的取值范围不够,则修改自增主键的字段类型,使其能存更多、更大的数据。
    mysql> select * from auto_test6; 
    +-----+------+ 
    | id  | name | 
    +-----+------+ 
    |   1 | A    | 
    |   2 | B    | 
    |   3 | C    | 
    | 125 | X    | 
    | 126 | Y    | 
    | 127 | Z    | 
    +-----+------+ 
    6 rows in set (0.00 sec)  
    mysql> show create table auto_test6; 
    +------------+-------------------------------------------------------------+ 
    | Table      | Create Table                                                | 
    +------------+-------------------------------------------------------------+ 
    | auto_test6 | CREATE TABLE `auto_test6` (   
    `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8 | 
    +------------+-------------------------------------------------------------+ 
    mysql> alter table auto_test6  modify column id int NOT NULL AUTO_INCREMENT; 
    Query OK, 6 rows affected (0.15 sec) 
    Records: 6  Duplicates: 0  Warnings: 0  
    mysql> show create table auto_test6; 
    +------------+-------------------------------------------------------------+ 
    | Table      | Create Table                                                | 
    +------------+-------------------------------------------------------------+ 
    | auto_test6 | CREATE TABLE `auto_test6` (   
    `id` int NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 | 
    +------------+-------------------------------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> insert into auto_test6(name) values('D'); 
    Query OK, 1 row affected (0.01 sec)  
    mysql> select * from auto_test6; 
    +-----+------+ 
    | id  | name | 
    +-----+------+ 
    |   1 | A    | 
    |   2 | B    | 
    |   3 | C    | 
    | 125 | X    | 
    | 126 | Y    | 
    | 127 | Z    | 
    | 128 | D    | 
    +-----+------+ 
    7 rows in set (0.00 sec)  
    mysql> show create table auto_test6; 
    +------------+-------------------------------------------------------------+ 
    | Table      | Create Table                                                | 
    +------------+-------------------------------------------------------------+ 
    | auto_test6 | CREATE TABLE `auto_test6` (   
    `id` int NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
    PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8 | 
    +------------+-------------------------------------------------------------+ 
    1 row in set (0.01 sec)
分享:

    相关文档

    相关产品