【MySQL】空白を含むレコードをDISTINCTで正しく重複排除する方法（REPLACE関数活用）

MySQLのDISTINCTは、見た目が同じでも前後に空白（スペース）が含まれているレコードを別々の値として扱ってしまいます。例えば「John」「John 」（末尾に空白）「 John 」（前後に空白）は、それぞれ異なるデータとして認識されるため、意図したとおりに重複を除去できません。

この問題を解決するには、REPLACE関数を使って空白を取り除いた上でDISTINCTを適用します。基本構文は以下のとおりです。

SELECT DISTINCT REPLACE(カラム名, ' ', '') FROM テーブル名;

サンプルテーブルの作成

まず、動作確認用のテーブルを作成しましょう。

mysql> CREATE TABLE DemoTable
(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(20)
);
Query OK, 0 rows affected (0.63 sec)

テストデータの挿入

次に、INSERTコマンドを使って空白の有無が異なるレコードを挿入します。

mysql> INSERT INTO DemoTable(Name) VALUES('John ');
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO DemoTable(Name) VALUES(' John ');
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO DemoTable(Name) VALUES('John');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO DemoTable(Name) VALUES('Sam');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO DemoTable(Name) VALUES('Carol');
Query OK, 1 row affected (0.22 sec)
mysql> INSERT INTO DemoTable(Name) VALUES(' Sam');
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO DemoTable(Name) VALUES('Mike ');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable(Name) VALUES('David');
Query OK, 1 row affected (0.17 sec)

登録された全レコードの確認

SELECT文ですべてのレコードを表示してみます。

mysql> SELECT * FROM DemoTable;

実行結果は以下のとおりです。同じ名前でも空白の位置によって複数行に分かれています。

+----+-----------+
| Id | Name      |
+----+-----------+
| 1  | John      |
| 2  | John      |
| 3  | John      |
| 4  | Sam       |
| 5  | Carol     |
| 6  | Sam       |
| 7  | Mike      |
| 8  | David     |
+----+-----------+
8 rows in set (0.00 sec)

REPLACE関数とDISTINCTを組み合わせたクエリ

ここで本題のクエリです。REPLACE関数で空白を削除してからDISTINCTを適用することで、空白の違いを無視した重複排除が可能になります。

mysql> SELECT DISTINCT REPLACE(Name, ' ', '') FROM DemoTable;

実行結果：

+----------------------+
| replace(Name,' ','') |
+----------------------+
| John                 |
| Sam                  |
| Carol                |
| Mike                 |
| David                |
+----------------------+
5 rows in set (0.00 sec)

8件のレコードから、空白を除いた実際の名前は5種類であることが確認できました。「John」の3つのバリエーションや「Sam」の2つのバリエーションが、それぞれ1つにまとめられています。

補足：TRIM関数との違い

なお、先頭と末尾の空白だけを除去したい場合はTRIM関数を使う方法もあります。

SELECT DISTINCT TRIM(Name) FROM DemoTable;

REPLACEは文字列中のすべての空白を削除するのに対し、TRIMは先頭と末尾のみを対象とします。名前に途中のスペースを含みたいケース（例：「John Smith」）ではTRIMの方が適しているので、用途に応じて使い分けるとよいでしょう。