如何使用存储MYSQL过程的表输出

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

我一直在寻找最后一个小时的时间，还没有找到这个看似简单的问题的决定性答案:

How do you call a stored MYSQL function/procedure and use its output in further SELECT queries?

如何调用存储的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

Where test_proc() is defined by:

其中test_proc()的定义为:

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.

只是作为一个例子。我也可以使用存储函数。

1 个解决方案

#1

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.

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

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.

解决方案是让proc在为您创建表之后将数据放入临时表中。此表仅在过程结束时对您的连接可用。如果其他人同时运行proc，并且任何其他连接都不可见，则不会引起冲突。

Add this to the procedure:

将此加入程序:

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

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.

当您的过程完成时，从foo中选择*;它会给你你从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.

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

#1