場景介紹

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

查詢SQL 語句編寫

我們首先是將要新增的數(shù)據(jù)查詢出來，然后使用insert into ... select 遷移到我們的新表中。話不多說，我們直接上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

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

這個表有一個特性，就是它有從0開始自增為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)后會生成多條新數(shù)據(jù)。具體來說，對于bus_mark_info表中的每條記錄，我們期望生成的關(guān)聯(lián)數(shù)據(jù)數(shù)量應(yīng)該等于該記錄中pages字段中逗號的數(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的值來截取我們的數(shù)據(jù)。例如，當(dāng)help_topic_id為0時，我們應(yīng)該取pages字段中第一個逗號之前的值；當(dāng)help_topic_id為1時，我們應(yīng)該取pages字段中第一個逗號和第二個逗號之間的值，依此類推。為實現(xiàn)這一目標(biāo)，我們將使用兩個SUBSTRING_INDEX函數(shù)來進行數(shù)據(jù)截取。首先，我們將截取從開始位置到help_topic_id+1個逗號之前的部分，然后再截取該部分中最后一個逗號之后的部分,即SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 )。通過這樣的處理，我們便成功地利用help_topic_id和SUBSTRING_INDEX函數(shù)完成了數(shù)據(jù)的分割。

• 注意事項

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

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

遷移數(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é)

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

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

最新評論