sql - 选择所有行匹配的 id

Question

我有两个表:文档和元数据。 DOCUMENT 存储 ID 和一些我们不感兴趣的信息，METADATA 存储这些文档的“标签”。标签由键和值组成。

因此，对于一份文档，DOCUMENT 表中只有一个条目，但 METADATA 表中可能有多个条目。

现在我需要的是传递一组键/值，并从 METADATA 表中仅检索与所有键/值匹配的文档。这意味着“同时”检查不同的行，好吧，我真的不知道该怎么做。

简单示例:

META_KEY | META_VALUE | META_DOCUMENT_ID
----------------------------------------
Firstname| Chris      | 1
Lastname | Doe        | 1
Firstname| Chris      | 2
Lastname | Moe        | 2

因此，如果我使用以下标签进行查询:“Firstname”=“Chris”、“Lastname”=“Doe”，我想要 1 作为结果。如果我只指定 "Firstname"="Chris"我想要 1 和 2 作为结果。

非常感谢您的帮助!

---

编辑:

我可以计算必须匹配的标签数量吗？像这样:

从元数据中选择 meta_document_id、count(*)，其中 (meta_key = 'Firstname' 和 meta_value = 'Chris') 或 (meta_key = 'Lastname' 和 meta_value = 'Doe') 按 meta_document_id 分组

通过 count(*)，我可以轻松找出所有输入键/值对是否匹配。其性能如何？

Answer 1

嗯，您正在使用名为“键值”或“实体属性值”的数据库模型。

这通常不是最佳选择，您可以在以下问题中阅读更多相关内容:

• Key/Value pairs in a database table
• Key value pairs in relational database

对于这两种情况，您需要两个单独的查询，如下所示:

SELECT distinct META_DOCUMENT_ID
FROM METADATA 
WHERE meta_key = 'Firstname' and meta_value = 'Chris'

SELECT distinct m1.META_DOCUMENT_ID
FROM METADATA m1
JOIN METADATA m2
ON m1.META_DOCUMENT_ID = m2.META_DOCUMENT_ID
WHERE m1.meta_key = 'Firstname' and m1.meta_value = 'Chris'
  AND m2.meta_key = 'Lastname' and m2.meta_value = 'Doe'

编辑:

I suppose I'll have to join N times the table for N key/value pairs ?

这可以在没有连接的情况下完成，例如如下所示(假设每个 id 不超过 1 个 meta_key 值):

SELECT META_DOCUMENT_ID
FROM METADATA 
WHERE (meta_key, meta_value) IN
   ( ('Firstname' ,'Chris'), ('Lastname', 'Doe' ) )
GROUP BY META_DOCUMENT_ID
HAVING COUNT(*) = 2 /* 2 means that we are looking for 2 meta keys */

How is that going to run performance-wise ?

太糟糕了。请参阅上面链接中有关此模型的说明。

在很多情况下，这个查询必须进行全表扫描(特别是当我们要查找的属性/键的数量较多时)，对每个 id 的值进行计数，然后选择这些 count = 2 的 id。

在规范化模型中，这是一个简单的查询，可以使用索引快速仅选取名字 = 'Chris' 的这几行

SELECT *
FROM table 
WHERE firstname = 'Chris' and lastname = 'Doe'