MySQL comma split field row and column conversion test improvements



<p> 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). </p><p> in this mode, the application often needs to divide the column by delimiter and get the result of the column change. </p>

<span class= "cnblogs_code_copy" ></span><p style= "margin:10px auto; line-height:19px; Font-family:verdana, Sans-serif; font-size:13px; " > Table data: </p><table style= "border:1px solid RGB (192, 192, 192); Border-image:none; width:143px; height:142px; Font-family:verdana, Sans-serif; Border-collapse:collapse, "border=" 0 "><tbody><tr><td style=" PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;ID&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;VALUE&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;1&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;TINY,SMALL,BIG&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;2&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;SMALL,MEDIUM&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;3&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " >tiny,big</td></tr></tbody></table><p> </p><p style= "margin:10px Auto line-height:19px; Font-family:verdana, Sans-serif; font-size:13px; " > Expect to get results: </p><table style= "border:1px solid RGB (192, 192, 192); Border-image:none; Font-family:verdana, Sans-serif; Border-collapse:collapse, "border=" 0 "><tbody><tr><td style=" PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " >ID</td><TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;VALUE&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;1&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;TINY&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;1&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;SMALL&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;1&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " >big</td></tr><tr><TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;2&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;SMALL&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;2&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;MEDIUM&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;3&LT;/TD&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;TINY&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD style= "PADDING:3PX; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " &GT;3&LT;/TD&GT;&LT;TD style= "padding:3px; BORDER:1PX Solid RGB (192, 192, 192); Border-image:none; Border-collapse:collapse; " >big</td></tr></tbody></table><p> </p><p></p><pre ><span style= "line-height:1.5;" > #需要处理的表 </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >create</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >table</span> tbl_name (ID <span style= "Color:rgb (0, 0, 255); line-height:1.5;" >int</span>, msize <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >varchar</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >100</span><span style= "line-height:1.5;" >); </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> tbl_name <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >values</span> (&Lt;span style= "Color:rgb (128, 0, 0); line-height:1.5; Font-weight:bold; " >1</span>,<span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' Color:rgb (255, 0, 0); line-height:1.5; " >tiny,small,big</span><span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' line-height:1.5; " >); </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> tbl_name <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >values</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >2</span>,<span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' Color:rgb (255, 0, 0); line-height:1.5; " >small,medium</span><span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' line-height:1.5;">); </span><span style=" Color:rgb (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> tbl_name <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >values</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >3</span>,<span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' Color:rgb (255, 0, 0); line-height:1.5; " >tiny,big</span><span style= "Color:rgb (255, 0, 0); line-height:1.5; " > ' </span><span style= ' line-height:1.5; " > #用于循环的自增表 </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >create</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >table</span> incre_table (Autoincreid <span style= "Color:rgb (0, 0, 255); line-height:1.5;" >int</span><span style= "line-height:1.5;" >); </span><span style= "Color:rGB (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> incre_table <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >values</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >1</span><span style= "line-height:1.5;" >); </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> incre_table <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >values</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >2</span><span style= "line-height:1.5;" >); </span><span style= "Color:rgb (0, 0, 255); line-height:1.5; " >insert</span> <span style= "Color:rgb (0, 0, 255); line-height:1.5; " >into</span> incre_table <span style= "Color:rgb (0, 0, 255); Line-height: 1.5; " >values</span> (<span style= "Color:rgb (0, 0); line-height:1.5; font-weight:bold;" >3</span>);

