"MySQL" Comma split field row and column conversions

Because many business tables use a design pattern that violates the first paradigm for historical reasons or performance reasons, multiple attribute values are stored in the same column. In this mode, the application often needs to divide the column by delimiter and get the result of the column change:

To build a table statement:

1 DROP Table if EXISTSTbl_name;2 CREATE TABLETbl_name (3Idint( One) not NULLAuto_increment,4UserNamevarchar( -) not NULL,5 PRIMARY KEY(ID)6 )7ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=UTF8;8 9 Insert  intoTbl_nameValues(1,'a,aa,aaa');Ten Insert  intoTbl_nameValues(2,'B,BB'); One Insert  intoTbl_nameValues(3,'C,CC');

Such as:

SQL statements:

1 SELECTA.id,substring_index (Substring_index (A.username,',', b.help_topic_id+1),',',-1) asname2  fromTbl_name A Left JoinMysql.help_topic b3  onb.help_topic_id<(LENGTH (A.username)-LENGTH (REPLACE(A.username,',',"'))+1) 4 ORDER  bya.ID;

Execution Result:

The analysis is as follows:

Length (a.username)-Length (REPLACE(a.username,',', ') ))+1

Indicates the number of rows to be converted into columns after being separated by commas, hereinafter referred to as N;

Loop {Judging by ID: I<=N {Gets the data closest to the first comma, which is Substring_index (Substring_index (A.username,',', b.help_topic_id+1),',',-1) I=I+1} ID=Id+1 }

Summarize:

The disadvantage of this approach is that we need a separate table with sequential numbers (here is incre_table). and the maximum value of a continuous series must be greater than the number of values that match the split. Of course, MySQL also has a ready-made list of contiguous numbers available. If mysql.help_topic:help_topic_id a total of 504 values, generally can meet the majority of demand.

"MySQL" Comma split field row and column conversions