MySQLでタグ機能を実装しAND検索する

商品(item)にN個のタグを設定し、tag名(tag id)で商品をAND検索できる機能を実装したい。

table design

以下の3つのテーブルを用意する

  • item: 商品テーブル。タグの設定対象
  • item_to_tag: itemとtagの中間テーブル
  • tag: タグテーブル

f:id:tic40:20170225133709p:plain

item table

mysql> select * from item;
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
|  3 | item3 |
+----+-------+

item_to_tag table(中間テーブル)

mysql> select * from item_to_tag;
+----+---------+--------+
| id | item_id | tag_id |
+----+---------+--------+
|  1 |       1 |      1 |
|  2 |       1 |      2 |
|  3 |       1 |      3 |
|  4 |       1 |      4 |
|  5 |       2 |      4 |
|  6 |       2 |      5 |
|  7 |       3 |      1 |
|  8 |       3 |      6 |
+----+---------+--------+

tag table

mysql> select * from tag;
+----+------+
| id | name |
+----+------+
|  1 | tag1 |
|  2 | tag2 |
|  3 | tag3 |
|  4 | tag4 |
|  5 | tag5 |
|  6 | tag6 |
+----+------+

AND検索

‘tag4'かつ'tag5'が紐付けられているitemを検索する(item.nameはitem.idに置き換えてもよい)

mysql> SELECT item.*
    -> FROM item
    -> JOIN item_to_tag AS itt ON item.id = itt.item_id
    -> JOIN tag ON itt.tag_id = tag.id
    -> WHERE tag.name IN ('tag4','tag5')
    -> GROUP BY item.id
    -> HAVING COUNT(item.id) = 2;
+----+-------+
| id | name  |
+----+-------+
|  2 | item2 |
+----+-------+

AND検索(group_concat と FIND_IN_SET を使う)

mysql> SELECT * FROM item
    -> WHERE EXISTS (
    -> SELECT group_concat(tag_tmp.name) AS tags
    -> FROM tag AS tag_tmp
    -> JOIN item_to_tag AS itt ON tag_tmp.id = itt.tag_id
    -> WHERE item.id = itt.item_id
    -> HAVING FIND_IN_SET('tag4', tags) AND FIND_IN_SET('tag5', tags)
    -> );
+----+-------+
| id | name  |
+----+-------+
|  2 | item2 |
+----+-------+

まとめ

  • 案外素直にAND検索のsqlが書けなかった。もっと良い方法があるかもしれない。

SQLアンチパターン

SQLアンチパターン

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版