皆さんは、find_
今回は、知らなくてもなんとかなるけど、知っておくとちょっと便利なfind_
検証環境
今回は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.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.01 sec)
今回は以下のようなアンケートを取った場合の回答を、SQLのアンチパターンの1つであるジェイウォーク
回答はYES/
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_
一番簡単に考えたら、answer_
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_
ではさっそく使ってみましょう。find_
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_
では同様に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_
まとめ
今回はfind_
ただし、indexが使用できないなど副作用があるため、サービスに使用する場合はよく考えてから利用しましょう。