利用临时表去除数据库重复数据

第一步：备份数据并创建临时表
create table jc_content_ext_bak as select * from jc_content_ext;

第二步：临时表创建索引
alter table jc_content_ext_bak add primary key (content_id);
alter table jc_content_ext_bak add index idx_jc_content_ext_01 (title);
alter table jc_content_ext_bak add UNIQUE idx_jc_content_ext_02 (content_id,title);

第三部：删除
delete from jc_content_ext where content_id in(
select content_id from jc_content_ext_bak
where content_id not in
(select max(content_id) content_id from jc_content_ext_bak
group by title HAVING count(content_id)>1)
and title in
(select title from jc_content_ext_bak group by title HAVING count(content_id)>1));

 

create table t_id as
select content_id from jc_content_ext_bak
where content_id not in
(select max(content_id) content_id from jc_content_ext_bak
group by title HAVING count(content_id)>1)
and title in
(select title from jc_content_ext_bak group by title HAVING count(content_id)>1);

delete from jc_content_ext where content_id in(select * from t_id ）；

第四步：测试并验证数据
select title from jc_content_ext group by title HAVING count(content_id)>1);

第五步：删除临时表
drop table jc_content_ext_bak ;
drop table t_id;

转载于:https://www.cnblogs.com/poilk/p/6879277.html