當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql分隔字符串转变为多行_【mysql】将以固定分隔符分隔的字符串转成多行的形式...

發布時間：2024/10/8 数据库 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql分隔字符串转变为多行_【mysql】将以固定分隔符分隔的字符串转成多行的形式... 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

背景

近期在做用戶標簽項目，目前標簽的存儲是用戶id，標簽ids(每個標簽以,形式存儲) 的形式，但是如果想統計前后兩天標簽的變化，使用find_in_set函數，一方面查詢速度慢(因為不能使用索引)，另一方面目前標簽已有將近300多個，以后還會更多，一個標簽一個標簽的寫，使得sql特別長。

目前采取的策略是：將用戶標簽表拆分成用戶id，標簽id的形式。這樣將前后兩天的表關聯，就能查出昨天到今天有那些標簽離開，哪些標簽進來。

那么采用上述策略就需要研究怎么將用戶id，標簽ids的形式轉換成用戶id，標簽id的形式。

探索

我們知道像 1,2,3,4,5,212 這種字符串，如果要分別取到1 2 3 4 5 212，用編程的思想就是先將該字符串用","分隔成一個數據，然后遍歷取到數組里的每一個值，但是在mysql里并沒有數組的概念，但是我們可以用各種方法求得字符串的長度，以及求得使用“,”分隔后有多少個值。也可以用mysql可以采取的字符串截取的形式去獲得相應位置的數值。下面就讓我們看一下吧~

實現

相關表結構

CREATE TABLE `tagids_label` (

`userid` int(11) NOT NULL COMMENT '用戶id',

`label` int(11) NOT NULL COMMENT '標記，暫時保留三天的數據，day%3 ',

`day` int(11) NOT NULL COMMENT '對應的統計日期的天',

`tagids` text NOT NULL COMMENT '標簽id，以，(英文)分隔',

`createTime` datetime NOT NULL COMMENT '創建時間',

`updateTime` datetime NOT NULL COMMENT '更新時間',

PRIMARY KEY (`userid`,`label`),

KEY `index_day` (`day`),

KEY `index_label` (`label`),

KEY `index_label_userid` (`userid`,`label`),

KEY `index_createTime_userid` (`userid`,`createTime`),

KEY `index_userid` (`userid`),

KEY `index_createtime` (`createTime`) USING BTREE,

FULLTEXT KEY `index_tagids` (`tagids`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶標簽結果表'

CREATE TABLE `sequence` (

`seq` int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ps:sequence表表示標簽的個數，從1到最大個數

獲得固定分隔符分隔后元素個數

原數據

SELECT * FROM `tagids_label` WHERE `userid` =2

blockchain

171,172,173,174,175,184,187,189,191,192,49,52,55,90,96,101,104,110,7,9,253,270,277,280,129,131,134,136,138,139,231,241,58,63,66,70,72,75,77,79,84,149,150,159,163,165,166,193,195,256,225,236,246,248,197,200,207,221,210,278,227

分隔符分隔后元素個數

SELECT length(`tagids`) ,length(REPLACE (`tagids`,',','')),length(`tagids`)-length(REPLACE (`tagids`,',',''))+1 FROM `tagids_label` WHERE `userid` =2

image.png

注：length(tagids)計算字符串長度，以字節為單位，每個數字、英文標點符號是一個字節，每個中文、中文標點符號是3個字節。length(tagids)表示tagids含有多少個數字和標點符號

replace(tagids,',','')將字符串tagids里的","用空字符來代替，length(replace(tagids,',',''))就表示tagids含有多少個數字。

length(tagids)-length(replace(tagids,',','')) 表示tagsid含有多少個標點符號，那標點符號+1就表示tagids用","分隔符分隔后含有多少個元素，即標簽個數。

substring_index 截取字符串

根據關鍵字","截取字符串

SELECT substring_index('171,172,173,174,175,184,187,189,191,192,49,52,55,90,96,101,104,110,7,9,253,270,277,280,129,131,134,136,138,139,231,241,58,63,66,70,72,75,77,79,84,149,150,159,163,165,166,193,195,256,225,236,246,248,197,200,207,221,210,278,227', ',',1)

UNION ALL

......

UNION ALL

結果：

171

171,172

171,172,173

171,172,173,174

......

注：substring_index(str,delim,count) 說明：substring_index(被截取字段，關鍵字，關鍵字出現的次數)，如果count=-1我們就可以截取到倒數第一個被關鍵字分隔的元素。只要在上面查詢結果中再使用一次substring_index即可獲得每個被關鍵字分隔的元素。

最終實現

SELECT

userid,

SUBSTRING_INDEX(

SUBSTRING_INDEX(tagids, ',', seq),

',' ,- 1

) sub_id,

seq

FROM sequence

JOIN (SELECT * FROM `tagids_label` WHERE userid = 2)b

WHERE

seq BETWEEN 1

AND (

SELECT

1 + LENGTH(tagids) - LENGTH(replace(tagids, ',', ''))

)

ORDER BY

userid,

tagids;

結果：

image.png

總結

以上是生活随笔為你收集整理的mysql分隔字符串转变为多行_【mysql】将以固定分隔符分隔的字符串转成多行的形式...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：开颅血肿清除术手术配合ppt（开颅血肿清
下一篇：网站使用mysql_使用网站的mysql