参数化表名称 [英] Parameterized table name

问题描述

这是我的问题:我想检查名称已参数化的表中的行，例如table_X. X的值来自另一个表，例如，在我的主表中，我有一个列c_id和一个列X，要连接的表的名称为table_X，毫无疑问它存在，并且具有相同的列，我将加入该表，以检查该表中是否存在c_id的值.

this is my problem: I want to check rows in a table which name is parameterized, something like table_X. The value of X comes from another table, so for example in my main table I have a column c_id and a column X, the table to join has name table_X, it EXISTS with no doubt, and it has the same column c_id, which I shall join on, to check if there are values of c_id in that table.

1. 我尝试了一个视图，但是没有成功，因为我无法在视图中放入参数化的表名.我可以参数化where子句和其他内容，但是没有表名.

1. I've tried a view, but without success, because I can't put a parameterized table name in a view. I can parameterize where clauses and other things, but no table names.

我尝试了一个过程，

SET @q = CONCAT('select blabla from table_', X);
PREPARE stmt FROM @q;
EXECUTE stmt;

但是过程无法返回值，我需要它，因为我需要知道参数化表中是否存在c_id值，否则它就没有用了.

but procedures can't return values, and I need it, because I need to know if there is the c_id value in the parameterized table, else it is useless.

我尝试了一个函数，但是存储的函数或触发器中不允许使用动态SQL"

I've tried a function, but "Dynamic SQL is not allowed in stored function or trigger"

那么我该怎么做才能提取这些数据?我从PHP调用此视图/函数/任何东西，我知道我可以从PHP端通过两个查询来做到这一点，但对于将来的实现，我需要在db端做到这一点. 有可能吗?

So what can I do to extract this data? I'm calling this view/function/whatever from PHP, and I know I can do it from PHP side, with two queries, but I need to do it db-side, for future implementations. Is it possible?

注意:我无法修改数据库的结构:)顺便说一句，它是 Limesurvey db，听起来像是疯狂的数据库结构，是吧?

NOTE: I can't modify the structure of the DB :) btw, it's the Limesurvey db, sounds like a crazy db structure, huh?

推荐答案

唯一的方法，而不是动态地构建查询，是对每种组合进行硬编码并挑选出所需的组合.

The only way, without dynamically building queries, is to hard code in every combination and pick out the one you want.

如果表名是存储过程的参数，则可以在IF块中.但这感觉笨拙.

If the table name is a parameter to a stored procedure, this can be in IF blocks. But it feels clunky.

如果每个表中的字段都相同，则可以将表合并在一起并从中选择...

If the fields from each table are the same, you can union the tables together and select from those...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, * FROM Table1
UNION SELECT 'Table2' AS tableName, * FROM Table2
UNION SELECT 'Table3' AS tableName, * FROM Table3
-- etc

SELECT * FROM myUnifiedStructure WHERE tableName = 'Table1'

如果每个表中的字段都不相同，那么您可能只对字段的子集感兴趣...

If the fields are different in each table, you may only be interested in a subset of the fields...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, field1 AS field1, field4 AS field2 FROM Table1
UNION SELECT 'Table2' AS tableName, field2 AS field1, field3 AS field2 FROM Table2
UNION SELECT 'Table3' AS tableName, field2 AS field1, field4 AS field2 FROM Table3
-- etc

或者您可以为源表中不存在的字段传递NULL ...

Or you can pass in NULLs for fields that don't exist in the source table...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, NULL   AS field1, field2 AS field2 FROM Table1
UNION SELECT 'Table2' AS tableName, field1 AS field1, field2 AS field2 FROM Table2
UNION SELECT 'Table3' AS tableName, field1 AS field1, NULL   AS field2 FROM Table3
-- etc

这篇关于参数化表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！