背景
近期在做用戶標(biāo)簽項(xiàng)目仇奶，目前標(biāo)簽的存儲是用戶id胁黑，標(biāo)簽ids（每個標(biāo)簽以,形式存儲） 的形式，但是如果想統(tǒng)計(jì)前后兩天標(biāo)簽的變化邓梅，使用find_in_set函數(shù)冶共，一方面查詢速度慢（因?yàn)椴荒苁褂盟饕┣颍硪环矫婺壳皹?biāo)簽已有將近300多個，以后還會更多捅僵，一個標(biāo)簽一個標(biāo)簽的寫家卖，使得sql特別長。
目前采取的策略是：將用戶標(biāo)簽表拆分成 用戶id庙楚，標(biāo)簽id的形式上荡。這樣將前后兩天的表關(guān)聯(lián)，就能查出昨天到今天有那些標(biāo)簽離開醋奠，哪些標(biāo)簽進(jìn)來榛臼。
那么采用上述策略就需要研究怎么將用戶id伊佃，標(biāo)簽ids的形式轉(zhuǎn)換成用戶id窜司，標(biāo)簽id的形式。
探索
我們知道像 1,2,3,4,5,212 這種字符串航揉，如果要分別取到1 2 3 4 5 212塞祈，用編程的思想就是先將該字符串用","分隔成一個數(shù)據(jù)，然后遍歷取到數(shù)組里的每一個值帅涂，但是在mysql里并沒有數(shù)組的概念议薪，但是我們可以用各種方法求得字符串的長度，以及求得使用“,”分隔后有多少個值媳友。也可以用mysql可以采取的字符串截取的形式去獲得相應(yīng)位置的數(shù)值斯议。下面就讓我們看一下吧~
實(shí)現(xiàn)
- 相關(guān)表結(jié)構(gòu)
CREATE TABLE `tagids_label` (
`userid` int(11) NOT NULL COMMENT '用戶id',
`label` int(11) NOT NULL COMMENT '標(biāo)記，暫時 保留三天的數(shù)據(jù)醇锚，day%3 ',
`day` int(11) NOT NULL COMMENT '對應(yīng)的統(tǒng)計(jì)日期的天',
`tagids` text NOT NULL COMMENT '標(biāo)簽id哼御，以，（英文）分隔',
`createTime` datetime NOT NULL COMMENT '創(chuàng)建時間',
`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='用戶標(biāo)簽結(jié)果表'
CREATE TABLE `sequence` (
`seq` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ps:sequence表表示標(biāo)簽的個數(shù)焊唬，從1到最大個數(shù)
-
獲得固定分隔符分隔后元素個數(shù)
- 原數(shù)據(jù)
SELECT * FROM `tagids_label` WHERE `userid` =2blockchain171,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- 分隔符分隔后元素個數(shù)
SELECT length(`tagids`) ,length(REPLACE (`tagids`,',','')),length(`tagids`)-length(REPLACE (`tagids`,',',''))+1 FROM `tagids_label` WHERE `userid` =2image.png- 注：length(
tagids)計(jì)算字符串長度恋昼，以字節(jié)為單位，每個數(shù)字赶促、英文標(biāo)點(diǎn)符號是一個字節(jié)液肌，每個中文、中文標(biāo)點(diǎn)符號是3個字節(jié)鸥滨。length(tagids)表示tagids含有多少個數(shù)字和標(biāo)點(diǎn)符號
replace(tagids,',','')將字符串tagids里的","用空字符來代替嗦哆，length(replace(tagids,',',''))就表示tagids含有多少個數(shù)字谤祖。
length(tagids)-length(replace(tagids,',','')) 表示tagsid含有多少個標(biāo)點(diǎn)符號，那標(biāo)點(diǎn)符號+1就表示tagids用","分隔符分隔后含有多少個元素老速，即標(biāo)簽個數(shù)泊脐。
- substring_index 截取字符串
- 根據(jù)關(guān)鍵字","截取字符串
結(jié)果：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 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', ',',2) UNION ALL 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', ',',3) UNION ALL 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', ',',4) UNION ALL ...... UNION ALL 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',',',61)171 171,172 171,172,173 171,172,173,174 ...... 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- 注：substring_index（str,delim,count） 說明：substring_index（被截取字段，關(guān)鍵字烁峭，關(guān)鍵字出現(xiàn)的次數(shù)）容客，如果count=-1我們就可以截取到倒數(shù)第一個被關(guān)鍵字分隔的元素。只要在上面查詢結(jié)果中再使用一次substring_index即可獲得每個被關(guān)鍵字分隔的元素约郁。
- 最終實(shí)現(xiàn)
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;
-
結(jié)果：
image.png
image.png
