MySQL删除表中重复数据及主键重新排列
一、删除表中重复数据
删除前:
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select * from duplicate; + ----+-------+ | id | name | + ----+-------+ | 1 | wang | | 3 | wdang | | 5 | wdand | | 6 | wddda | | 2 | wang | | 4 | wdang | + ----+-------+ |
删除后:
1
2
3
4
5
6
7
8
9
|
mysql> select * from duplicate; + ----+-------+ | id | name | + ----+-------+ | 1 | wang | | 3 | wdang | | 5 | wdand | | 6 | wddda | + ----+-------+ |
sql语句:
1
|
delete duplicate as a from duplicate as a,( select * from duplicate group by name having count (1)>1) as b where a. name =b. name and a.id > b.id; |
二、自增主键ID重新排列
需求:数据表的自增主键ID号乱了,不是连贯数字,需要重新排列
原理:先删除原有的自增ID,然后重新建立新的自增ID
1、删除原有主键:
1
|
ALTER TABLE `mytable` DROP id; |
2、增加新主键字段:
1
|
ALTER TABLE `mytable` ADD id INT (11) NOT NULL FIRST ; |
3、设置新主键:
ALTER
TABLE
`mytable`
MODIFY
COLUMN
id
INT
(11)
NOT
NULL
AUTO_INCREMENT,
ADD
PRIMARY
KEY
(id);
实践:
delete ws_ncu_lib as a from ws_ncu_lib as a, (select * from ws_ncu_lib group by url having count(*)> 1) as b where a.url = b.url and a.id > b.id;
alter table ws_ncu_lib drop id;
alter table ws_ncu_lib add id int(10) not null first;
alter table ws_ncu_lib modify column id int(10) not null auto_increment, add primary key(id);
另一种方法
create temporary table newtable select * from ws_ncu_lib group by url having count(*) > 1;
delete from ws_ncu_lib using( ws_ncu_lib, newtable) where ws_ncu_lib.url=newtable.url;
insert into ws_ncu_lib (select * from newtable);