这个自定义函数的目的是处理格式非常简单的JSON,像下面这种
{“name”:"Tom", "age":21, "sex":"male"}
给出名字,要求取出对应的值。
DELIMITER $$
DROP FUNCTION IF EXISTS `json_item` $$
CREATE FUNCTION `json_item`(
my_json VARCHAR(4096),
my_item VARCHAR(64)
) RETURNS VARCHAR(512) CHARSET utf8
BEGIN
DECLARE s VARCHAR(4096);
DECLARE i, j SMALLINT UNSIGNED;
IF my_json = '' OR my_json IS NULL THEN
return '';
END if;
set s = CONCAT('"',my_item,'":');
SET i = INSTR(my_json, s);
IF i = 0 then
return '';
end if;
set i = i + length(my_item) + 3;
set j = LOCATE(',', my_json, i);
if j = 0 then
set j = LOCATE('}', my_json, i);
end if;
if j = 0 then
return '';
end if;
set s = substring(my_json, i, j - i);
if left(s, 1) = '"' then
set s = substring(s, 2, length(s) - 2);
end if;
return s;
END$$
DELIMITER ;