【MySQL】カンマ区切りのIDのリストを、マスタテーブルで定義されている日本語名にフォーマットして出力する不思議なSQL

DB&サーバ

2015.09.29

fukasawaです。こんにちは。記事のタイトルってどのくらいの長さまでなら許されるのでしょうか。

現在携わっているプロジェクトでは、本番環境で発行したSQLの履歴を残す運用になっています。過去に発行されたSQLを見る機会も多いのですが、履歴の中であまり見慣れない書き方のSQLを見つけたので、勉強も兼ねて動きを追ってみました。
「不思議な」と書いてますが、主観なので一般的に不思議なのかどうかはよくわからないです。

※MySQL 5.6.21 で検証

1. SQLについて

今回調べるSQLを実行するために、サンプルテーブルを用意しました。

■mst_animals

■owners

ownersのpetsというカラムに、飼っている動物のID（mst_animalsテーブルのID）をカンマ区切りで格納しています。

■実行するSQL


SELECT
  o.id,
  o.name,
  group_concat(ma.name SEPARATOR '、') AS pets_ja
FROM owners AS o
  LEFT JOIN mst_animals AS ma ON o.pets REGEXP CONCAT("(^|,)", ma.id, "(,|$)")
GROUP BY o.id;

【出力結果】

petsに格納されていた「,」区切りのIDのリストが、「、」区切りの日本語名に変換されて出力されていることがわかります。このSQLの動きを追っていきます。

2. 使われている関数・演算子について

SQLの中で使用されている関数・演算子について見ていきます。

GROUP_CONCAT

GROUP_CONCAT(expr)

GROUP BY 句でグループ化された複数の行をひとつにまとめ、指定の文字（SEPARATOR）で連結した文字列として返します。

■foods

■使用例


SELECT
  id,
  genre,
  group_concat(name SEPARATOR '、') AS food_list
FROM foods
GROUP BY genre;

【出力結果】

genreごとにグループ化され、食べ物の名前が「、」区切りで連結されていることがわかります。

※DISTINCT 句で重複を排除したり、ORDER BY 句で連結順を変えたりすることも可能です。（詳細はマニュアルをご参照ください。）

CONCAT

CONCAT(str1,str2,...)

引数で渡された値を連結した文字列を返します。

■使用例


SELECT concat(name, 'は', genre, 'です。') AS FROM foods;

【出力結果】

REGEXP

正規表現演算子

正規表現のパターンで検索条件を指定することができます。

■使用例


SELECT
  id,
  name,
  pets
FROM owners
WHERE pets REGEXP '^.*(1).*$';

【出力結果】

petsに「1」を含むレコードを出力しています。

3. SQLの動作について

使用されている関数・演算子がどのような動きをするかを確認したところで、SQLの動作を詳しく見てみます。

CONCAT("(^|,)", ma.id, "(,|$)")

まず、この部分なのですがテーブルを結合する際に使用する正規表現のパターンを生成しています。


SELECT CONCAT("(^|,)", ma.id, "(,|$)")
FROM mst_animals AS ma;

↓のような文字列を生成しています。

こちらの正規表現を使用して、ownersテーブルとmst_animalsテーブルを結合します。
petsに「1,2,4」が含まれているownersのレコードに、mst_animalsのidが「1」「2」「4」の3つのレコードが結合される、という感じで結合します。わかりやすいように、一度 GROUP BY 句を省きグループ化しない状態で出力してみます。


SELECT
  o.id    AS o_id,
  o.name  AS o_name,
  o.pets  AS o_pets,
  ma.id   AS ma_id,
  ma.name AS ma_name
FROM owners AS o
  LEFT JOIN mst_animals AS ma ON o.pets REGEXP CONCAT("(^|,)", ma.id, "(,|$)")
ORDER BY o.id, ma.id;

【出力結果】

petsカラムに「1,2,4」が設定されている鈴木さんのデータに、mst_animalsで定義された「1 犬」「2 猫」「4 キリン」のレコードが結合されていることが確認できます。

ここまできたら、あとはownersのidでグループ化を行いオーナーごとにレコードをひとつにまとめ、結合されている動物の名前をGROUP_CONCATで連結してあげるだけです。


SELECT
  o.id,
  o.name,
  group_concat(ma.name SEPARATOR '、') AS pets_ja
FROM owners AS o
  LEFT JOIN mst_animals AS ma ON o.pets REGEXP CONCAT("(^|,)", ma.id, "(,|$)")
GROUP BY o.id;

petsに格納されていたカンマ区切りのIDのリストを「、」区切りの日本語名に変換することができました。