mysql：从另一个查询中获取表名

There a similar questions to this but I can't quite get it to work. I have 2 databases. db 1 has user names

有一个类似的问题,但我不能让它工作。我有2个数据库。 db 1具有用户名

userdb.usertable.username

db 2 has config tables for each user which are called

db 2具有为每个被调用的用户配置的表

"their username" + _Config

I am trying to run a query that will loop through all the users of a specific type, get their usernames and then extract a value from their own config table.

我正在尝试运行一个查询,它将遍历特定类型的所有用户,获取他们的用户名,然后从他们自己的配置表中提取一个值。

this was close but would only work for 1 user.

这很接近,但只适用于1个用户。

SET @username:=(SELECT userdb.usertable.username FROM userdb.usertable WHERE version="free" );

SET @table:=concat(@username,"_Config");

SET @sql_text =concat("SELECT * FROM configdb.",@table);

PREPARE stmt FROM @sql_text;

EXECUTE stmt;

Any help would be appreciated.

任何帮助,将不胜感激。

1 个解决方案

#1

First off: GET RID OF THIS DATABASE DESIGN IMMEDIATELY.

首先:立即获取此数据库设计。

99% of time you should not need two databases for a single application.

99%的时间您不需要为单个应用程序使用两个数据库。

You may want to take a look at this too before going any further.

在进一步研究之前,你可能也想看看这个。

Second, it should be something like this:

其次,它应该是这样的:

Users_Table
id,username,(more user detail fields),config_value_1,config_value_2

Keep all of your user data in one table, unless a user can have more than one value for a config parameter. Then you would need a junction table like this

将所有用户数据保存在一个表中,除非用户可以为config参数设置多个值。然后你需要这样的联结表

Users_Table
id,username,other fields

Config_Value_1_Table
value_id,name

Config_Value_1_Users
user_id,value_id

A common real world example of this could look like this:

一个常见的现实世界的例子可能如下所示:

users
id,first_name,last_name,username,password_hash,password_salt,email,is_admin,is_banned,is_active

locations
id,location_name

user_locations
user_id,location_id

then all you have to do is write a pretty simple query to get all of your user data

那么你所要做的就是编写一个非常简单的查询来获取所有用户数据

#1