[MySQL] 刪除重複的資料

1). 刪除重複的資料-建索引鍵
ALTER IGNORE TABLE [表格名] ADD UNIQUE INDEX(欄位名);
ex: ALTER IGNORE TABLE change_messages2 ADD UNIQUE INDEX(change_id);

2). 刪除重複的資料
//刪除較舊的紀錄，如果要刪除新的紀錄a.iddelete from a
using [表格名] as a, [表格名] b
where (a.id>b.id)
and (a.[欄位名]=b.[欄位名])
ex:
delete from a
using change_messages2 as a, change_messages2 b
where (a.id>b.id)
and (a.name=b.name)


※Reference：
http://www.dotblogs.com.tw/doremi.miredo/archive/2008/12/20/6464.aspx
http://www.codebit.cn/mysql/deleting-duplicate-rows-in-a-mysql-database.html