Loading

MySQL动态执行字符串

需求

  • 表中存有按天维度字符串表达式字段，需要取出，然后计算出字符串表达式的结果，最后得到一个结果集，并且保留结果

例：表达式字段值为value > 1，需要先replace掉value为实际的值10，然后执行select 10 > 1，每行表达式不一样，最后需要输出为多行结果集


问题

  • 字符串表达式需要先从SQL中查出结果集，然后拼接成select $expression$动态执行，但select语句中不支持动态执行字符串

解决思路

  • 动态执行字符串必须用到PREPARE/EXECUTE命令
  • 拼接SQL语句执行需要遍历结果集的每条记录中的表达式字段，需要用到游标
  • 使用存储过程接收参数筛选而非函数，因为函数中不支持使用PREPARE命令
  • 最后将表达式结果保存到另外一个表

脚本

  • user_exp_interfacecallresptimewarnnum_detail_warn为新建表，接收最终结果集
delimiter //
-- 创建存储过程，接收3个参数
drop procedure if exists user_exp_interfaceCallRespTimeWarnNum_detail //
create procedure user_exp_interfaceCallRespTimeWarnNum_detail(IN in_dateType varchar(10), IN in_dateTime varchar(10), IN in_indexId varchar(100))

begin
-- 申明变量
-- 申明一个遍历标识
DECLARE s int DEFAULT 0;
DECLARE p_sys_code varchar(100);
DECLARE p_interface varchar(1000);
DECLARE p_expression varchar(1000);

-- 申明游标指向一个select结果集
DECLARE cur1 CURSOR FOR 
    SELECT
            b.sys_code as sys_code,
            IFNULL(d.interface, "") as interface,
        ifnull(REPLACE (
        REPLACE ( REPLACE ( REPLACE ( b.index_cal_name, "callNum", d.call_num), "p95", p95 ), "&&", "and" ),
        "||",
        "or" 
        ), "0") as expression
    FROM
        (
    SELECT
        sy.sys_code AS sys_code,
        ifnull( index_cal_name, ( SELECT index_cal_name FROM ue_prewarning_value WHERE sys_code = "ALL" AND index_id =in_indexId ) ) index_cal_name,
        cs.sys_name_cn as sys_name,
        cs.rd_center as center_name,
        concat(cs.op_main_engineer_primary, "(", op_main_engineer_primary_no, ")") as op_main_engineer_primary
    FROM
        tdata_event_topic_project_map_sys_code sy
        LEFT JOIN ue_prewarning_value ue ON sy.sys_code = ue.sys_code	
        AND ue.index_id = in_indexId
        LEFT JOIN tdata_cloud_system cs on sy.sys_code = cs.sys_code
        where cs.date_time = (select max(date_time) from tdata_cloud_system)
        ) b
        LEFT JOIN tdata_apm_interface_call_detail d ON b.sys_code = d.sys_code
        where d.date_type = in_dateType and d.date_time = in_dateTime;
	
-- 申明结果集终止flag设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

-- 将表中的数据清楚
delete from user_exp_interfacecallresptimewarnnum_detail_warn where date_type = in_dateType and date_time = in_dateTime and index_id = in_indexId;

#open cursor
-- 打开游标
OPEN cur1;

-- 从结果集取出值, 顺序、字段数量需要对应上
fetch cur1 into p_sys_code, p_interface, p_expression;

-- 执行insert插入
while s<>1 do
	SET @s = CONCAT("INSERT INTO user_exp_interfacecallresptimewarnnum_detail_warn ( 
	date_type, 
	date_time, 
	interface, 
	index_id, 
	sys_code, 
	pass, 
	expression) 
	VALUE (",
	
	 concat("'", in_dateType , "',"), 
	 concat("'", in_dateTime , "',"), 
	 concat("'", p_interface , "',"), 
	 concat("'", in_indexId , "',"), 
	 concat("'", p_sys_code , "',"), 
    -- 拼接成select $expression$表达式
	"(select ", p_expression, "),", 
	concat("'", p_expression , "'"), ");");
	
	-- PREPARE拼接之后的sql
	PREPARE stmt1 FROM @s;
	EXECUTE stmt1;
	fetch cur1 into p_sys_code, p_interface, p_expression;
end while;
-- 关闭游标
close cur1;

-- 最后执行一次查询展示结果集
select * from user_exp_interfacecallresptimewarnnum_detail_warn where date_type = in_dateType and date_time = in_dateTime and index_id = in_indexId;
END;

//
delimiter ;
-- 调用存储过程
call user_exp_interfaceCallRespTimeWarnNum_detail("day", "2022-10-10", "interfaceCallRespTimeWarnNum");

参考

posted @ 2022-10-11 19:29  FynnWang  阅读(771)  评论(0)  编辑  收藏  举报