MySQL - 使用存储过程结果定义 IN 语句 [英] MySQL - Using stored procedure results to define an IN statement

问题描述

我想使用存储过程来定义 select 语句的 IN 子句.

I'd like to use a stored procedure to define the IN clause of a select statement.

这是我正在尝试做的(简化版本):

This is (a simplified version of) what I'm trying to do:

SELECT * 
FROM myTable 
WHERE columnName IN (CALL myStoredProc)

myStoredProc 在数据库中执行一些复杂的逻辑并返回 columnName 的可能匹配值列表.上面的语句显然不起作用.如果有所不同，可以在另一个存储过程中执行 select 语句.

myStoredProc performs some complicated logic in the database and returns a list of possible matching values for columnName. The statement above does not work obviously. The select statement may be performed in another stored procedure if that makes a difference.

这在 mySQL 中完全可行吗?

Is this at all possible in mySQL?

推荐答案

您当前的存储过程有什么返回类型?你说的是列表"，所以 TEXT?

What return type does your current stored procedure have? You are speaking of "a list", so TEXT?

也许有更简单的方法，但您可以做的一件事(在另一个存储过程中)是构建另一个查询.

Maybe there's an easier way, but one thing you can do (inside another stored procedure) is to build another query.

要做到这一点，我们需要解决 MySQL 的两个限制: a) 要在存储过程中执行动态 SQL，它需要是一个准备好的语句.b) 准备好的语句只能从用户变量中创建.所以完整的SQL是:

To do that, we need to work around two limitations of MySQL: a) To execute dynamic SQL inside a stored procedure, it needs to be a prepared statement. b) Prepared statements can only be created out of user variables. So the complete SQL is:

SET @the_list = myStoredProc();
SET @the_query = CONCAT('SELECT * FROM myTable WHERE columnName IN (' , @the_list , ')');
PREPARE the_statement FROM @the_query;
EXECUTE the_statement;

如果您正在谈论从存储的例程返回结果集，然后将其用作表，那是不可能的.您需要创建一个临时表来解决此限制.

If you're talking about returning a result set from a stored routine and then using it as table, that is not possible. You need to make a temporary table to work around this limitation.

这篇关于MySQL - 使用存储过程结果定义 IN 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！