mysql解析json串函数

DELIMITER //  

CREATE FUNCTION fn_parseJson(

p_jsonstr VARCHAR(255) CHARACTER

SET utf8 ,

p_key VARCHAR(255)

) RETURNS VARCHAR(300)

BEGIN

 

DECLARE rtnVal VARCHAR(255) DEFAULT '' ;

DECLARE v_key VARCHAR(255) ;

SET v_key = CONCAT('"' , p_key , '":') ;

SET @v_flag = p_jsonstr REGEXP v_key ;

IF(@v_flag = 0) THEN

 

SET rtnVal = '' ;

ELSE

SELECT

val INTO rtnVal

FROM

(

SELECT

@start_pos := locate(v_key , p_jsonstr) ,

@end_pos := @start_pos + length(v_key) ,

@tail_pos :=

IF(

locate("," , p_jsonstr , @end_pos) = 0 ,

locate("}" , p_jsonstr , @end_pos) ,

locate("," , p_jsonstr , @end_pos)

) ,

substring(

p_jsonstr ,

@end_pos + 1 ,

@tail_pos - @end_pos - 2

) AS val

) AS t ;

END

IF ; RETURN rtnVal ;

END//  

DELIMITER ;