MySQL道普請便り

第182回知っておくとちょっと便利なMySQLのfind_in_set関数

皆さんは、find_in_setという関数をご存知でしょうか?MySQLの固有の関数のためあまり馴染みが無いかもしれませんが、ものすごく刺さるときは刺さるという関数になります。

今回は、知らなくてもなんとかなるけど、知っておくとちょっと便利なfind_in_set関数を紹介していきたいと思います。

検証環境

今回はDockerで建てたMySQLを使用します。以下のコマンドでDockerを建てて、ローカルからアクセスをします。

% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
% % mysql -uroot -pmy-secret-pw

執筆時点では、以下の通りMySQL 8.0.28を使用しています。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.01 sec)

今回は以下のようなアンケートを取った場合の回答を、SQLのアンチパターンの1つであるジェイウォーク(1つの列に複数の値を格納)を使って入れた場合を考えます。

回答はYES/NOで回答できるものとして、YESのものだけDBに入れているとします。

mysql> CREATE TABLE survey(id SERIAL, user_id INTEGER, answer_id TEXT);
mysql> INSERT INTO survey(user_id, answer_id) VALUES(1, "1,2,4,5,12");
mysql> INSERT INTO survey(user_id, answer_id) VALUES(2, "11,21,41,14,51,15");
mysql> INSERT INTO survey(user_id, answer_id) VALUES(3, "2");
mysql> INSERT INTO survey(user_id, answer_id) VALUES(4, "1,2"); 
mysql> INSERT INTO survey(user_id, answer_id) VALUES(5, "22");
mysql> select * FROM survey;
+----+---------+-------------------+
| id | user_id | answer_id         |
+----+---------+-------------------+
|  1 |       1 | 1,2,4,5,12        |
|  2 |       2 | 11,21,41,14,51,15 |
|  3 |       3 | 2                 |
|  4 |       4 | 1,2               |
|  5 |       5 | 22                |
+----+---------+-------------------+
5 rows in set (0.01 sec)

id 1では、項番1, 2, 4, 5, 12に関してYESと回答したという想定です。

find_in_setを使わない場合

まずはfind_in_setを使わないで、アンケート結果から回答2を選択した人を抽出してみたいと思います。

一番簡単に考えたら、answer_idでlike文を使って、以下のようなクエリを書いてみれば良さそうに思えます。

mysql> SELECT * FROM survey WHERE answer_id like "%2%";

これを実行すると、以下のような結果になります。困ったことに、id2の21やid5の22が引っかかってしまっていることがわかります。

+----+---------+-------------------+
| id | user_id | answer_id         |
+----+---------+-------------------+
|  1 |       1 | 1,2,4,5,12        |
|  2 |       2 | 11,21,41,14,51,15 |
|  3 |       3 | 2                 |
|  4 |       4 | 1,2               |
|  5 |       5 | 22                |
+----+---------+-------------------+

「回答2」というのをうまく特定するのが、存外難しいことがわかります。

というわけで詳しい説明を省きますが、正しく引くには以下のようになります。

mysql> SELECT * FROM survey WHERE answer_id like "%,2,%" 
UNION SELECT * FROM survey WHERE answer_id like "2,%"
UNION SELECT * FROM survey WHERE answer_id like "%,2"
UNION SELECT * FROM survey WHERE answer_id = "2";

上記のクエリを実行すると、以下のように欲しかった結果が出ます。

+----+---------+------------+
| id | user_id | answer_id  |
+----+---------+------------+
|  1 |       1 | 1,2,4,5,12 |
|  4 |       4 | 1,2        |
|  3 |       3 | 2          |
+----+---------+------------+

とはいえ、何個もパターンがあるので、これをミスなく実行するのは難しいというのは理解してもらえると思います。また、今回は回答2だけを抽出するだけでしたが、2と4を回答した人を探したいとなると、もっと大変になってしまいます。

find_in_set関数を使ってみる

こんなときに便利な関数がfind_in_setになります。公式のドキュメントはこちらになります。

ではさっそく使ってみましょう。find_in_set関数の使い方は左側に検索したい数字を入れて、右側に「,」区切りで繋がれた文字列を入れると、その文字が何番目に入っているか教えてくれる関数になります。いくつか確認してみましょう。

mysql> SELECT find_in_set(1 , "1,11,111");
+-----------------------------+
| find_in_set(1 , "1,11,111") |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.01 sec)

"1,11,111"に1が1番目にあるので1と返ってきます。続いて、11と検索してみたらどうでしょうか?

mysql> SELECT find_in_set(11 , "1,11,111");
+------------------------------+
| find_in_set(11 , "1,11,111") |
+------------------------------+
|                            2 |
+------------------------------+
1 row in set (0.01 sec)

