Mysql大資料表重複資料刪除資料

對沒有索引的欄位進行查詢重複時如果使用select name from table where name in(select name from table group by name having count(name)>1)這類查詢，效率非常低，是不可取的，下面給出替代步驟：

1.根據重複記錄建立暫存資料表

create table   temptable as (

   select title from video

    GROUP BY title HAVING count(title) >1

);

2.查詢重複資料

select a.* from temptable t,video a where a.title=t.title;

 

下面給出實際中我使用的重複資料刪除資料的指令碼，供參考：

create table   temptable as (   select MAX(id) as id  from video    GROUP BY title HAVING count(title) >1);delete  from video where id in (select id from temptable) order by id ;drop table temptable;