古いシステムのお守りをする上で String でカンマ区切りでダラダラ1つのフィールドに複数のデータを持っているカラムがよくある。

この aaa,bbb,ccc のようなデータをSQL側で検索する方法。

まず適当にテーブルを作って値を挿入する

DB を作る

CREATE DATABASE IF NOT EXISTS `hogehoge` DEFAULT CHARACTER SET utf8;

Table を作る

CREATE TABLE `piyo` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `age` INT(11) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `friends` VARCHAR(300),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

データを挿入する

INSERT INTO `piyo` VALUES (1,20, 'Terry', 'Joe');
INSERT INTO `piyo` VALUES (2,19, 'Andy', 'Joe');
INSERT INTO `piyo` VALUES (3,20, 'Joe', 'Terry,Andy');
INSERT INTO `piyo` VALUES (4,30, 'Big Bear', 'Terry,Andy,Joe');

OK

SELECT * FROM piyo;
+------+-------+----------+----------------+
|   id |   age | name     | friends        |
|------+-------+----------+----------------|
|    1 |    20 | Terry    | Joe            |
|    2 |    19 | Andy     | Joe            |
|    3 |    20 | Joe      | Terry,Andy     |
|    4 |    30 | Big Bear | Terry,Andy,Joe |
+------+-------+----------+----------------+
4 ROWS IN SET

コンマ区切りフィールド中を検索するには FIND_IN_SET 関数を使う

では Andyを friends 中に持つレコードを検索してみる

SELECT * FROM piyo WHERE FIND_IN_SET('Andy', friends);
+------+-------+----------+----------------+
|   id |   age | name     | friends        |
|------+-------+----------+----------------|
|    3 |    20 | Joe      | Terry,Andy     |
|    4 |    30 | Big Bear | Terry,Andy,Joe |
+------+-------+----------+----------------+
2 ROWS IN SET

取れた。

では次は Joe を friends に持つものを検索してみる。

SELECT * FROM piyo WHERE FIND_IN_SET('Joe', friends);
+------+-------+----------+----------------+
|   id |   age | name     | friends        |
|------+-------+----------+----------------|
|    1 |    20 | Terry    | Joe            |
|    2 |    19 | Andy     | Joe            |
|    4 |    30 | Big Bear | Terry,Andy,Joe |
+------+-------+----------+----------------+
3 ROWS IN SET

取れた。ちゃんと機能するようだ。

では、 Terry と Joe の両方を friends に持つ値を検索してみよう

SELECT * FROM piyo WHERE FIND_IN_SET('Terry', friends) AND FIND_IN_SET('Joe', friends);
+------+-------+----------+----------------+
|   id |   age | name     | friends        |
|------+-------+----------+----------------|
|    4 |    30 | Big Bear | Terry,Andy,Joe |
+------+-------+----------+----------------+
1 ROW IN SET

できた

では FIND_IN_SET はどういう関数なのか

SELECT FIND_IN_SET('Terry', friends) FROM piyo
+---------------------------------+
|   FIND_IN_SET('Terry', friends) |
|---------------------------------|
|                               0 |
|                               0 |
|                               1 |
|                               1 |
+---------------------------------+
4 ROWS IN SET
 
SELECT FIND_IN_SET('Joe', friends) FROM piyo
+-------------------------------+
|   FIND_IN_SET('Joe', friends) |
|-------------------------------|
|                             1 |
|                             1 |
|                             0 |
|                             3 |
+-------------------------------+
4 ROWS IN SET

ということで FIND_IN_SET 関数は引数で取った値の位置を返すというモノになっている。 ヒットしなければゼロになると。ゼロ即ち判定としては FALSE である。