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_newmysql> CREATE TABLE M_new LIKE M;Query OK, 0 rows affected (0.49 sec)3.更改新表M_new为自增IDmysql> 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: 04.将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 ||+-----------+------------------+------+-----+-------------------+-----------------------------+