MySQL column to row conversion tips (share)

Preface:

Because many business tables use design patterns that violate the first paradigm due to historical or performance reasons. That is, multiple attribute values ​​are stored in the same column (see the table below for the specific structure).

In this mode, applications often need to split the column based on the delimiter and obtain the result of column-to-row conversion.

Table data:

Expected results:

text:

#The table 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');

#AutoIncrement table for loop 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 most basic principle of this join is the Cartesian product. This is how the cycle is achieved.

The following is an analysis of the specific issues:

length(a.Size) - length(replace(a.mSize,',',''))+1 indicates the number of values ​​in the column after comma separation, referred to as n below.

Pseudo code of the join process:

Looping by ID

{

Judgment: whether i <= n

{

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

i = i +1

}

ID = ID +1

}

Summarize:

The disadvantage of this approach is that we need a separate table (here increase_table) with the consecutive columns. And the maximum value of the continuous sequence must be greater than the number of values ​​that meet the segmentation requirements.

For example, if there is a row whose mSize has 100 comma-separated values, then our increase_table needs to have at least 100 consecutive rows.

Of course, there are also ready-made lists of consecutive numbers available in MySQL. For example, mysql.help_topic: help_topic_id has a total of 504 values, which can generally meet most needs.

After rewriting, it is 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;

The above MySQL column to row conversion tips (sharing) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.