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

推荐答案

这不能直接完成，因为存储过程中无限制的select的输出是发送给客户端的结果集，但从技术上讲不是桌子.

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;将为您提供从proc中获得的收益.您几乎可以像任何桌子一样加入它.

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屋！