在LEFT JOIN中使用变量作为表名的MYSQL查询

SELECT var1,var2,var3,table_name 
FROM table1 LEFT JOIN table_name on var3=table_name.id

Meaning I want to dynamically left join table, depending on value of table_name from table1, since var3 is taken from there.

意思是我想动态地离开连接表，这取决于table1中table_name的值，因为var3是从那里获取的。

But the above query results in

但是上面的查询结果是

table table_name does not exist

table table_name不存在

My mistake of mysql limitation?

我的mysql限制错误？

2 个解决方案

#1

Table names, as well as column names, can't be dynamic in an SQL query. So you have to apply your logic programmatically, using 2 queries, or with a stored procedure, see an example here: http://forums.mysql.com/read.php?98,126506,126598#msg-126598

表名称以及列名称在SQL查询中不能是动态的。因此，您必须以编程方式应用逻辑，使用2个查询或使用存储过程，请参阅此处的示例：http：//forums.mysql.com/read.php？98,126506,126598 #msg-126598

#2

Another way is to unite all tables with a union query:

另一种方法是将所有表与联合查询联合起来：

SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`;

You could even prepare that as view:

您甚至可以将其作为视图进行准备：

CREATE VIEW `AllTheTables` AS
SELECT *, 42 as table_origin FROM `Table_42`
UNION ALL
SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`
UNION ALL
SELECT *, 69 as table_origin FROM `Table_69`;

And thus query it safely:

从而安全地查询它：

SELECT * FROM AllTheTables WHERE table_origin IN (44,58) AND something = 'foobar';
-- or --
SELECT * FROM AllTheTables WHERE table_origin = 42 AND something = 'the question';

In your exact case it could look like this:

在您的确切情况下，它可能如下所示：

SELECT var1, var2, var3, table_name 
FROM table1 LEFT JOIN AllTheTables ON table1.var3=AllTheTables.table_origin

#1

#2