MySQL 重複したデータを1件だけ残して削除したい

テーブル table_a

id	name	typeID
1	星宮いちご	1
2	霧矢あおい	2
3	紫吹蘭	3
4	神崎美月	3
5	星宮いちご	1
6	大空あかり	1
7	藤堂ユリカ	2
8	有栖川おとめ	4
9	星宮いちご	1
10	霧矢あおい	2

このテーブルからnameとtypeIDが重複されているデータを最初の1件だけ残して削除したいと思います。

重複しているデータ

重複しているデータは下記のクエリで調べることが出来ました

SELECT * FROM table_a
WHERE (typeID, name) IN (
  SELECT typeID, name FROM table_a
  GROUP BY typeID, name
  HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2
);

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  1 | 星宮いちご        |      1 |
|  2 | 霧矢あおい        |      2 |
|  5 | 星宮いちご        |      1 |
|  9 | 星宮いちご        |      1 |
| 10 | 霧矢あおい        |      2 |
+----+-----------------+--------+

このデータをそのまま削除するようにすると、重複しているデータ全てが削除されてしまいます。
グループ化したデータの最初の1件を除くか、最初の1件だけを取得できれば良いのですが…
例えばmin(id)で取得すると

SELECT min(id) FROM table_a
WHERE (typeID, name) IN (
  SELECT typeID, name FROM table_a
  GROUP BY typeID, name
  HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2
);

+---------+
| min(id) |
+---------+
|       1 |
+---------+

それぞれのグループからではなく、重複している全てのデータから最も小さいidのものと取得するので重複しているデータが複数ある場合は不適切です。
と、良い方法が思いつかなかったので方法を変えます。

重複のないユニークなデータだけのテーブルを作成し、それ以外を削除する

同じテーブルを比較して、nameとtypeIDが元のテーブルと同じで、idが元のテーブル以上のものが1件ならユニークなデータになりそうです。

SELECT * FROM table_a AS t1
WHERE 1 = (
  SELECT COUNT(*) FROM table_a AS t2
  WHERE t1.name = t2.name
    AND t1.typeID = t2.typeID
    AND t1.id >= t2.id
);

+----+-----------------------+--------+
| id | name                  | typeID |
+----+-----------------------+--------+
|  1 | 星宮いちご　             |      1 |
|  2 | 霧矢あおい　             |      2 |
|  3 | 紫吹蘭　　              |      3 |
|  4 | 神崎美月　　            |      3 |
|  6 | 大空あかり　             |      1 |
|  7 | 藤堂ユリカ　             |      2 |
|  8 | 有栖川おとめ             |      4 |
+----+-----------------------+--------+

ユニークなデータが取得できました。
このユニークなリスト以外を削除するか、カウントが1件より大きいものが重複しているデータなのでそれを削除するかすれば良さそうです。

ユニークなリスト以外の方が見通しが良さそうなのでそちらで

DELETE FROM table_a
WHERE id NOT IN (
  SELECT id FROM(
    SELECT * FROM table_a AS t1
    WHERE 1 = (
      SELECT COUNT(*) FROM table_a AS t2
      WHERE t1.name = t2.name
        AND t1.typeID = t2.typeID
        AND t1.id >= t2.id
    )
  ) AS tmp
);

> Query OK, 3 rows affected (0.10 sec)

SELECT * FROM table_a;
+----+-----------------------+--------+
| id | name                  | typeID |
+----+-----------------------+--------+
|  1 | 星宮いちご　             |      1 |
|  2 | 霧矢あおい　             |      2 |
|  3 | 紫吹蘭　　              |      3 |
|  4 | 神崎美月　　            |      3 |
|  6 | 大空あかり　             |      1 |
|  7 | 藤堂ユリカ　             |      2 |
|  8 | 有栖川おとめ             |      4 |
+----+-----------------------+--------+

DELETE, UPDATE時にサブクエリに同じ名前のテーブルがあるとエラーになるので、ユニークなリストを取得するサブクエリをAS tmpで別名扱いにしています。
これで無事重複しているデータはidが最小のもの1件を残して削除することができました！

MySQL 8.0 以上 WITH を使う

