如何使用存储的 MYSQL 程序的表输出 [英] How to use Table output from stored MYSQL Procedure

问题描述

我一直在寻找最后一个小时左右，但没有找到这个看似简单的问题的最终答案:

I've been looking for the last hour or so and haven't found a conclusive answer to this seemingly simple problem:

如何调用存储的 MYSQL 函数/过程并在进一步的 SELECT 查询中使用其输出?

虽然这显然行不通，但这正是我想要的:

Although this obviously doesn't work, this is the kind of thing I'd like to have:

SELECT P.`id` FROM (CALL test_proc()) AS P

其中 test_proc() 定义为:

Where test_proc() is defined by:

DROP PROCEDURE IF EXISTS test_proc;
DELIMITER ;;
CREATE PROCEDURE test_proc()
BEGIN
    SELECT * FROM `table`;
END;;
DELIMITER ;

举个例子.我也可以使用存储函数.

Just as an example. I'd be fine with using a stored function as well.

推荐答案

这不能直接做，因为存储过程中无界选择的输出是发送给客户端的结果集，但技术上不是表.

This can't be done, directly, because the output of an unbounded select in a stored procedure is a result set sent to the client, but not technically a table.

解决方法是在为您创建表后让 proc 将数据放入临时表中.过程完成后，此表将仅对您的连接可用.如果其他人同时运行 proc 并且对任何其他连接不可见，则不会导致冲突.

The workaround is to let the proc put the data in a temporary table after creating the table for you. This table will be available only to your connection when the procedure finishes. It will not cause a conflict if somebody else runs the proc at the same time and won't be visible to any other connection.

将其添加到程序中:

DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo SELECT ... your existing select query here ...;

当你的过程完成时，SELECT * FROM foo; 会给你你从过程中得到的东西.您可以像加入任何表一样加入它.

When your procedure finishes, SELECT * FROM foo; will give you what you what you would have gotten from the proc. You can join to it pretty much like any table.

完成后，放下它，否则断开连接时它会自行消失.如果您再次运行该 proc，它将被删除并重新创建.

When you're done, drop it, or it will go away on its own when you disconnect. If you run the proc again, it will be dropped and recreated.

这篇关于如何使用存储的 MYSQL 程序的表输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！