更改mysql数据库主键自增时报错ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1'

更改mysql数据库主键自增时报错ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1'

主要步骤：

1、将主键字段值为0的那条记录值改为其他大于0且不重复的任意数

2、修改主键字段为auto_increment

3、把刚才修改过的那条记录的值还原

CREATE TABLE `table_test_bak` (
  `oc_sys_dict_id` bigint(40) NOT NULL,
  `module` varchar(32) DEFAULT NULL COMMENT '模块名称',
  `name` varchar(32) DEFAULT NULL COMMENT '字典名称',
  `key` varchar(32) DEFAULT NULL COMMENT '字典KEY',
  `value` varchar(50) DEFAULT NULL COMMENT '字典Value',
  `remark` varchar(100) DEFAULT NULL COMMENT '备注',
  `status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '保留字段',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tenancy_id` bigint(40) NOT NULL,
  PRIMARY KEY (`oc_sys_dict_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into table_test_bak select * from table_test;


# 1、因为第一列中有个值是0
mysql> alter table table_test modify  oc_sys_dict_id bigint(40) NOT NULL auto_increment primary key;                   
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
# 2、先把值为0的修改掉
mysql> update table_test set oc_sys_dict_id=9999 where oc_sys_dict_id =0;
Query OK, 1 row affected (0.00 sec)
# 3、修改主键属性
mysql> alter table table_test CHANGE oc_sys_dict_id  oc_sys_dict_id bigint(40) NOT NULL auto_increment primary key;
Query OK, 37 rows affected (0.03 sec)
Records: 37  Duplicates: 0  Warnings: 0
# 4、恢复修改掉的数据
mysql> update table_test set oc_sys_dict_id=0 where oc_sys_dict_id=9999;   
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show create table table_test\G;
*************************** 1. row ***************************
       Table: table_test
Create Table: CREATE TABLE `table_test` (
  `oc_sys_dict_id` bigint(40) NOT NULL AUTO_INCREMENT,
  `module` varchar(32) DEFAULT NULL COMMENT '模块名称',
  `name` varchar(32) DEFAULT NULL COMMENT '字典名称',
  `key` varchar(32) DEFAULT NULL COMMENT '字典KEY',
  `value` varchar(50) DEFAULT NULL COMMENT '字典Value',
  `remark` varchar(100) DEFAULT NULL COMMENT '备注',
  `status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '保留字段',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tenancy_id` bigint(40) NOT NULL,
  PRIMARY KEY (`oc_sys_dict_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table table_test AUTO_INCREMENT=37;