在mysql sproc中使用变量作为表名 [英] use a variable for table name in mysql sproc

问题描述

我正在尝试将表名传递到我的mysql存储过程中，以使用此sproc从不同的表中进行选择，但是它不起作用...

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...

这就是我要尝试的:

CREATE PROCEDURE `usp_SelectFromTables`(
 IN TableName varchar(100)
)
BEGIN
        SELECT * FROM @TableName;
END

我也尝试了@符号，但它只是告诉我TableName不存在...我知道:)

I've also tried it w/o the @ sign and that just tells me that TableName doesn't exist...which I know :)

推荐答案

它依赖于DBMS，但是这种表示法通常需要Dynamic SQL，并且会遇到一个问题，即函数返回值取决于输入的时间.被执行.这给系统带来了麻烦.通常，DBMS不允许您为查询的结构元素(如表名或列名)使用占位符(参数)；因此，DBMS不允许使用占位符(参数).它们仅允许您指定诸如列值之类的值.

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字符串，然后对其进行处理.但是请注意，您突然容易受到SQL注入攻击的攻击-可以执行您的过程的人可以部分控制要执行的SQL.

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.

这篇关于在mysql sproc中使用变量作为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！