Skills of mysql column switching of sharing

Foreword:

Because many business tables use design patterns that violate Paradigm 1 for historical or performance reasons. That is, multiple attribute values are stored in the same column (see the following table for specific structure).

In this mode, the application often needs to divide the column according to the separator and get the result of column conversion.

Table data:

ID	Value
1	tiny,small,big
2	small,medium
3	tiny,big

Expected results:

ID	Value
1	tiny
1	small
1	big
2	small
2	medium
3	tiny
3	big

Text:

# Tables to be processed 
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');

# Self-increasing table for circulation 
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;

Principle analysis:

The basic principle of this join is Cartesian product. Loop is realized in this way.

The following is an analysis of specific problems:

length (a. Size)-length (replace (a. mSize, ',', ')) +1 denotes the number of values held by the column after being divided by commas, hereinafter referred to as n

Pseudocode for the join procedure:

Loop according to ID

{

Judge whether i < = n

{

Get the data closest to the comma at i, that is, substring_index (substring_index (a. mSize, ',', b. ID), ',',-1)

i = i +1

}

ID = ID +1

}

Summary:

The disadvantage of this approach is that we need a separate table with successive sequences (here incre_table), and the maximum value 1 of the successive sequences must be greater than the number of values that fit the partition.

For example, if an mSize with 1 row has 100 comma-split values, then our incre_table needs to have at least 100 consecutive rows.

Of course, there is also a ready-made list of continuous numbers available inside mysql. For example, mysql.help_topic: help_topic_id has 504 values, and 1 can meet most requirements.

It is rewritten as follows:

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;