mysql5.6 解析JSON字符串方式(支持復(fù)雜的嵌套格式)
更新時(shí)間：2022年07月05日 11:36:38 作者：鄧1136507751
這篇文章主要介紹了mysql5.6 解析JSON字符串方式(支持復(fù)雜的嵌套格式)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
mysql5.6 解析JSON字符串
支持復(fù)雜的嵌套格式
廢話不多說，先上代碼。
CREATE FUNCTION `json_parse`(`jsondata` longtext,`keyname` text) RETURNS text CHARSET utf8
BEGIN
DECLARE delim VARCHAR(128);
DECLARE result longtext;
DECLARE startpos INTEGER;
DECLARE endpos INTEGER;
DECLARE endpos1 INTEGER;
DECLARE findpos INTEGER;
DECLARE leftbrace INTEGER;
DECLARE tmp longtext;
DECLARE tmp2 longtext;
DECLARE Flag INTEGER;
SET delim = CONCAT('"', keyname, '": "');
SET startpos = locate(delim,jsondata);
IF startpos > 0 THEN
SET findpos = startpos+length(delim);
SET leftbrace = 1;
SET endpos = 0;
SET Flag =1;
get_token_loop: repeat
IF substr(jsondata,findpos,2)='\\"' THEN
SET findpos = findpos + 2;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,2)='\\\\' THEN
SET findpos = findpos + 2;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,1)='"' AND Flag = 1 THEN
SET endpos = findpos;
SET findpos = LENGTH(jsondata)+1;
leave get_token_loop;
END IF;
SET findpos = findpos + 1;
UNTIL findpos > LENGTH(jsondata) END repeat;
IF endpos > 0 THEN
SELECT
substr(
jsondata
,startpos
+length(delim)#取出value值的起始位置
,endpos#取出value值的結(jié)束位置
-(
startpos
+length(delim)
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) INTO result
FROM DUAL;
SET result= replace(result,'\\"','"');
SET result= replace(result,'\\\\','\\');
ELSE
SET result=null;
END IF;
/*
SELECT
substr(
jsondata
,locate(delim,jsondata)
+length(delim)#取出value值的起始位置
,locate(
'"'
,jsondata
,locate(delim,jsondata)
+length(delim)
)#取出value值的結(jié)束位置
-(
locate(delim,jsondata)
+length(delim)
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) INTO result
FROM DUAL; */
ELSE
SET delim = CONCAT('"', keyname, '": {');
SET startpos = locate(delim,jsondata);
IF startpos > 0 THEN
SET findpos = startpos+length(delim);
SET leftbrace = 0;
SET endpos = 0;
SET Flag =0;
get_token_loop: repeat
IF substr(jsondata,findpos,2)='{"' THEN
SET leftbrace = leftbrace + 1;
SET findpos = findpos + 2;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,2)='\\"' THEN
SET findpos = findpos + 2;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,3)=': "' THEN
SET Flag = 1;
SET findpos = findpos + 3;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,1)='"' THEN
SET Flag = 0;
ELSEIF substr(jsondata,findpos,1)='}' AND Flag = 0 THEN
IF leftbrace > 0 THEN
SET leftbrace = leftbrace - 1;
ELSE
SET endpos = findpos;
SET findpos = LENGTH(jsondata)+1;
END IF;
END IF;
SET findpos = findpos + 1;
UNTIL findpos > LENGTH(jsondata) END repeat;
IF endpos > 0 THEN
SELECT
substr(
jsondata
,startpos
+length(delim)#取出value值的起始位置
,endpos#取出value值的結(jié)束位置
-(
startpos
+length(delim)
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) INTO result
FROM DUAL;
SET result=CONCAT("{",result, '}');
ELSE
SET result=null;
END IF;
ELSE
SET delim = CONCAT('"', keyname, '": [');
SET startpos = locate(delim,jsondata);
IF startpos > 0 THEN
SET findpos = startpos+length(delim);
SET leftbrace = 0;
SET endpos = 0;
SET tmp = substring_index(jsondata,delim,-1);
SET tmp2 = substring_index(tmp,']',1);
IF locate('[',tmp2) =0 THEN
SET endpos = locate(']',tmp);
SET endpos = endpos+findpos-1;
ELSE
get_token_loop: repeat
IF substr(jsondata,findpos,2)='\\"' THEN
SET findpos = findpos + 2;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,3)=': "' THEN
SET Flag = 1;
SET findpos = findpos + 3;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,1)='[' AND Flag = 0 THEN
SET leftbrace = leftbrace + 1;
SET findpos = findpos + 1;
iterate get_token_loop;
ELSEIF substr(jsondata,findpos,1)='"' THEN
SET Flag = 0;
ELSEIF substr(jsondata,findpos,1)=']' AND Flag = 0 THEN
IF leftbrace > 0 THEN
SET leftbrace = leftbrace - 1;
ELSE
SET endpos = findpos;
SET findpos = LENGTH(jsondata)+1;
END IF;
END IF;
SET findpos = findpos + 1;
UNTIL findpos > LENGTH(jsondata) END repeat;
END IF;
IF endpos > 0 THEN
SELECT
substr(
jsondata
,startpos
+length(delim)#取出value值的起始位置
,endpos#取出value值的結(jié)束位置
-(
locate(delim,jsondata)
+length(delim)
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) INTO result
FROM DUAL;
SET result=CONCAT("[",result, ']');
ELSE
SET result=null;
END IF;
ELSE
SET delim = CONCAT('"', keyname, '": ');
SET startpos = locate(delim,jsondata);
IF startpos > 0 THEN
SET endpos = locate(',',jsondata,startpos+length(delim));
SET endpos1 = locate('}',jsondata,startpos+length(delim));
IF endpos>0 OR endpos1>0 THEN
IF endpos1>0 AND endpos1 < endpos OR endpos =0 THEN
SET endpos = endpos1;
END IF;
SELECT
substr(
jsondata
,startpos
+length(delim)#取出value值的起始位置
,endpos#取出value值的結(jié)束位置
-(
locate(delim,jsondata)
+length(delim)
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) INTO result
FROM DUAL;
IF STRCMP(result,'null')=0 THEN
SET result=null;
END IF;
ELSE
SET result=null;
END IF;
ELSE
SET result=null;
END IF;
END IF;
END IF;
END IF;
if result='' and RIGHT(keyname,2)='Id' then
SET result=null;
end if;
RETURN result;
ENDjsondata需要嚴(yán)格的json格式（注意逗號(hào)和分號(hào)以及雙引號(hào)之間的空格）
SET jsondata='{"CurrentPage": 1, "data": [{"config": "123"}, {"config": "456"}], "PageSize": 10}'?
SELECT json_parse(jsondata, 'CurrentPage') INTO CurrentPage;
SELECT json_parse(jsondata, 'data') INTO data;這邊如果想獲取config的內(nèi)容，可以這樣處理
? ? ? ? SET count = (LENGTH(data)-LENGTH(REPLACE(data,'},','')))/2+1; ? ? ? ? SET i = 0; ?? ??? ?WHILE i < count DO ?? ??? ??? ?SET SetObject = SUBSTRING_INDEX(SUBSTRING_INDEX(data,'},',i+1),'},',-1); ?? ??? ??? ?IF LENGTH(SetObject)>0 THEN ?? ??? ??? ??? ?SELECT json_parse(SetObject, 'config') INTO config;?? ? ?? ??? ??? ?END IF; ?? ??? ??? ?SET i = i + 1; ?? ??? ?END WHILE;
不足之處，jsondata數(shù)據(jù)多的情況下，會(huì)有效率問題。
mysql5.6及以下解析json方法
之前在公司發(fā)現(xiàn)在線的查詢平臺(tái)是MySQL5.6，不能用JSON_EXTRACT，也不能用存儲(chǔ)過程，所以只能自己編了一個(gè)簡(jiǎn)單的小查詢，幾條數(shù)據(jù)還是能查的，如果數(shù)據(jù)量大的話，估計(jì)耗的資源就會(huì)比較多。
先說一下問題的背景
是想在'{"platform":"Android","source":"tt","details":null}'這一串東西里面找到source這個(gè)key對(duì)應(yīng)的value值。
這個(gè)方法是先找到source":"這個(gè)字符串的起始位置和長(zhǎng)度，這樣就能夠找到value值的起始位置；再找到這個(gè)字符串以后第一個(gè)"出現(xiàn)的位置，就能得到value值的結(jié)束位置。
再利用substr函數(shù)，就可以取出對(duì)應(yīng)的位置。
下面是對(duì)應(yīng)的代碼
SELECT
'{"platform":"Android","source":"tt","details":null}' as 'sample'
,substr(
'{"platform":"Android","source":"tt","details":null}'
,locate('source":"','{"platform":"Android","source":"tt","details":null}')
+length('source":"')#取出value值的起始位置
,locate(
'"'
,'{"platform":"Android","source":"tt","details":null}'
,locate('source":"','{"platform":"Android","source":"tt","details":null}')
+length('source":"')
)#取出value值的結(jié)束位置
-(
locate('source":"','{"platform":"Android","source":"tt","details":null}')
+length('source":"')
)#減去value值的起始位置，得到value值字符長(zhǎng)度
) as result
FROM DUAL
運(yùn)行以后，就得到result的結(jié)果，就是tt。如果需要其他元素，就替換一下對(duì)應(yīng)的key值和字段，就好了。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
MySql8設(shè)置遠(yuǎn)程連接的實(shí)戰(zhàn)記錄
與SQL Server類似,MySQL在需要遠(yuǎn)程操縱其他電腦時(shí),也需要對(duì)其做遠(yuǎn)程連接的相應(yīng)設(shè)置,下面這篇文章主要給大家介紹了關(guān)于MySql8設(shè)置遠(yuǎn)程連接的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了windows下安裝MySQL教程的相關(guān)資料,需要的朋友可以參考下2022-10-10
MySQL數(shù)據(jù)庫表分區(qū)注意事項(xiàng)大全【推薦】
這篇文章主要介紹了MySQL數(shù)據(jù)庫表分區(qū)注意事項(xiàng)相關(guān)內(nèi)容，比較全面，這里分享給大家，需要的朋友可以參考。2017-10-10
本文主要介紹了MySQL?TCL事務(wù)控制,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-11-11
這篇文章主要介紹了mysql字符切割的四種方式匯總,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01
MySQL數(shù)據(jù)庫如何給表設(shè)置約束詳解
約束主要完成對(duì)數(shù)據(jù)的檢驗(yàn)，保證數(shù)據(jù)庫數(shù)據(jù)的完整性；如果有相互依賴數(shù)據(jù)，保證該數(shù)據(jù)不被刪除，本篇文章教你如何給表設(shè)置約束2022-03-03
win10下mysql 8.0.11壓縮版安裝詳細(xì)教程
這篇文章主要為大家詳細(xì)介紹了win10下mysql 8.0.11壓縮版安裝詳細(xì)教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-05-05
MySQL中的FIND_IN_SET函數(shù)的使用場(chǎng)景
MySQL 中的?FIND_IN_SET()?函數(shù)用于在一個(gè)逗號(hào)分隔的字符串列表中查找指定的值,并返回其在列表中的位置,下面就來介紹一下FIND_IN_SET函數(shù)的具體使用,感興趣的可以了解一下2024-05-05
在數(shù)據(jù)庫系統(tǒng)中，同時(shí)有多個(gè)用戶或進(jìn)程訪問數(shù)據(jù)是常見的情況，為了確保數(shù)據(jù)的完整性和一致性，數(shù)據(jù)庫管理系統(tǒng)引入了鎖機(jī)制，本文將深入探討?MySQL?鎖機(jī)制，幫助您理解鎖的分類、實(shí)現(xiàn)方式以及使用場(chǎng)景和優(yōu)化策略,需要的朋友可以參考下2023-05-05
淺談mysql數(shù)據(jù)庫中的換行符與textarea中的換行符
下面小編就為大家?guī)硪黄獪\談mysql數(shù)據(jù)庫中的換行符與textarea中的換行符。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-01-01