如何加入数据库和表，其中数据库名称是从当前表中的字段派生的？

I currently have a database server which stores a main database and, a table within that database has a structure similar to below

我目前有一个存储主数据库的数据库服务器,该数据库中的表具有类似于下面的结构

companies.companyInfo

ID | Name     | databaseName | otherinfo
---+----------+--------------+----------
0  | companyA | databaseA    | Extra1
1  | companyB | databaseB    | Extra2

and the company databases are as follows

公司数据库如下

databaseA.users

userID | Name    | Password  |  extra
-------+---------+-----------+---------
0      | user1   | pass1     | other1
1      | user2   | pass2     | other2

and databaseB is similar.

和databaseB类似。

What I want to do is get user information from one of the usertable, but to be linked from the main database

我想要做的是从一个usertable获取用户信息,但从主数据库链接

So I want to do is some kind of subselect to get all user details, but feed the database name into the subselect from the companies.companyInfo table query.

所以我想要做的是获取所有用户详细信息的某种子选择,但是将数据库名称提供给companies.companyInfo表查询的子选择。

I'm stuck.. please help!

我被卡住了......请帮忙!

1 个解决方案

#1

It's not entirely clear what you're after, but in principle one cannot use expressions (including the value of a field from a table lookup) as identifiers (e.g. name of a table or column).

你并不完全清楚你所追求的是什么,但原则上我不能使用表达式(包括表查找中的字段的值)作为标识符(例如表或列的名称)。

One workaround is to 'prepare' a statement from a string containing the desired query; such a string can be constructed by concatenating into it expressions in the place of identifiers:

一种解决方法是从包含所需查询的字符串中“准备”一个语句;这样的字符串可以通过在标识符的位置连接到表达式来构造:

SELECT CONCAT(
  'SELECT * FROM `', REPLACE(databaseName, '`', '``'), '`.users'
) INTO @qry FROM companies.companyInfo WHERE ID = 1;

PREPARE stmt FROM @qry;
EXECUTE stmt;

However, it may be just as easy to obtain the database name in your application using one SELECT query, and then perform the same concatenation to issue the second query from there.

但是,使用一个SELECT查询在应用程序中获取数据库名称可能同样容易,然后执行相同的串联以从那里发出第二个查询。

All of that said, perhaps your data structure needs further normalisation? Why not combine all of the databases into one, with a column in each table indicating to which company each record relates?

所有这些都说,也许您的数据结构需要进一步规范化?为什么不将所有数据库合并为一个,每个表中的列指示每个记录与哪个公司相关?

#1