MySQL 在mysql sproc中使用表名变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2754423/
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
use a variable for table name in mysql sproc
提问by Kyle
I'm trying to pass a table name into my mysql stored procedure to use this sproc to select off of different tables but it's not working...
我正在尝试将表名传递到我的 mysql 存储过程中，以使用此 sproc 从不同的表中进行选择，但它不起作用...
this is what I"m trying:
这就是我正在尝试的：
CREATE PROCEDURE `usp_SelectFromTables`(
IN TableName varchar(100)
)
BEGIN
SELECT * FROM @TableName;
END
I've also tried it w/o the @ sign and that just tells me that TableName doesn't exist...which I know :)
我也试过没有@ 符号，这只是告诉我 TableName 不存在......我知道:)
采纳答案by Jonathan Leffler
It depends on the DBMS, but the notation usually requires Dynamic SQL, and runs into the problem that the return values from the function depend on the inputs when it is executed. This gives the system conniptions. As a general rule (and therefore probably subject to exceptions), DBMS do not allow you to use placeholders (parameters) for structural elements of a query such as table names or column names; they only allow you to specify values such as column values.
它依赖于 DBMS，但这种表示法通常需要动态 SQL，并且会遇到函数的返回值取决于执行时的输入的问题。这给出了系统的概念。作为一般规则（因此可能会有例外），DBMS 不允许您使用占位符（参数）作为查询的结构元素，例如表名或列名；它们只允许您指定诸如列值之类的值。
Some DBMS do have stored procedure support that will allow you to build up an SQL string and then work with that, using 'prepare' or 'execute immediate' or similar operations. Note, however, that you are suddenly vulnerable to SQL injection attacks - someone who can execute your procedure is then able to control, in part, what SQL gets executed.
某些 DBMS 确实具有存储过程支持，允许您构建 SQL 字符串，然后使用“准备”或“立即执行”或类似操作来处理该字符串。但是请注意，您突然很容易受到 SQL 注入攻击 - 可以执行您的过程的人可以部分控制执行的 SQL。
回答by Angelo
SET @cname:='jello';
SET @vname:='dwb';
SET @sql_text = concat('select concept_id,concept_name,',@vname,' from enc2.concept a JOIN enc2.ratings b USING(concept_id) where concept_name like (''%',@cname,'%'') and 3 is not null order by 3 asc');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
回答by codewaggle
An extra bit that caused me problems.
额外的一点给我带来了问题。
I wanted to set the table name and field dynamically in a query as @kyle asked, but I also wanted to store the result of that query into a variable @awithin the query.
我想按照@kyle 的要求在查询中动态设置表名和字段，但我也想将该查询的结果存储到查询中的变量@a中。
Instead of putting the variable @ainto the concatliterally, you need to include it as part of the string text.
您需要将变量作为字符串文本的一部分包含在内，而不是将变量@a放在concat字面上。
delimiter //
CREATE PROCEDURE removeProcessed(table_name VARCHAR(255), keyField VARCHAR(255), maxId INT, num_rows INT)
BEGIN
SET @table_name = table_name;
SET @keyField = keyField;
SET @maxId = maxId;
SET @num_rows = num_rows;
SET @sql_text1 = concat('SELECT MIN(',@keyField,') INTO @a FROM ',@table_name);
PREPARE stmt1 FROM @sql_text1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
loop_label: LOOP
SET @sql_text2 = concat('SELECT ',@keyField,' INTO @z FROM ',@table_name,' WHERE ',@keyField,' >= ',@a,' ORDER BY ',@keyField,' LIMIT ',@num_rows,',1');
PREPARE stmt2 FROM @sql_text2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
...Additional looping code...
END LOOP;
END
//
delimiter ;
So in @sql_text1assign the result of the query to @awithin the string using:
因此，在使用以下命令@sql_text1将查询结果分配到@a字符串中时：
') INTO @a FROM '
') INTO @a FROM '
Then in @sql_text2use @aas an actual variable:
然后，在@sql_text2使用@a作为实际变量：
,' WHERE ',@keyField,' >= ',@a,' ORDER BY '
,' WHERE ',@keyField,' >= ',@一种,' ORDER BY '
