mysql - 删除多余的记录

Question

我有一张 table :

+------------+------------------------------------------------------+------+-----+---------+-------+
| Field      | Type                                                 | Null | Key | Default | Extra |
+------------+------------------------------------------------------+------+-----+---------+-------+
| person_id1 | int(10)                                              | NO   | MUL | 0       |       |
| person_id2 | int(10)                                              | NO   | MUL | 0       |       |
| priority   | smallint(5)                                          | NO   |     | 0       |       |
| link_type  | enum('member_of_band','legal_name','performs_as','') | NO   |     |         |       |
+------------+------------------------------------------------------+------+-----+---------+-------+

这张表没有主键，但是person_id1、person_id2都有索引。

问题是 - 我们有不一致的数据，例如，这个查询:

SELECT
    COUNT(*) as c, person_id1, person_id2
FROM person_person
WHERE link_type = "member_of_band"
GROUP BY person_id1, person_id2
HAVING c > 1
LIMIT 10;

返回:

+---+------------+------------+
| c | person_id1 | person_id2 |
+---+------------+------------+
| 2 |   50674235 |   51048792 |
| 3 |   50674245 |   50715733 |
| 2 |   50674283 |   50712621 |
| 2 |   50674322 |   50714244 |
| 2 |   50674378 |   51048804 |
| 2 |   50674438 |   51048812 |
| 4 |   50674442 |   50715733 |
| 2 |   50674449 |   50716913 |
| 2 |   50674455 |   51048803 |
| 3 |   50674469 |   50715733 |
+---+------------+------------+

有没有办法删除所有多余的记录，留下没问题的？

我的想法是:

DELETE person_person FROM person_person
WHERE (person_id1, person_id2) IN (

    SELECT
        person_id1, person_id2
    FROM person_person
    WHERE link_type = "member_of_band"
    GROUP BY person_id1, person_id2
    HAVING COUNT(*) > 1
    LIMIT 100

) AND link_type = "member_of_band";

但这会删除所有带 double 的记录，而我只需要删除 double 。

mysql> select * from person_person where person_id1 = 50674245 and person_id2 = 50715733;
+------------+------------+----------+----------------+
| person_id1 | person_id2 | priority | link_type      |
+------------+------------+----------+----------------+
|   50674245 |   50715733 |        0 | member_of_band |
|   50674245 |   50715733 |        0 | member_of_band |
|   50674245 |   50715733 |        0 | member_of_band |
+------------+------------+----------+----------------+

Answer 1

ALTER IGNORE TABLE person_person ADD UNIQUE INDEX (person_id1, person_id2, link_type);