mysql删除重复记录

Solution 1：

Add Unique Index on your table:

ALTER IGNORE TABLE `TableA`   
ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

Solution 2：

Add primry key in your table then you can easily remove duplicates from your table using below query:

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

 

Solution 3：

SELECT DISTINCT * INTO TableA_Verify FROM TableA;

DROP TABLE TableA;

RENAME TABLE TableA_Verify TO TableA;

 

ref：

http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql

http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql

http://stackoverflow.com/questions/14046355/how-do-i-delete-all-the-duplicate-records-in-a-mysql-table-without-temp-tables

http://www.databasejournal.com/features/mysql/article.php/10897_2201621_2/Deleting-Duplicate-Rows-in-a-MySQL-Database.htm