【MySQL】カンマ区切りになっているテキストのカラムを縦持ちにする

MySQL 限定テクニック。

項目がいくつあるか事前にわかっていれば、SUBSTRING_INDEX 関数を使って縦持ちにできる。 項目数が不定だとたぶんつらい。(試してない)

例として、下のテーブル favoritesfavorites カラムに、 カンマ区切りで好きな言語が3つ入っているとする。

CREATE TABLE favorites (
    name VARCHAR(16) PRIMARY KEY
  , favorites VARCHAR(255) NOT NULL
);

中身は下のとおり。

name favorites
太郎 C,Rust,Scheme
花子 Ruby,Python,APL

とりあえずインラインビューで 1 .. 3 までの補助テーブル L (loop のつもり) をつくっておいて、直積をとる。

結果の6行に対して、SUBSTRING_INDEX を適用して分割した値を得ることができる。

SELECT  name
      , SUBSTRING_INDEX(SUBSTRING_INDEX(favorites, ",", L.`index`), ",", -1) AS favorite
   FROM favorites CROSS JOIN (
           SELECT 1 AS `index`
     UNION SELECT 2
     UNION SELECT 3
  ) L
 ORDER BY name
;

かくして、縦持ちの結果が得られる。

name favorite
太郎 C
太郎 Rust
太郎 Scheme
花子 Ruby
花子 Python
花子 APL