如何根据包含表名的字符串从表中选择每一行?

In MySQL, I have a number of procedures which are more or less identical - they all perform the same (or very similar) operations, but they perform it on different tables.

在MySQL中，我有许多程序，它们或多或少相同——它们都执行相同的(或非常类似的)操作，但它们在不同的表上执行。

I'd like to reduce these to one procedure, parameterized by table name, if possible. For example, suppose I wanted to execute a generic select:

如果可能的话，我想把它们简化为一个过程，通过表名参数化。例如，假设我想执行一个通用选择:

SELECT * FROM TableFor("TableName")

Is this (or anything similar) possible in MySQL? Is it possible in any SQL dialect?

这在MySQL中可能吗?在任何一种SQL方言中都有可能吗?

Per Tomva's Answer

每Tomva的回答

A full example:

一个完整的例子:

DROP PROCEDURE IF EXISTS example;

CREATE PROCEDURE example(IN tablename VARCHAR(1000)) BEGIN
  SET @statement = CONCAT('SELECT * FROM ', @tablename);

  PREPARE statement FROM @statement;
  EXECUTE statement;
  DEALLOCATE PREPARE statement;
END;

CALL example('tablename');

2 个解决方案

#1

You can do this with a prepared statement.

您可以通过准备好的语句来实现这一点。

It will be something along the lines of

它将沿着

SET @stat = CONCAT('SELECT * FROM ', @tab'); 

PREPARE stat1 FROM @stat; 
EXECUTE stat1; 
DEALLOCATE PREPARE stat1;

Dynamic SQL does not work in a function, so make a Stored Procedure from this, and you will be able to provide the table parameter.

动态SQL在函数中不起作用，因此从这里创建一个存储过程，您将能够提供表参数。

#2

I am going to assume you know what a stored procedure is (I hope you do otherwise my answer will be useless)

我将假设您知道存储过程是什么(我希望您这样做，否则我的答案将无效)

First create a table object in your procedure

首先在过程中创建一个表对象

declare @tablenames table(name varchar)
insert into @MonthsSale (name) values ('firsttable')
insert into @MonthsSale (name) values ('secondtable')
...

You can add this little line to suppress the rows affected messages:

您可以添加这个小行来抑制受影响的消息:

SET NOCOUNT ON

Then create a cursor for this table and a variable to save your table name

然后为这个表创建一个游标，并创建一个变量来保存表名

DECLARE @TABLENAME VARCHAR
DECLARE tables_cursor CURSOR FOR SELECT name FROM @tablenames

Then loop through cursor and execute your code for each table name

然后循环游标，并为每个表名执行代码

OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor INTO @Tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  YOUR CODE USING THE @Tablename
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor

#1