MySQL 8.0から使えるようになった WITH Syntax (Common Table Expressions) を使えば、テンポラリーなテーブルを作ることが出来るようなので本来は同じサブクエリーを複数使う時に威力を発揮するのだと思いますが、サブクエリーが入れ子になっているクエリの見通しを良くするのにも使えそうです。(メモリ消費はどうなんだ？って部分はあるかもですが…)

先の例だとユニークなリストを作成する部分をWITHにしてしまえば、見通しが良くなりそうです。

削除対象の重複しているデータを表示

WITH unique_list AS (
  SELECT * FROM table_a AS t1
  WHERE 1 = (
    SELECT COUNT(*) FROM table_a AS t2
    WHERE t1.name = t2.name
      AND t1.typeID = t2.typeID
      AND t1.id >= t2.id
  )
)
SELECT * FROM table_a
WHERE id NOT IN (SELECT id FROM unique_list);

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  5 | 星宮いちご        |      1 |
|  9 | 星宮いちご        |      1 |
| 10 | 霧矢あおい        |      2 |
+----+-----------------+--------+

重複を削除

WITH unique_list AS (
  SELECT * FROM table_a AS t1
  WHERE 1 = (
    SELECT COUNT(*) FROM table_a AS t2
    WHERE t1.name = t2.name
      AND t1.typeID = t2.typeID
      AND t1.id >= t2.id
  )
)
DELETE FROM table_a
WHERE id NOT IN (SELECT id FROM unique_list);

> Query OK, 3 rows affected (0.09 sec)

SELECT * FROM table_a;
+----+-----------------------+--------+
| id | name                  | typeID |
+----+-----------------------+--------+
|  1 | 星宮いちご　             |      1 |
|  2 | 霧矢あおい　             |      2 |
|  3 | 紫吹蘭　　              |      3 |
|  4 | 神崎美月　　            |      3 |
|  6 | 大空あかり　             |      1 |
|  7 | 藤堂ユリカ　             |      2 |
|  8 | 有栖川おとめ             |      4 |
+----+-----------------------+--------+

WITHを使えば既に別名になっているので、DELETE, UPDATE時にサブクエリに同じ名前のテーブルがあるとエラーになる問題の回避も楽になります。それでもNOT IN unique_listとするとエラーになるので(SELECT 〜)しなければならないのですが...

重複しているデータの最後(idが最も大きい)ものを残して重複を削除したい

上記のidが最も小さいものを残すパターンの場合idを比較することで、ユニークなリストを作成できましたが、重複するデータでidが最も大きいものを残そうとした場合は別のアプローチが必要になりそうです。

重複しているデータで`id`が最大のもの以外

idが最大のものはnameとtypeIDが同じで、元テーブルのid以上のidの時は1度なので、idが元テーブルのid以上の回数が1回より多い場合がid最大値以外の重複しているデータになりそうです。

SELECT * FROM table_a AS t1
WHERE 1 < (
  SELECT COUNT(*) FROM table_a AS t2
  WHERE t1.name = t2.name
    AND t1.typeID = t2.typeID
    AND t1.id <= t2.id
);

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  1 | 星宮いちご        |      1 |
|  2 | 霧矢あおい        |      2 |
|  5 | 星宮いちご        |      1 |
+----+-----------------+--------+

重複しているデータの削除

WITH dup_list AS (
  SELECT * FROM table_a AS t1
  WHERE 1 < (
    SELECT COUNT(*) FROM table_a AS t2
    WHERE t1.name = t2.name
      AND t1.typeID = t2.typeID
      AND t1.id <= t2.id
  )
)
DELETE FROM table_a
WHERE id IN (SELECT id FROM dup_list);

> Query OK, 3 rows affected (0.02 sec)

SELECT * FROM table_a;
+----+--------------------+--------+
| id | name               | typeID |
+----+--------------------+--------+
|  3 | 紫吹蘭　　          |      3 |
|  4 | 神崎美月　　        |      3 |
|  6 | 大空あかり　         |      1 |
|  7 | 藤堂ユリカ　         |      2 |
|  8 | 有栖川おとめ         |      4 |
|  9 | 星宮いちご　         |      1 |
| 10 | 霧矢あおい　         |      2 |
+----+--------------------+--------+