mysql - 是否可以在不使用 EXECUTE 的情况下返回 MySQL 存储过程中(可变)数量的选择的 UNION 的单个结果集？

Question

我正在 MySQL 中创建一个存储过程，给定一个域名，应该为域名中的每个“级别”进行一个选择查询(全部到同一个表，返回相同的字段)，并返回 UNION这些查询的结果作为单个结果集。

由于查询是在循环中执行的(即查询的数量取决于作为存储过程参数提供的域名中的级别数)，因此我不能仅使用包含 UNION 的多个选择的静态 SQL 语句他们之间。

相反，我需要迭代地进行选择查询，或者在此循环中构建一个串联的 SQL 查询字符串，并在循环后执行它。

后一个解决方案(连接的 SQL 语句)对我来说听起来既“丑陋”又“危险”(安全方面)，因此我更愿意以某种方式“本地”执行它，但我不确定这是否是可能吗？

我已经想到可以使用临时表来存储每次循环迭代中的查询结果，但后来我陷入了如何最终联合所有这些临时表的内容的困境(记住:将会有每次调用我的存储过程时这些临时表的变体号)最终都不使用 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           | ...   | ...   | ...   |
------------------------------------------------

Answer 1

在过程开始时，使用您将返回的输出列的定义创建一个临时表。临时表命名空间的范围仅限于 session (数据库连接)，因此该过程的两个并发运行不可能导致该名称发生冲突。

-- 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;