如何在mysql中删除与其他行重复的行？

Question

如何在mysql中删除与其他行重复的行？

我想创建页面delete_duplicate.php

加载pagr delete_duplicate.php时

将删除id为3和4行

因为行id %3重复行id %2和行id %4重复行id %1

如何编写php代码？

加载页面前delete_duplicate.php-table:成员

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____3_____|__100005__|____bbbbbb____|
|____4_____|__100261__|____aaaaaa____|
|____5_____|__100261__|____cccccc____|

加载页面后delete_duplicate.php-table:成员

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____5_____|__100261__|____cccccc____|

Answer 1

您可以通过在user_id和product_id上创建唯一索引来永久解决此问题

create unique index member_userid_productid on member(userid, productid);

对于您的示例，您可以使用带有join的delete来删除重复项

delete m from members m join
              (select m.user_id, m.product_id, min(m.like_id) as minlike_id
               from members m
               group by m.user_id, m.product_id
              ) tokeep
              on m.user_id = tokeep.user_id and
                 m.product_id = tokeep.product_id and
                 m.like_id > tokeep.minlike_id;

Answer 2

您应该发出以下语句，以便从表中删除重复的记录并实现product_id的唯一性

DELETE e1 FROM member e1, member e2 WHERE e1.product_id = e2.product_id AND e1.likeid > e2.likeid;

Answer 3

如果你想通过SQL实现这一点，那么下面的查询会有所帮助

DELETE FROM member m1, member m2 WHERE m1.likeid > m2.likeid 
AND m1.userid = m2.userid 
AND m1.product_id = m2.product_id