GROUP_CONCATの反対をやりたい（MySQLパフォーマンス無視）

MySQLにはgroup_concatという神関数がある。
例えば…

create table users_jobs (
  user_id int,
  job_id char(2)
);
insert into users_jobs values(1, 'A');
insert into users_jobs values(1, 'B');
insert into users_jobs values(2, 'A');
insert into users_jobs values(3, 'B');
insert into users_jobs values(3, 'C');
insert into users_jobs values(3, 'D');

このテーブルを普通にSELECT

select user_id, job_id from users_jobs order by user_id, job_id;

user_id	job_id
1	A
1	B
2	A
3	B
3	C
3	D

group_concatを使ってSELECT

select user_id, group_concat(job_id order by job_id) from users_jobs group by user_id order by user_id;

user_id	group_concat(job_id order by job_id)
1	A,B
2	A
3	B,C,D

うん実に見やすいですね。

この逆をやりたい。つまり

create table user_jobs_unko (
  user_id int,
  job_ids char(30)
);
insert into user_jobs_unko values(1, 'A,B');
insert into user_jobs_unko values(2, 'A');
insert into user_jobs_unko values(3, 'B,C,D');

こういうテーブルを正規化したいわけです。
普通にSELECT

select user_id, job_ids from user_jobs_unko order by user_id;

user_id	job_ids
1	A,B
2	A
3	B,C,D

こうなった。超長いうえにパフォーマンスが悪そう

select
  unko.user_id,
  min(seq.idx) as idx,
  substring_index(substring_index(unko.job_ids, ',', seq.idx), ',', -1) as job_id
from
  user_jobs_unko unko
  cross join (
    select
      @idx := 1 as idx
    union
    select
      @idx := @idx + 1 as idx
    from
      information_schema.COLUMNS
    limit
      15
  ) seq
group by
  unko.user_id,
  job_id
order by
  unko.user_id,
  idx;

user_id	idx	job_id
1	1	A
1	2	B
2	1	A
3	1	B
3	2	C
3	3	D

ヤッター

解説

select
  unko.user_id,
  unko.job_ids,
  seq.idx,
  substring_index(unko.job_ids, ',', seq.idx),
  substring_index(substring_index(unko.job_ids, ',', seq.idx), ',', -1) as job_id
from
  user_jobs_unko unko
  cross join (
    select
      @idx := 1 as idx
    union
    select
      @idx := @idx + 1 as idx
    from
      information_schema.COLUMNS
    limit
      15
  ) seq
order by
  unko.user_id,
  seq.idx;

このSQLを流せば理解できるでしょう

あとこういう方法もあるみたい
↓は多分パフォーマンスがマシ、↑は順番が維持される
【MySQL】カンマ区切りのIDのリストを、マスタテーブルで定義されている日本語名にフォーマットして出力する不思議なSQL | バシャログ。