MySQLカンマ分割フィールドの行列変換

テーブルデータ:
ID
Value
1
tiny,small,big
2
small,medium
3
tiny,big
 
期待される結果:
ID
Value
1
tiny
1
small
1
big
2
small
2
medium
3
tiny
3
big
本文:

#      
create table tbl_name (ID int ,mSize varchar(100));
insert into tbl_name values (1,'tiny,small,big');
insert into tbl_name values (2,'small,medium');
insert into tbl_name values (3,'tiny,big');

#        
create table incre_table (AutoIncreID int);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);

 

select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1) 
from 
tbl_name a
join
incre_table b
on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;

 
原理分析:
このjoinの最も基本的な原理はデカルト積である.このようにしてサイクルを実現する.
以下は具体的な問題分析です.
length(a.Size)-length(replace(a.mSize,',')))+1は、カンマで分割した後、持つ数値の数を変更し、以下nと略す
joinプロシージャの擬似コード:
IDによるループ
{
判断:iが<=nであるか否か
{
i番目のカンマに最も近い前のデータ、すなわちsubstring_を取得index(substring_index(a.mSize,',',b.ID),',',-1)
i = i +1 
}
ID = ID +1 
}
 
まとめ:
この方法の欠点は,連続数列を持つ独立したテーブル(ここではincre_table)が必要であることである.また、連続数列の最大値は、分割された値に合致する個数よりも大きくなければならない.
たとえば、1行のmSizeに100個のカンマで分割された値がある場合、incre_tableには少なくとも100個の連続行が必要です.
もちろんmysql内部にも既存の連続数リストがあります.例えばmysql.help_topic: help_topic_idは504個の数値を共有しており、一般的にほとんどのニーズを満たすことができます.
書き換え後は次のようになります.
 

select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1) 
from 
tbl_name a
join
mysql.help_topic b
on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;