ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1'

测试环境

1.MYSQL版本
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+

2.建表语句
mysql> show create table M;                                                                                                                                                                                                                                                                                                                                                CREATE TABLE 
create table `M` (
  `id` int(10) NOT NULL DEFAULT '0',
  `domain` varchar(255) DEFAULT NULL,
  `ip` int(10) unsigned DEFAULT NULL,
  `ipv6` varbinary(16) DEFAULT NULL,
  `intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

3.表结构

mysql> desc M;
+-----------+------------------+------+-----+-------------------+-----------------------------+
| Field     | Type             | Null | Key | Default           | Extra                       |
+-----------+------------------+------+-----+-------------------+-----------------------------+
| id        | int(10)          | NO   | PRI | 0                 |                             |
| domain    | varchar(255)     | YES  | UNI | NULL              |                             |
| ip        | int(10) unsigned | YES  |     | NULL              |                             |
| ipv6      | varbinary(16)    | YES  |     | NULL              |                             |
| intime    | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+------------------+------+-----+-------------------+-----------------------------+

测试如下:

在建表的时候没有使用自增ID,这样会引业务后续出现小问题,现在通过数据交换的方式更改M表ID为自增ID

注:生产环境谨慎执行

1.直接更改M表时,是更改不了的,有主键冲突
mysql> alter table M change id id int not null auto_increment;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'


2.可以用数据交换的方式来进行更改,创建新表M_new

mysql> CREATE TABLE M_new LIKE M;

Query OK, 0 rows affected (0.49 sec)


3.更改新表M_new为自增ID

mysql> ALTER TABLE M_new 
    -> CHANGE COLUMN `Id` `Id` INT(10) NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.79 sec)
Records: 0  Duplicates: 0  Warnings: 0


4.将M表数据插入到M_new表中,注意select的列是除了ID列

mysql> insert into M_new (domain,ip,ipv6,intime) select domain,ip,ipv6,intime from M;


5.数据插入完成后,交换表名
mysql>RENAME TABLE M TO M_old, M_new TO M;

Query OK, 0 rows affected (0.34 sec)

6.再次查看M表,现在是自增ID了

mysql> desc M;
+-----------+------------------+------+-----+-------------------+-----------------------------+
| Field     | Type             | Null | Key | Default           | Extra                       |
+-----------+------------------+------+-----+-------------------+-----------------------------+
| Id        | int(10)          | NO   | PRI | NULL              | auto_increment              |
| domain    | varchar(255)     | YES  | UNI | NULL              |                             |
| ip        | int(10) unsigned | YES  |     | NULL              |                             |
| ipv6      | varbinary(16)    | YES  |     | NULL              |                             |
| intime    | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
|+-----------+------------------+------+-----+-------------------+-----------------------------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值