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值的結束位置 -( 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值的結束位置 -( 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值的結束位置 -( 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值的結束位置 -( 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值的結束位置 -( 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; END
jsondata需要嚴格的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的內容,可以這樣處理
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數據多的情況下,會有效率問題。
mysql5.6及以下解析json方法
之前在公司發現在線的查詢平臺是MySQL5.6,不能用JSON_EXTRACT,也不能用存儲過程,所以隻能自己編瞭一個簡單的小查詢,幾條數據還是能查的,如果數據量大的話,估計耗的資源就會比較多。
先說一下問題的背景
是想在'{"platform":"Android","source":"tt","details":null}'這一串東西裡面找到source這個key對應的value值。
這個方法是先找到source":"這個字符串的起始位置和長度,這樣就能夠找到value值的起始位置;再找到這個字符串以後第一個"出現的位置,就能得到value值的結束位置。
再利用substr函數,就可以取出對應的位置。
下面是對應的代碼
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值的結束位置 -( locate('source":"','{"platform":"Android","source":"tt","details":null}') +length('source":"') )#減去value值的起始位置,得到value值字符長度 ) as result FROM DUAL
運行以後,就得到result的結果,就是tt。如果需要其他元素,就替換一下對應的key值和字段,就好瞭。
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- postgresql 中position函數的性能詳解
- MySQL 十大常用字符串函數詳解
- MySQL中存儲過程的詳細詳解
- MySQL查詢字段實現字符串分割split功能的示例代碼
- MySQL存儲過程的創建、調用與管理詳解