我正在MySQL中创建一个存储过程,给定域名,该过程应该为域名中的每个"级别"进行一个select查询(所有查询都指向同一个表,返回相同的字段),并将这些查询的结果作为一个结果集返回UNION。
由于查询是在循环中执行的(即查询的数量取决于作为存储过程参数提供的域名中的级别的数量),因此我不能只使用包含多个带UNION的选择的静态SQL语句。
相反,我需要迭代地进行选择查询,或者在这个循环中构建一个连接的SQL查询字符串,并在循环后执行它。
后一种解决方案(串联SQL语句)对我来说既"丑陋"又"危险"(从安全角度来看),因此我更愿意以某种方式"原生"执行,但我不确定这是否可能?
我已经想好了在每次循环迭代中都可以使用临时表来存储查询结果,但后来我陷入了如何最终UNION所有这些临时表的内容的困境(记住:对我的存储过程的每次调用都会有一个不同数量的临时表),而最终不使用EXECUTE?(当然,与从头开始构建整个多UNION SQL语句并执行它相比,使用EXECUTE的丑陋和风险要小得多,但我仍然很好奇是否真的需要EXECUTE来实现这一点?)
对于这个用例,有人有任何优雅/推荐的解决方案吗(我认为这至少是一个不太罕见的解决方案?)?
以下是我到目前为止的功能,以防有人好奇或认为它能让他们更好地了解我的问题:
CREATE lookup_domain(IN lookup_name VARCHAR(4096))
BEGIN
DECLARE noof_levels int(11) DEFAULT 0;
DECLARE remaining_levels varchar(4096) DEFAULT '';
SET noof_levels = ROUND((LENGTH(lookup_name)-LENGTH(REPLACE(lookup_name, ".", ""))) / LENGTH("."));
SET remaining_levels = lookup_name;
WHILE noof_levels >= 1 DO
SET noof_levels = noof_levels - 1;
#This will simply print the current domain level
#but in reality it will look up properties of it in a table,
#like: SELECT name, prop1, prop2, prop3 FROM domains WHERE name = ?
#and it's these results that I want to UNION into a single result set
SELECT remaining_levels AS 'debug_res';
SET remaining_levels = SUBSTRING(remaining_levels,LOCATE('.', remaining_levels)+1);
END WHILE;
END
因此,使用参数"some.test.domain.com"调用该函数所需的(单个)结果集应该类似于:
------------------------------------------------
| name | prop1 | prop2 | prop3 |
------------------------------------------------
| some.test.domain.com | ... | ... | ... |
| test.domain.com | ... | ... | ... |
| domain.com | ... | ... | ... |
------------------------------------------------
在过程的开头,创建一个临时表,其中包含将要返回的输出列的定义。临时表命名空间的作用域是会话(数据库连接),因此过程的两次并发运行不可能导致该名称发生冲突。
-- in case it is there from a previous run if the proc in the same session
DROP TEMPORARY TABLE IF EXISTS tmp_lookup;
-- define the columns for the output
CREATE TEMPORARY TABLE tmp_lookup (name ...
-- the above statements go before this next line
SET noof_levels = ...
然后,在循环中,将SELECT
更改为INSERT ... SELECT
,将结果写入临时表,而不是立即发送到客户端。
INSERT INTO tmp_lookup SELECT name...
END WHILE;
之后,将所有收集的行返回给调用者:
SELECT * FROM tmp_lookup;