MySQLでカンマ区切りの文字列から特定の単語を削除する方法

MySQLでは、SET型やカンマ区切りの文字列として保存されたデータから、特定の単語（要素）を削除したいケースがあります。本記事では、ビット演算とFIND_IN_SET関数を組み合わせることで、カンマ区切り文字列内の特定の単語を効率的に削除する方法を解説します。

1. サンプルテーブルの作成

まず、SET型のカラムを持つテーブルを作成します。

mysql> create table DemoTable836(FirstName SET('John','Chris','Adam'));
Query OK, 0 rows affected (0.60 sec)

2. テストデータの挿入

INSERT文を使って、いくつかのレコードを登録します。

mysql> insert into DemoTable836 values('John,Chris');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable836 values('John,Chris,Adam');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable836 values('Chris,Adam');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable836 values('John,Adam');
Query OK, 1 row affected (0.37 sec)

3. 登録データの確認

SELECT文で全レコードを表示してみましょう。

mysql> select *from DemoTable836;

実行結果は以下の通りです。

+-----------------+
| FirstName       |
+-----------------+
| John,Chris      |
| John,Chris,Adam |
| Chris,Adam      |
| John,Adam       |
+-----------------+
4 rows in set (0.00 sec)

4. 特定の単語を削除するUPDATE文

ここで「Chris」という単語を削除するには、次のようなUPDATE文を実行します。

mysql> update DemoTable836 set FirstName = FirstName &~ (1 << FIND_IN_SET('Chris', FirstName) - 1);
Query OK, 3 rows affected (0.21 sec)
Rows matched: 4 Changed: 3 Warnings: 0

ポイント： SET型の値は内部的にビットフラグとして管理されています。FIND_IN_SET関数で対象の単語が何番目の要素かを特定し、その位置に対応するビットを「&~（AND NOT）」演算でオフにすることで、該当する単語だけを文字列から取り除くことができます。

5. 削除後の結果確認

再度テーブルの内容を確認してみます。

mysql> select *from DemoTable836;

実行結果は以下の通りです。「Chris」がすべての行から削除されていることがわかります。

+-----------+
| FirstName |
+-----------+
| John      |
| John,Adam |
| Adam      |
| John,Adam |
+-----------+
4 rows in set (0.00 sec)

まとめ

このように、SET型カラムに対してはFIND_IN_SETとビット演算を組み合わせることで、SQL一文だけでカンマ区切り文字列から特定の単語を簡単に削除できます。通常のVARCHAR型のカンマ区切り文字列の場合は、REPLACE関数やアプリケーション側での文字列処理を検討するとよいでしょう。