[MySQL] LIKEとINを組み合わせて使いたい場合はREGEXPとGROUP_CONCATとCONCATを使う

MySQLで、以下のような検索がしたい。

• あるフィールドの値が、他のテーブルからの検索結果の値から始まる

サンプルデータ

商品マスター(items)

name	item_code	type
あんぱん	abc01	パン
ジャムぱん	def02	パン
カレーぱん	ghy03	パン
梅干しおにぎり	qwe01	ごはん
こんぶおにぎり	asd02	ごはん
鮭おにぎり	zxc03	ごはん

注文(orders)

ID	order_code（商品コード-価格）
order01	abc01-80
order02	abc01-160
order03	ghy03-120
order04	zxc03-100
order05	asd02-400
order06	qwe01-240

検索要求

注文から、以下の条件を満たすレコードを抽出したい。

• 注文コードの値が、「商品マスター」において「パン」の分類に該当する商品コードから始まる

IN と LIKE を組み合わせたい

パッと考えると以下のようなSQLを書きたくなる。

SELECT *
FROM orders
WHERE order_code LIKE IN ('abc01-%', 'def02-%', 'ghy03-%');

しかし、LIKEとINを同時に使うことはできない。

手法

検索条件に正規表現を使う

この場合、正規表現で検索条件を作れる REGEXP を利用することができる。

SELECT *
FROM orders
WHERE order_code REGEXP ('(^abc01-.*)|(^def02-.*)|(^ghy03-.*)');

しかし本来 (^abc01-.*)|(^def02-.*)|(^ghy03-.*) の箇所は別の検索結果から導き出さないといけない。

そこで、サブクエリを使って検索結果から正規表現文字列を作成する。

CONCAT()

• CONCAT( 文字列1, 文字列2, ・・・文字列N ) で、各引数の文字列を連結したひとつの文字列を取得できる。
• CONCAT( 文字列1, (SELECTサブクエリ), 文字列2) で、「文字列1サブクエリの結果文字列2」というふうな取得もできる。

SELECT CONCAT('あん', 'ぱん');

↓
あんぱん

GROUP_CONCAT()

• GROUP_CONCAT(フィールド名 SEPARATOR 連結文字列) FROM TABLE_NAME WHERE 検索条件
  で、検索結果を連結文字列でつなげた文字列を得ることができる。

SELECT GROUP_CONCAT(item_code SEPARATOR ',')
FROM items
WHERE type = 'パン';

↓
abc01,def02,ghy03

CONCAT() と GROUP_CONCAT() とサブクエリを使って正規表現文字列を作る

SELECT CONCAT('(^', (
  SELECT GROUP_CONCAT(item_code SEPARATOR '-.*)|(^')
  FROM items
  WHERE type = 'パン';
), '-.*)');

↓
(^abc01-.*)|(^def02-.*)|(^ghy03-.*)

正規表現での検索と組み合わせる

SELECT *
FROM orders
WHERE order_code REGEXP (

  SELECT CONCAT('(^', (
    SELECT GROUP_CONCAT(item_code SEPARATOR '-.*)|(^')
    FROM items
    WHERE type = 'パン';
  ), '-.*)')

);

こんな感じで検索できるようです。