更改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;
更改mysql数据库主键自增时报错ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1'