MySQL 存储过程函数中的动态表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13705045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic table names in stored procedure function
提问by Urbycoz
I've written a stored procedure function to get a name from a table. The trouble is that I want the table name to be passed in as a parameter (there are several different tables I need to use this function with):
我编写了一个存储过程函数来从表中获取名称。问题是我希望将表名作为参数传入（有几个不同的表我需要使用这个函数）：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getName`(tableName VARCHAR(50), myId INT(11)) RETURNS VARCHAR(50)
begin
DECLARE myName VARCHAR(50);
SELECT
'name' INTO myName
FROM
tableName
WHERE
id=myId;
RETURN myName;
end
This method has an error because it uses the variable name "tableName" instead of the actual value of the variable.
此方法有错误，因为它使用变量名称“tableName”而不是变量的实际值。
I can work around this problem in a procedureby using a CONCATlike this:
我可以通过使用这样的程序来解决这个问题CONCAT：
SET @GetName = CONCAT("
SELECT
'name'
FROM
",tableName,"
WHERE
id=",myId,";
");
PREPARE stmt FROM @GetName;
EXECUTE stmt;
...but, when I try to do this in a function I get a message saying:
...但是，当我尝试在函数中执行此操作时，我收到一条消息：
Dynamic SQL is not allowed in stored function or trigger
存储函数或触发器中不允许使用动态 SQL
I tried to use a procedureinstead, but I couldn't get it to just return a value, like a function does.
我尝试使用一个过程，但我无法让它像函数一样只返回一个值。
So, can anyone see a way to get around this problem. It seems incredibly basic really.
那么，任何人都可以看到解决这个问题的方法。它看起来非常基本。
回答by Devart
If you want to buld a SQL statement using identifiers, then you need to use prepared statements; but prepared statements cannot be used in functions. So, you can create a stored procedure with OUT parameter -
如果要使用标识符构建 SQL 语句，则需要使用准备好的语句；但是准备好的语句不能在函数中使用。因此，您可以使用 OUT 参数创建一个存储过程 -
CREATE PROCEDURE getName
(IN tableName VARCHAR(50), IN myId INT(11), OUT myName VARCHAR(50))
BEGIN
SET @GetName =
CONCAT('SELECT name INTO @var1 FROM ', tableName, ' WHERE id=', myId);
PREPARE stmt FROM @GetName;
EXECUTE stmt;
SET myName = @var1;
END
Using example -
使用示例 -
SET @tableName = 'tbl';
SET @myId = 1005;
SET @name = NULL;
CALL getName(@tableName, @myId, @name);
SELECT @name;
回答by Payal
I agree that we can use stored procedure with the OUT parameter but what if we are creating this store procedure for web application where no. of other instances will use the same procedure and change this OUT variable. In mysql, OUT variable is like global variable. One instance will change its value and before our program read its value, it would be change by another instance on web. What is the other solution? I am facing the same issue, I want to have return value + I want to use dynamic table name.
我同意我们可以使用带有 OUT 参数的存储过程，但是如果我们为没有的 Web 应用程序创建这个存储过程呢？其他实例将使用相同的过程并更改此 OUT 变量。在mysql中，OUT变量就像全局变量。一个实例会改变它的值，在我们的程序读取它的值之前，它会被网络上的另一个实例改变。另一种解决方案是什么？我面临同样的问题，我想有返回值 + 我想使用动态表名。
