自增主键达到上限，无法插入数据-j9九游会登录

场景现象

插入数据时报错：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'

j9九游会登录的解决方案

• 如果数据变化较多，表中实际数据量远小于自增主键的容量，则可以考虑将该表的数据全量导入新表，删除原表，然后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)