FIND_IN_SET 便利っぽい

find_in_set.sql

mysql> STATUS
--------------
mysql  Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1

Connection id:          1
Current database:       hogefugapiyo
Current user:           spamspamspam@localhost
SSL:                    Not in use
Current pager:          /usr/bin/less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.27-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysql.sock
Uptime:                 1 hour 51 min 24 sec

Threads: 1  Questions: 56  Slow queries: 0  Opens: 36  Flush tables: 1  Open tables: 27  Queries per second avg: 0.008
--------------

mysql> -- 検証用一時テーブルの作成
mysql> CREATE TEMPORARY TABLE hoge ( fuga VARCHAR( 256 ) ) ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO hoge VALUES ( 'foo' ) , ( 'bar' ) , ( 'buz' ) , ( 'spam' ) , ( 'ham' ) ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from hoge ;
+------+
| fuga |
+------+
| foo  |
| bar  |
| buz  |
| spam |
| ham  |
+------+
5 rows in set (0.00 sec)

mysql> -- 検索対象の文言、カンマ区切りでリストアップ
mysql> SET @foos = 'foo,bar,buz' ;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 検索
mysql> SELECT * FROM hoge WHERE FIND_IN_SET( fuga , @foos ) ;
+------+
| fuga |
+------+
| foo  |
| bar  |
| buz  |
+------+
3 rows in set (0.00 sec)