場(chǎng)景介紹

最近我們對(duì)一個(gè)需求進(jìn)行了改造。在此之前，我們有一個(gè)工單信息表名為bus_mark_info，其中包含一個(gè)配置字段pages。以前，為了方便配置，配置人員直接將多個(gè)頁(yè)面使用逗號(hào)連接后保存，就像是將page1, page2, page3等直接存儲(chǔ)在了該字段中。隨著業(yè)務(wù)的發(fā)展，我們現(xiàn)在需要對(duì)每個(gè)頁(yè)面進(jìn)行單獨(dú)配置，并添加一些其他屬性。為了實(shí)現(xiàn)這一需求，我們?cè)赽us_mark_info表中添加了一個(gè)關(guān)聯(lián)表bus_pages。在上線時(shí)，我們需要將已有的pages字段中配置歷史數(shù)據(jù)的頁(yè)面值使用逗號(hào)進(jìn)行分割，并存入新的表中，然后廢棄掉工單信息表中的pages字段。bus_mark_info表數(shù)據(jù)如下：

查詢SQL 語(yǔ)句編寫

我們首先是將要新增的數(shù)據(jù)查詢出來(lái)，然后使用insert into ... select 遷移到我們的新表中。話不多說(shuō)，我們直接上sql:

SELECT 
 T1.id,
 SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 ) AS page 
FROM
 bus_mark_info T1
 JOIN mysql.help_topic T2 ON T2.help_topic_id < ( length( T1.pages )- length( REPLACE ( T1.pages, ',', '' ))+ 1 ) 
WHERE
 T1.pages IS NOT NULL 
ORDER BY
 T1.id,
 T2.help_topic_id

在這個(gè)sql中，我們使用了mysql 的help_topic表，這個(gè)表存儲(chǔ)的是各種注釋、地址等幫助信息，內(nèi)容如下：

這個(gè)表有一個(gè)特性，就是它有從0開(kāi)始自增為1的id屬性--help_topic_id 并且 擁有固定數(shù)量（701）的數(shù)據(jù)。

• 關(guān)聯(lián)數(shù)據(jù)數(shù)量

原始的bus_mark_info表中的每條數(shù)據(jù)，在與help_topic表關(guān)聯(lián)后會(huì)生成多條新數(shù)據(jù)。具體來(lái)說(shuō)，對(duì)于bus_mark_info表中的每條記錄，我們期望生成的關(guān)聯(lián)數(shù)據(jù)數(shù)量應(yīng)該等于該記錄中pages字段中逗號(hào)的數(shù)量加1。例如，如果某條數(shù)據(jù)的pages字段的取值為page1,page2,page3，那么我們應(yīng)該生成三條關(guān)聯(lián)數(shù)據(jù)。因此，我們的關(guān)聯(lián)條件應(yīng)該是T2.help_topic_id < (length(T1.pages) - length(REPLACE(T1.pages, ',', '')) + 1)。

• 正確分割字段

一旦確保了正確的關(guān)聯(lián)數(shù)據(jù)數(shù)量，我們需要根據(jù)help_topic_id的值來(lái)截取我們的數(shù)據(jù)。例如，當(dāng)help_topic_id為0時(shí)，我們應(yīng)該取pages字段中第一個(gè)逗號(hào)之前的值；當(dāng)help_topic_id為1時(shí)，我們應(yīng)該取pages字段中第一個(gè)逗號(hào)和第二個(gè)逗號(hào)之間的值，依此類推。為實(shí)現(xiàn)這一目標(biāo)，我們將使用兩個(gè)SUBSTRING_INDEX函數(shù)來(lái)進(jìn)行數(shù)據(jù)截取。首先，我們將截取從開(kāi)始位置到help_topic_id+1個(gè)逗號(hào)之前的部分，然后再截取該部分中最后一個(gè)逗號(hào)之后的部分,即SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 )。通過(guò)這樣的處理，我們便成功地利用help_topic_id和SUBSTRING_INDEX函數(shù)完成了數(shù)據(jù)的分割。

• 注意事項(xiàng)

當(dāng)然，我們使用help_topic是因?yàn)樗膆elp_topic_id是從0開(kāi)始，每次遞增1的，我們也可以使用有次特性的別的表或者數(shù)據(jù)代替。 help_topic_id最大值為700，也就是說(shuō)我們這個(gè)sql只能處理pages最多有701個(gè)頁(yè)面連接的數(shù)據(jù)，如果有些pages字段分割之后的數(shù)量大于701，我們則需要使用別的表來(lái)替代。

如果有家人對(duì)SUBSTRING_INDEX函數(shù)和insert into ... select不太熟悉的話可以翻閱下我們歷史的文章，有專門介紹過(guò)。

遷移數(shù)據(jù)sql

遷移數(shù)據(jù)的sql如下：

INSERT INTO bus_pages ( mark_id, page ) SELECT
T1.id,
SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 ) AS page 
FROM
 bus_mark_info T1
 JOIN mysql.help_topic T2 ON T2.help_topic_id < ( length( T1.pages )- length( REPLACE ( T1.pages, ',', '' ))+ 1 ) 
WHERE
 T1.pages IS NOT NULL 
ORDER BY
 T1.id,
 T2.help_topic_id

執(zhí)行后數(shù)據(jù)表如下：

總結(jié)

在實(shí)際開(kāi)發(fā)中，當(dāng)需要對(duì)包含多個(gè)字段連接符的數(shù)據(jù)進(jìn)行查詢與遷移時(shí)，可以使用SQL中的SUBSTRING_INDEX函數(shù)結(jié)合一些輔助表的特性進(jìn)行數(shù)據(jù)分割和遷移。通過(guò)合理的SQL編寫，可以有效處理數(shù)據(jù)關(guān)聯(lián)與拆分，達(dá)到遷移數(shù)據(jù)的目的。

以上就是MySQL中使用逗號(hào)分隔的字段轉(zhuǎn)換為多行數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL字段轉(zhuǎn)多行數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論