SelectA.id,substring_index (Substring_index (A.msize,',', B.autoincreid),',',-1)  fromTbl_name aJoinincre_table b onB.autoincreid<=(Length (a.msize)-LengthReplace(A.msize,',',"'))+1)Order  bya.ID;

Principle Analysis:

The most basic principle of this join is the Cartesian product. This is the way to implement loops.

The following is a specific problem analysis:

Length (a.size)-Length (replace (a.msize, ', ', ')) +1 indicates the number of values to be changed after the comma is divided, hereinafter referred to as n

SelectA.id,substring_index (Substring_index (A.msize,',', B.autoincreid),',',-1)  fromTbl_name aJoinincre_table b onB.autoincreid<=(Length (a.msize)-LengthReplace(A.msize,',',"'))+1)Order  bya.ID;

Principle Analysis:

The most basic principle of this join is the Cartesian product. This is the way to implement loops.

The following is a specific problem analysis:

Length (a.size)-Length (replace (a.msize, ', ', ')) +1 indicates the number of values to be changed after the comma is divided, hereinafter referred to as n

Pseudo-code for the join procedure:

Loop based on ID

{

Judging: I whether <= n

{

Get the data closest to the first comma, i.e. 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 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:

SelectA.id,substring_index (Substring_index (A.msize,',', b.help_topic_id+1),',',-1)  fromTbl_name aJoinMysql.help_topic b onb.help_topic_id<(Length (a.msize)-LengthReplace(A.msize,',',"'))+1)Order  bya.ID;

Test Example:

</pre><pre class= "SQL" name= "code" >--select help_topic_id from mysql.help_topic--eg. use a field "," Separate combination Select Group_concat (user_id order by user_id ASC) as NIDs from Admin_userselect B.did,group_concat (b.sid order by adj Ustment desc,similar DESC) from Test b GROUP by B.did-1. If multiple shopping guide with 1 single decomposition--Add time period select A.djbh,a.je,substring_index (Substri Ng_index (a.dgy_list_id, ', ', b.help_topic_id+1), ', ', -1) from Ipos_qtlsd ajoinmysql.help_topic Bon b.help_topic_id < (Length (a.dgy_list_id)-Length (replace (a.dgy_list_id, ', ', ')) +1) and a.djbh= ' bp0102_qtsy000070 ' ORDER by a.djbh;--2. Averaging--SELECT help_topic_id from mysql.help_topic--1. If more than one shopping guide with 1 single first decomposition-@zddm--@ rqselect A.djbh,substring_index (substr Ing_index (a.dgy_list_id, ', ', b.help_topic_id+1), ', ', -1) as Fjid,substring_index (Substring_index (A.DGY_LIST_MC, ', ' , b.help_topic_id+1), ', ', -1) as Fjmc,format (a.je/(Length (a.dgy_list_id)-Length (replace (a.dgy_list_id, ', ', ')) +1), 2) as Fjje,je from Ipos_qtlsd ajoinmysql.help_topic bon b.help_topic_id < (length (a.dgy_list_id)-Length (replace (a.dgy_list_id, ', ', '))) +1) and A.rq between Unix_timestamp (' 2016-04-01 ') and Unix_timestamp (' 2016-05-01 ') and a.djbh= ' gd_151125000001 ' ORDER by a.djbh;--gd_151125000001--3. The decomposed indicator--SELECT help_topic_id from mysql.help_topic--1. If multiple shopping guide with 1 single first decomposition--@khdm_change terminal code--@start_time start time--@end_tim E End time--SELECT * from IPOS_QTLSD WHERE djbh= ' gd_151125000001 ' Set @khdm_change = ' BP0102 '; set @start_time =unix_timestamp ( ' 2016-04-01 '); set @end_time =unix_timestamp (' 2016-05-01 '); Select Fjid,fjmc,sum (Fjje) from (select A.zddm,a.zdmc,a.djbh, Substring_index (Substring_index (a.dgy_list_id, ', ', b.help_topic_id+1), ', ', -1) as Fjid, Substring_index (Substring_index (A.DGY_LIST_MC, ', ', b.help_topic_id+1), ', ',-1) As FJMC, FORMAT (a.je/(Length (a.dgy_list_id)-Length (replace (a.dgy_list_id, ', ', ')) +1), 2) as Fjje, je from IPOS_QTLSD a J Oin mysql.help_topic B on b.help_topic_id < (length (a.dgy_list_id)-Length (replace (a.dgy_list_id, ', ', ')) +1) and A.rq between @start_time and @end_timeand a.zd_id= (SELECT ID from Com_base_kehu where [Email protected]_change)) Aagroup by Fjid, fjmc--and A.djbh= ' gd_151125000001 '--order by A.DJBH;



MySQL comma split field row and column conversion test improvements