mysql只要重复的,解决mysql数据重复问题-只保留一条数据

1.sql语句,原理使用临时表法，效率比较低

delete

FROM `source`

WHERE is_deleted=0

AND (student_id,score_id) IN

(SELECT f.student_id ,

f.score_id

FROM

(SELECT b.`student_id` AS student_id ,

b.`score_id` AS score_id

FROM `source` b

WHERE b.`is_deleted`=0

GROUP BY b.student_id,b.score_id

HAVING count(*) > 1) f )

AND id NOT IN

(SELECT g.id

FROM

(SELECT min(c.id) AS id

FROM `source` c

WHERE c.`is_deleted`=0

GROUP BY c.`student_id`,c.`score_id`

HAVING count(*)>1) g );

2.临时表法-需要dba协作

1.停服情况下，原表(备份)，建一张临时表，将原表的唯一数据拷贝到临时表中，删除源表，更改临时表表名

2.非停服情况下去重，新建一张临时表

insert into temp () values (select distinct * from source)

delete from source where 重复数据 in (select * from temp)

insert into source () values(select * from temp)