我确信这已经被询问/回答了,但我不知道这种操作是如何调用的,而且我的SQL知识有限。
我正在搜索一条合并两个表的SQL语句:
表USER:
ID hash
=========
1 abc
2 def
3 ghi
和USER_FIELD表:
ID user_id key value
=============================
1 1 firstname John
2 1 lastname Doe
3 2 firstname Justin
4 2 lastname Case
现在我想知道我是如何得到这个观点的:
ID hash firstname lastname
================================
1 abc John Doe
因此,如果我在USER_FIELD表中添加一个带有关键字"email"的附加条目,我会在最后一个结果中得到一个新列
这在MySQL中是可能的吗?还是我必须在PHP中更改结果?
这是一个好的DB设计吗?还是我应该放弃它,使用不同的(哪一个?)
您可以使用join
和group by
:
select u.*, firstname, lastname
from user u join
(select uf.user_id,
max(case when key = 'firstname' then value end) as firstname,
max(case when key = 'lastname' then value end) as lastname
from user_field uf
group by user_id
) uf
on uf.user_id = u.id;
您也可以使用一系列连接来完成此操作:
select u.*. firstname.value, lastname.value
from user u join
user_field firstname
on u.id = firstname.user_id and firstname.key = 'firstname' join
user_field lastname
on u.id = lastname.user_id and lastname.key = 'lastname';
您的结果似乎将其限制为仅一个用户id。您可能希望使用带有此筛选器的where
子句。
通常,对于平面类型的表(如用户数据),这不是一个好的做法。此结构通常用于存储用户指定的自定义字段或元数据,在这些字段或元数据中,单个实例可以没有或数百个字段或元数据。
避免这种情况的原因是,如果你想获得N用户属性,你必须在MySQL中进行N-1连接,并可能在应用程序中动态生成次优查询。只需将所有标准数据放在一个表中,就可以将这个user_field表用于仅在请求时检索的自定义字段。
如果为了能够有效地进行查询而使用这种结构,我会将user_field表更改为没有auto_increment id,而是将user_id和key设置为表的PRIMARY key。这样,用户的所有字段都将在表空间中挨着结束(假设您使用的是InnoDB),并且查找将是主键查找。进一步的一步是将键更改为tinyint,并使用查找ID而不是实际字符(您甚至可以在代码中使用常量来使其更干净)。
一对连接就可以了。
SELECT u.id, u.hash, uf_f.value AS firstname, uf_l.value AS lastname
FROM user AS u
LEFT JOIN user_field AS uf_f ON uf_f.user_id = u.id AND uf_f.key = 'firstname'
LEFT JOIN user_field AS uf_l ON uf_l.user_id = u.id AND uf_l.key = 'lastname'
您希望使用JOIN语句并使用ID和user_ID作为主题。
使用子查询获取数据的另一种简单方法。
SELECT hash, (select value from USER_FIELD where key = 'firstname' and user_id=6 ) as firstname, (select value from USER_FIELD where key = 'lastname' and user_id=6 ) as lastname FROM `USER` where ID = 6
但这不是一个好办法。若数据库变大,您将不得不在性能上妥协。你应该使用另一种设计模式。