Mysql column switching skills and example sharing

The following editor will bring you an articlemysql Tips on column switching (sharing). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

Foreword:

Because many business tables use the violation method due to historical reasons or performance reasons. A paradigm design pattern. 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 according to the delimiter and obtain 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:

#需要处理的表
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,&#39;,&#39;,&#39;&#39;))+1)
order by a.ID;

Principle analysis:

The most basic principle of this join is the Cartesian product. This is how the loop is implemented.

The following is the specific problem analysis:

length(a.Size) - length(replace(a.mSize,',',' '))+1 means, after splitting by commas, change the number of values ​​owned by the column. The following is referred to as n

The pseudo code of the join process:

Loop based on ID

{
判断：i 是否 <= n
{

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

i = i +1
}
ID = ID +1
}

Summary:

The disadvantage of this method is that we need a separate table with a continuous sequence (here is incre_table). And the maximum value of the continuous sequence must be greater than the number of values ​​that meet the division.

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

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

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,&#39;,&#39;,&#39;&#39;))+1)
order by a.ID;

The above is the detailed content of Mysql column switching skills and example sharing. For more information, please follow other related articles on the PHP Chinese website!