MySql输入JSON串返回拼装好的查询条件封装函数

最新推荐文章于 2024-07-22 03:04:58 发布

你刘大爷

最新推荐文章于 2024-07-22 03:04:58 发布

阅读量291

点赞数

文章标签： mysql sql 数据库 json

本文链接：https://blog.csdn.net/weixin_42868663/article/details/118996826

版权

本文详细介绍了MySQL中的fn_getJsonToWhere函数，用于将JSON串转换为WHERE条件语句。通过实例展示了如何使用该函数从JSON对象中提取指定关键字的子串，并生成相应的SQL查询条件。同时，文章还提到了相关物理表的创建与使用，以及函数的内部逻辑处理，包括游标和临时表的管理。

摘要由CSDN通过智能技术生成

例：

使用：select fn_getJsonToWhere('{"aa":{"wq":"员工/合作关键人","we":"90"},"bb":{"ss":"员工/合作关键人","tt":"80"}}','bb')
返回：  WHERE (1=1) AND ss='员工/合作关键人' AND tt=80

使用到的物理表：

CREATE TABLE sys_json_to_where_sub  (
  `ID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `JSON_SUB_COLUMN` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用于存储通过关键字得到对应的字json串'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


CREATE TABLE sys_json_to_where  (
  `ID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用于唯一标识当前操作的标识符',
  `JSON_COLUMN` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'json列头'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

函数：

CREATE DEFINER=`root`@`%` FUNCTION `fn_getJsonToWhere`(P_JSON json,P_NAME VARCHAR(100)) RETURNS varchar(5000) CHARSET utf8mb4
    COMMENT '传入JSon串，返回拼装好的查询条件'
BEGIN

/*
 * CREATE BY :lhw 2021-07-21
 *
 *功能说明：传入JSon串，返回拼装好的查询条件
 *
 *输入参数1：P_JSON json字符串 例：{"aa":{"wq":"员工/合作关键人","we":"90"},"bb":{"dd":"员工/合作关键人","ff":"80"}}
 *输入参数2：P_NAME 生成 where 条件的关键字，会根据关键字找到子串 例：aa
 *
 *返回参数：文本串 例: where 11='员工/合作关键人' and 22=90
*/

-- 使用 例： select fn_getJsonToWhere('{"aa":{"wq":"员工/合作关键人","we":"90"},"bb":{"ss":"员工/合作关键人","tt":"80"}}','bb')
-- 返回：  WHERE (1=1) AND ss='员工/合作关键人' AND tt=80
-- 由于mysql游标和临时表不能一起使用，只能通过使用物理表加唯一标识的方式实现
-- 此处唯一标识使用uuid会在下面的两个物理表中使用,使用完后通过此uuid将数据删除
DECLARE V_UUID VARCHAR(100);

-- 最终返回值，拼装好的查询条件
DECLARE V_RESULT VARCHAR(5000);

-- 通过关键字提取的json子串
DECLARE V_JSON_SUB VARCHAR(5000);

-- json子串列名
DECLARE V_JSON_COlUMN VARCHAR(1000);

-- 条件列
DECLARE V_COlUMN_NAME VARCHAR(1000);
-- 条件列ID
DECLARE V_COlUMN_ID VARCHAR(1000);

-- 用于判断是否为数字
DECLARE V_ISSURE VARCHAR(10);

-- 用于游标临时交换用
DECLARE V_TMP_C VARCHAR(1000);

-- 用于游标生成条件时
DECLARE V_WHERE_VALUE VARCHAR(1000);

-- 结束标志变量 
DECLARE doned INT DEFAULT 0;

--  定义游标
DECLARE coulmn_cur CURSOR FOR ( SELECT ID,JSON_COlUMN from sys_json_to_where where  id=V_UUID );

-- 游标中的内容执行完后将doned设置为1 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = 1;

-- 初始化返回值，如果没有解析到数据默认返回此条件
SET V_RESULT := ' WHERE (1=1)';

SET P_NAME := CONCAT('$.',P_NAME);

-- 如果输入json串为{}直接返回 WHERE (1=1)不再执行后面操作
IF ifnull(P_JSON,0) != 0 THEN
				
	return V_RESULT;
				
END IF;
		 


-- 生成uuid
select uuid() into V_UUID FROM dual;

-- 根据传入的json串和关键字得到需要加工的子串  $.aa
SELECT JSON_UNQUOTE(JSON_EXTRACT(P_JSON,P_NAME)) into V_JSON_SUB from dual;

select json_keys(V_JSON_SUB) into V_JSON_COlUMN from dual;

-- 去双引号
select replace(V_JSON_COlUMN,'"','') into V_JSON_COlUMN;
-- 去[
select replace(V_JSON_COlUMN,'[','') into V_JSON_COlUMN;
-- 去]
select replace(V_JSON_COlUMN,']','') into V_JSON_COlUMN;

-- 将提取的需要生成条件的列头存在表中
insert into sys_json_to_where_sub VALUES(V_UUID,V_JSON_COlUMN);

-- 通过逗号分割生成列，插入表中为了生成游标遍历
	INSERT INTO sys_json_to_where SELECT DISTINCT V_UUID,
	substring_index( substring_index( a.JSON_SUB_COLUMN, ',', b.help_topic_id + 1 ), ',', - 1 ) 
	FROM
		sys_json_to_where_sub a
		INNER JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.JSON_SUB_COLUMN ) - length( REPLACE ( a.JSON_SUB_COLUMN, ',', '' )) + 1 ) where a.ID = V_UUID;
		

-- 通过游标变历生成 最终的条件
	OPEN coulmn_cur;##-- 开始循环
	cur_loop :
	LOOP

	FETCH coulmn_cur INTO V_COlUMN_ID,V_COlUMN_NAME;
			-- 判断是否结束循环
			IF doned = 1 THEN LEAVE cur_loop;
			END IF;
    
		 -- 生成查询条件
		 
		 SELECT JSON_UNQUOTE(JSON_EXTRACT(V_JSON_SUB,CONCAT('$.',trim(V_COlUMN_NAME)))) into V_WHERE_VALUE from dual;
		 
		 
		 -- 判断是否为数字，不为数字需要加单引号 支持正负小数点
		 
		 select (V_WHERE_VALUE REGEXP '^(\\-|\\+){0,1}(([0-9]{1,3}(,[0-9]{3})*(.(([0-9]{3},)*[0-9]{1,})){0,1})|([0-9]+(.[0-9]+){0,1}))$') into V_ISSURE;
		 
		 -- V_ISSURE 为1说明是数字为0非数字
		 IF V_ISSURE = 0 THEN
				
				-- 添加单引号
				SET V_WHERE_VALUE := CONCAT('\'',V_WHERE_VALUE,'\''); 
				
		 END IF;
		 
		 
		 -- 拼接 = 号，将条件字段和值拼接到一起
	   SET V_TMP_C = CONCAT_WS('=',trim(V_COlUMN_NAME),trim(V_WHERE_VALUE));
		 
		 -- 和之前的条件拼接
		 SET V_RESULT := CONCAT_WS(' AND ',V_RESULT,V_TMP_C);
	 
		 
		 
		 
		
	END LOOP cur_loop;
	CLOSE coulmn_cur;

  -- 删除物理表数据
   delete from sys_json_to_where where id = V_UUID;
	 
   delete from sys_json_to_where_sub where id = V_UUID;




return V_RESULT;
END