mysql5.6 解析JSON字符串方式(支持復雜的嵌套格式)
更新時間：2022年07月05日 11:36:38 作者：鄧1136507751
這篇文章主要介紹了mysql5.6 解析JSON字符串方式(支持復雜的嵌套格式)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
mysql5.6 解析JSON字符串
支持復雜的嵌套格式
廢話不多說，先上代碼。
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值字符長度
) 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值字符長度
) 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值字符長度
) 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值字符長度
) 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值字符長度
) 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需要嚴格的json格式（注意逗號和分號以及雙引號之間的空格）
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ù)多的情況下，會有效率問題。
mysql5.6及以下解析json方法
之前在公司發(fā)現(xiàn)在線的查詢平臺是MySQL5.6，不能用JSON_EXTRACT，也不能用存儲過程，所以只能自己編了一個簡單的小查詢，幾條數(shù)據(jù)還是能查的，如果數(shù)據(jù)量大的話，估計耗的資源就會比較多。
先說一下問題的背景
是想在'{"platform":"Android","source":"tt","details":null}'這一串東西里面找到source這個key對應的value值。
這個方法是先找到source":"這個字符串的起始位置和長度，這樣就能夠找到value值的起始位置；再找到這個字符串以后第一個"出現(xiàn)的位置，就能得到value值的結(jié)束位置。
再利用substr函數(shù)，就可以取出對應的位置。
下面是對應的代碼
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值字符長度
) as result
FROM DUAL
運行以后，就得到result的結(jié)果，就是tt。如果需要其他元素，就替換一下對應的key值和字段，就好了。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
Mysql連接本地報錯:1130-host?...?is?not?allowed?to?connect?t
這篇文章主要給大家介紹了關(guān)于Mysql連接本地報錯:1130-host?...?is?not?allowed?to?connect?to?this?MySQL?server的解決方法,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2023-03-03
這篇文章主要介紹了MySQL?數(shù)據(jù)持久化過程講解，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，?需要的朋友可以參考一下，希望對你的學習有所幫助2022-07-07
深入解析MySQL中的longtext與longblob及應用場景
MySQL作為廣泛應用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),提供了豐富的數(shù)據(jù)類型以滿足各種數(shù)據(jù)存儲需求,本文將深入探討MySQL中l(wèi)ongtext和longblob的特性、區(qū)別以及在實際項目中的應用場景,感興趣的朋友跟隨小編一起看看吧2024-05-05![win2003 mysql單向同步配置步驟[已測]](http://img.jbzj.com/images/xgimg/bcimg3.png)
為了服務器安全特將mysql同步了下，由于時間長了經(jīng)常忘記，特以此整理下了，方便經(jīng)常支持腳本之家的朋友。2011-06-06
MySQL實現(xiàn)批量插入以優(yōu)化性能的教程
這篇文章主要介紹了MySQL實現(xiàn)批量插入以優(yōu)化性能的教程,文中給出了運行時間來表示性能優(yōu)化后的對比,需要的朋友可以參考下2015-04-04
mysql密碼忘記怎么辦，如何快速修改mysql密碼，下面給大家?guī)硭姆N方法快速修改mysql密碼,感興趣的朋友跟隨小編一起看看吧2023-01-01
最近在考慮訂單id怎么生成,下面這篇文章主要給大家介紹了關(guān)于面試官問訂單ID是如何生成的?難道不是MySQL自增主鍵的相關(guān)資料,需要的朋友可以參考下2023-02-02
最近在啟動mysql的時候發(fā)現(xiàn)mysql報錯了，錯誤代碼是2002，通過查找相關(guān)的資料發(fā)現(xiàn)是var/lib/mysql 的訪問權(quán)限問題，所以這篇文章主要介紹了mysql中異常錯誤ERROR：2002的解決方法，需要的朋友可以參考借鑒，下面來一起看看吧。2017-03-03
在本篇文章里小編給大家整理的是一篇關(guān)于mysql安裝忘記初始密碼簡單有效的解決方法，有需要的朋友們參考學習下。2020-01-01
mysql時間戳格式化函數(shù)from_unixtime使用的簡單說明
mysql中的FROM_UNIXTIME函數(shù)可以數(shù)據(jù)庫中整型類的時間戳格式化為字符串的日期時間格式,下面這篇文章主要給大家介紹了關(guān)于mysql時間戳格式化函數(shù)from_unixtime使用的簡單說明,需要的朋友可以參考下2022-08-08