我的一个表 ID(主键)自增属性不知怎么被取消,现在表里面有 10多万条记录。
怎样才能恢复,这个字段的自增属性呢?
现在的ID是 133000 ,数据很重要不能删除,ID号也不能变
ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC;提示 :Mysql 错误编号 1062
Duplicate enty '1' for key 1
怎样才能恢复,这个字段的自增属性呢?
现在的ID是 133000 ,数据很重要不能删除,ID号也不能变
ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC;提示 :Mysql 错误编号 1062
Duplicate enty '1' for key 1
alter table tty modify gg int AUTO_INCREMENT primary key
提示错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=135333, ROW_FORMAT = DYNAMIC' at line 1
Query OK, 0 rows affected (0.09 sec)mysql> insert into tb1 values (1,1);
Query OK, 1 row affected (0.02 sec)mysql> insert into tb1 values (2,2);
Query OK, 1 row affected (0.00 sec)mysql> insert into tb1 values (3,3);
Query OK, 1 row affected (0.02 sec)mysql>
mysql> select * from tb1;
+------+------+
| id | f1 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)mysql>
mysql> ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCR
EMENT;
Query OK, 3 rows affected (0.48 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> insert into tb1 values (null,99);
Query OK, 1 row affected (0.05 sec)mysql> select * from tb1;
+----+------+
| id | f1 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 99 |
+----+------+
4 rows in set (0.00 sec)mysql> ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCR
EMENT, ROW_FORMAT = DYNAMIC;
Query OK, 4 rows affected (0.34 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql>
建议另外生成一个自增字段,删除旧ID列,将此列修改为ID列
3楼的大哥,那样做,ID是从135224开始呢?
set auto_increment_increment = 135224
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> insert into tb1 values (null,777);
Query OK, 1 row affected (0.05 sec)mysql> select * from tb1;
+--------+------+
| id | f1 |
+--------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 99 |
| 135224 | 777 |
+--------+------+
5 rows in set (0.00 sec)mysql>
ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC; 而表内已经存在ID为1的记录了。
2。 提供 ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC; 的错误信息
ALTER TABLE dd AUTO_INCREMENT = 135224
再插入记录,从135224开始
ALTER TABLE tb1 MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC;
ALTER TABLE dd AUTO_INCREMENT = 135224 建议贴出来前,先自己做个测试。
select id,count(*) from tb_name group by id having count(*)>1;然后更改列属性(如果要设成自增的列不是主键,则还要设置为主键):
ALTER TABLE tb_name MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, ROW_FORMAT = DYNAMIC; 设了自增属性后,如果还想另外重新设置最新的自增值(非现有重复的才行):
ALTER TABLE tb_name AUTO_INCREMENT = 最新值;