mysql 5.7(5.6)以下解析json字符串

最新推荐文章于 2024-09-22 06:24:26 发布

hbzyb

最新推荐文章于 2024-09-22 06:24:26 发布

阅读量2.3k

点赞数 2

文章标签： mysql json 中文

本文链接：https://blog.csdn.net/hbzyb/article/details/98876543

版权

因项目需要编写包含解析json字符串语句的mysql函数，只能将5.6升级到了5.7，本以为就此完事，但谁知后来业务需要用到中文字符串key类型的json，这下5.7就歇菜了，因为原生函数不支持中文字符串作为key的解析，网上找了很多网友自定义解析json的函数，发现支持度都有不少问题，于是自己写了个最土的实现（如下），有需要的朋友拿去用吧

DELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_c`$$

CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET utf8
BEGIN
	DECLARE sCount int(3) default 0;

	SET sCount = length(details)-length(REPLACE(details,'{',''));
	SET details = SUBSTRING_INDEX(details, "{", 0 - sCount); -- "a":"a","b":"b","c":"c"}

	SET sCount = length(details)-length(REPLACE(details,'}',''));
	SET details = SUBSTRING_INDEX(details, "}", sCount); -- "a":"a","b":"b","c":"c"

	SET required_field = CONCAT('"',SUBSTRING_INDEX(required_field,'$.', -1),'":');
	SET details = TRIM(SUBSTRING_INDEX(details, required_field, -1)); -- "b","c":"c"

	IF LOCATE('{', details) = 1 || LOCATE('{', details) = 2 THEN  -- {"b","c":"c"}
		RETURN details;

	ELSEIF LOCATE('"', details) <> 1 THEN  -- b,"c":"c"或b
		IF LOCATE(',', details) > 1 THEN
			SET details = SUBSTRING_INDEX(details, ',', 1);
		END IF;
		
		RETURN TRIM(details);
	END IF;

	SET sCount = length(details)-length(REPLACE(details,'"',''));
	SET details = SUBSTRING_INDEX(details, '"', 0 - sCount); -- :"b","c":"c"}
	SET details = SUBSTRING_INDEX(details, '"', 1); -- b

	RETURN details;
END$$
DELIMITER ;