【MySQL】同テーブル内に重複した値を持つレコードを見つけるクエリ

　時たま格納される値がユニークであるべきカラムにユニーク制約がついておらず、ユニークでない値が格納されてしまう時があります。そういった問題が起きている時、事態の収拾のために値が重複しているレコードを見つけ、ユニークになる様に何がしかの対応をする必要があります。この値が重複しているレコードを見つけるクエリを紹介します。

　具体的なクエリは次です。

SELECT *
FROM accounts # 重複を持つレコードを探すテーブル（以降大元のテーブル）
WHERE exists(
          # サブクエリで大元のテーブルと大元のテーブルのエイリアスを WHERE でつなぐことによって
          # 同テーブル内の異なるレコードを比較します。
              SELECT *
              FROM accounts sub # 大元のテーブルのエイリアス（以降エイリアステーブル）。
                   # 大元のテーブルとエイリアステーブルの主キーが異なることを示す WHERE です。
                   # これをつけないと同一のレコードを比較対象に選んでしまい、重複を見つけられません。
              WHERE sub.account_id <> accounts.account_id
                # ここでは name が重複しているレコードを見つけます。
                # 適宜、目的のカラムに名前を変更する必要があります。
                AND sub.name = accounts.name
          )

　これを例えば次のテストデータと組み合わせると

create table accounts
(
    account_id bigint unsigned auto_increment
        primary key,
    name       varchar(255) not null comment '名前'
)
    comment 'アカウント' collate = utf8mb4_unicode_ci;
INSERT INTO accounts (name) VALUES ('浜松太郎');
INSERT INTO accounts (name) VALUES ('浜松重複太郎');
INSERT INTO accounts (name) VALUES ('浜松重複太郎');

　次の様になります。

account_id	name
2	浜松重複太郎
3	浜松重複太郎

　これによって冒頭の問題が起きた時も重複を解消すべきレコードを速やかに見つけられます。見つけたら後は削除なり更新なりで重複を解消し、再発防止のためのユニーク制約を付けるだけです。