|
DROP FUNCTION IF EXISTS parse_json;
DELIMITER //
CREATE FUNCTION parse_json(jsonStr text charset utf8, varname TEXT charset utf8) RETURNS int
DETERMINISTIC
BEGIN
DECLARE ratio float default 0;
DECLARE i int default 0;
IF json_valid(jsonStr) = 0 THEN
RETURN 0;
END IF;
WHILE i < JSON_LENGTH(jsonStr) DO
SELECT JSON_EXTRACT(jsonStr,CONCAT('$[',i,'].ratio')) INTO ratio;
SELECT i + 1 INTO i;
IF varname LIKE 'is_a' THEN
IF ratio != 20 THEN
return 1;
END IF;
END IF;
IF varname LIKE 'is_b' THEN
IF ratio > 10 THEN
return 1;
END IF ;
END IF;
IF varname LIKE 'is_c' THEN
IF ratio >= 1 THEN
RETURN 1;
END IF;
END IF;
END WHILE;
RETURN 0;
END //
DELIMITER ;
测试
-- test
set @json = '[{"id":301,"name":"AA","ratio":0.0},{"id":301,"name":"BB","ratio":0},{"id":301,"name":"CC","ratio":-0.01}]';
select parse_json(@json,'is_a'),parse_json(@json,'is_b'),parse_json(@json,'is_c');
注意
JSON加一层是否是JSON的验证.
string类型的参数（varchar/text）需要指定字符集，否则会出错。
注意，字符串不能直接用 if varname = ‘xxx’ then，这样会出现无法相等情况，可以用LIKE
|
