MySQL comma split field column career

Objective:

Because many business tables use a design pattern that violates the first paradigm for historical reasons or for performance reasons. That is, multiple attribute values are stored in the same column (see the following table for specific structures).

In this mode, the application often needs to divide the column by delimiter and get the result of the column change.

Table data:

Id	Value
1	Tiny,small,big
2	Small,medium
3	Tiny,big

Expect to get results:

Id	Value
1	Tiny
1	Small
1	Big
2	Small
2	Medium
3	Tiny
3	Big

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.

For example, if a row of msize has 100 comma-separated values, then our incre_table needs to have at least 100 contiguous rows.

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.

Rewrite as follows:

#需要处理的表create table Tbl_name (ID int, msize varchar), insert into tbl_name values (1, ' tiny,small,big ') and insert into TBL _name values (2, ' small,medium '); insert into tbl_name values (3, ' tiny,big '); #SQLselect A.id,substring_index (substring_ Index (A.msize, ', ', b.help_topic_id+1), ', ', -1) from Tbl_name ajoinmysql.help_topic bon b.help_topic_id < (length ( A.msize)-Length (replace (a.msize, ', ', ')) +1) Order by a.ID;

MySQL comma split field column career