2番目にあるので2と返ってきます。続いて、同じ文字列で0と検索してみたらどうでしょうか?

mysql> SELECT find_in_set(0 , "1,11,111");
+-----------------------------+
| find_in_set(0 , "1,11,111") |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

存在しないので0が返ってきます。複数値がある場合はどうなのか確認してみます。

mysql> SELECT find_in_set(1 , "1,11,111,1");
+-------------------------------+
| find_in_set(1 , "1,11,111,1") |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.01 sec)

1と返ってきていることがわかります。最後に2と"21,12"で比較をしてみます。

mysql> SELECT find_in_set(2 , "21,12");
+--------------------------+
| find_in_set(2 , "21,12") |
+--------------------------+
|                        0 |
+--------------------------+

0になったので、21と2を別物として捉えられていることがわかります。

さて、この関数の何が嬉しいかといいますと、MySQLではFALSEは0でTRUEは0以外となっているのに関係があります。つまりこれをWHERE句に使用すると、含まれているものが検索できるのです。

前述の2が回答されている条件で検索をしてみましょう。

mysql> select * FROM survey WHERE find_in_set(2, answer_id) ;
+----+---------+------------+
| id | user_id | answer_id  |
+----+---------+------------+
|  1 |       1 | 1,2,4,5,12 |
|  3 |       3 | 2          |
|  4 |       4 | 1,2        |
+----+---------+------------+

以前UNIONで頑張って検索した結果と同じ結果が得られていることがわかります。これで非常に便利なことがわかってもらえたと思います。

find_in_setの便利な使い方

うっかりジェイウォークな関数を作ってしまい、それでもなんとかしたいときに便利なのもそのとおりなのですが、これを日常的な作業で便利に扱う方法もあります。たとえば、ユーザidが1と2の人の回答を消したい場合を考えます。前後でSELECTをして変更結果を確認したい場合は、以下のようになります。

SELECT * FROM survey WHERE id in (1, 2);
UPDATE survey SET answer_id = NULL WHERE id in (1,2);
SELECT * FROM survey WHERE id in (1, 2);

上のSQLを実行すると下のような結果が得られます。

mysql> SELECT * FROM survey WHERE id in (1, 2);
+----+---------+-------------------+
| id | user_id | answer_id         |
+----+---------+-------------------+
|  1 |       1 | 1,2,4,5,12        |
|  2 |       2 | 11,21,41,14,51,15 |
+----+---------+-------------------+
2 rows in set (0.02 sec)

mysql> UPDATE survey SET answer_id = NULL WHERE id in (1,2);
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM survey WHERE id in (1, 2);
+----+---------+-----------+
| id | user_id | answer_id |
+----+---------+-----------+
|  1 |       1 | NULL      |
|  2 |       2 | NULL      |
+----+---------+-----------+
2 rows in set (0.00 sec)

さて、今回は2件だったので、コピペミスも何もありませんでしたが、これが複数件になったときにはやっぱりidの中身をミスする可能性があるので、変数に入れたくなると思います。が、残念なことにMySQLではlistを変数に代入できません。そんなときに便利なのが、find_in_setです。

では同様にidが3と4と5の回答を消してしまいましょう。

mysql> SET @list = "3,4,5";

mysql> SELECT * FROM survey WHERE find_in_set(id, @list);
+----+---------+-----------+
| id | user_id | answer_id |
+----+---------+-----------+
|  3 |       3 | 2         |
|  4 |       4 | 1,2       |
|  5 |       5 | 22        |
+----+---------+-----------+

mysql> UPDATE survey SET answer_id = NULL WHERE find_in_set(id, @list);

mysql> SELECT * FROM survey WHERE find_in_set(id, @list);
+----+---------+-----------+
| id | user_id | answer_id |
+----+---------+-----------+
|  3 |       3 | NULL      |
|  4 |       4 | NULL      |
|  5 |       5 | NULL      |
+----+---------+-----------+

"3,4,5"はただの文字列のためSETすることができます。これでコピペミスも減らす事ができるようになります。すごく便利です。

第119回で紹介したgroup_concat()を利用すると、SQLで引いた結果をSETして使用することができるので、さらに便利になります。

まとめ

今回はfind_in_set関数について紹介させていただきました。こちらの関数は標準関数ではないのでMySQL固有ではあるものの、ものすごく刺さるときは刺さると思います。特にSQLアンチパターンの1つであるジェイウォークなカラムがある場合や、変数にSETして利用したい場合に、ものすごく便利に使用することができます。

ただし、indexが使用できないなど副作用があるため、サービスに使用する場合はよく考えてから利用しましょう。

おすすめ記事

記事・ニュース一覧