MySQL逗號分割欄位的行列轉換測試改進

標籤：



<p>由於很多業務表因為曆史原因或者效能原因，都使用了違反第一範式的設計模式。即同一個列中儲存了多個屬性值（具體結構見下表）。</p><p>這種模式下，應用常常需要將這個列依據分隔字元進行分割，並得到列轉行的結果。</p>

<span class="cnblogs_code_copy"></span><p style="margin: 10px auto; line-height: 19px; font-family: verdana, sans-serif; font-size: 13px;">表資料：</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;">ID</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">Value</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">1</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">tiny,small,big</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">2</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">small,medium</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">3</td><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;">期望得到結果：</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;">Value</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">1</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">tiny</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">1</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">small</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">1</td><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;">2</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">small</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">2</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">medium</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">3</td><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">tiny</td></tr><tr><td style="padding: 3px; border: 1px solid rgb(192, 192, 192); border-image: none; border-collapse: collapse;">3</td><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(128, 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> (<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(128, 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(128, 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(128, 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(128, 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(128, 0, 0); line-height: 1.5; font-weight: bold;">3</span>);

 

select a.ID,substring_index(substring_index(a.mSize,‘,‘,b.AutoIncreID),‘,‘,-1) from tbl_name ajoinincre_table bon b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,‘,‘,‘‘))+1)order by a.ID;

原理分析：

這個join最基本原理是笛卡爾積。通過這個方式來實現迴圈。

以下是具體問題分析：

length(a.Size) - length(replace(a.mSize,‘,‘,‘‘))+1  表示了，按照逗號分割後，改列擁有的數值數量，下面簡稱n

select a.ID,substring_index(substring_index(a.mSize,‘,‘,b.AutoIncreID),‘,‘,-1) from tbl_name ajoinincre_table bon b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,‘,‘,‘‘))+1)order by a.ID;

原理分析：

這個join最基本原理是笛卡爾積。通過這個方式來實現迴圈。

以下是具體問題分析：

length(a.Size) - length(replace(a.mSize,‘,‘,‘‘))+1  表示了，按照逗號分割後，改列擁有的數值數量，下面簡稱n

join過程的虛擬碼：

根據ID進行迴圈

{

判斷：i 是否 <= n

{

擷取最靠近第 i 個逗號之前的資料， 即 substring_index(substring_index(a.mSize,‘,‘,b.ID),‘,‘,-1)

i = i +1 

}

ID = ID +1 

}

總結：

這種方法的缺點在於，我們需要一個擁有連續數列的獨立表（這裡是incre_table)。並且連續數列的最大值一定要大於符合分割的值的個數。

例如有一行的mSize 有100個逗號分割的值，那麼我們的incre_table 就需要有至少100個連續行。

當然，mysql內部也有現成的連續數列表可用。如mysql.help_topic： help_topic_id 共有504個數值，一般能滿足於大部分需求了。

改寫後如下:

select 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;

測試執行個體：

</pre><pre class="sql" name="code">-- SELECT  help_topic_id  FROM mysql.help_topic-- eg.把一個欄位用“，”分隔開組合select group_concat(user_id ORDER BY user_id ASC) as nids from admin_userSELECT b.did,GROUP_CONCAT(b.sid ORDER BY adjustment DESC,similar DESC) FROM test b GROUP BY b.did -- 1.如果多個導購同１張單的先分解-- 加時間段select a.DJBH,a.je,substring_index(substring_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.取平均值-- SELECT  help_topic_id  FROM mysql.help_topic-- 1.如果多個導購同１張單的先分解-- @zddm-- @ rqselect 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  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.分解後的指標-- SELECT  help_topic_id  FROM mysql.help_topic-- 1.如果多個導購同１張單的先分解-- @khdm_change 終端代碼-- @start_time　開始時間-- @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 join 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逗號分割欄位的行列轉換測試改進