华为云云数据库rds自增主键达到上限，无法插入数据_云淘科技
场景现象
插入数据时报错：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)
父主题： 其他使用问题
同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）
内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家
