在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 不允许您使用占位符(参数)作为查询的结构元素，例如表名或列名；它们只允许您指定诸如列值之类的值.

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