mysql - 如何从符合特定条件的规范化 MySQL 5.7 结构中检索值

Question

我正在尝试规范化我的 MySQL 5.7 数据模式并努力替换 SQL 查询:

目前有一个表包含每篇文章的所有属性:

article_id | title | ref_id | dial_c_id

任务是检索所有匹配两个给定属性(ref_id 和 dial_c_id)的文章，并检索它们的所有其他属性。

只有一张表，这很简单:

SELECT *
    FROM test.articles_test
WHERE
    ref_id = '127712' 
    AND dial_c_id = 51 

现在为了规范化，我创建了第二个表，它存储每篇文章的属性并删除了表文章中的属性:

表 1:

article_id | title 

表 2:

article_id | attr_group | attribute
1            ref_id       51
1            dial_c_id    33
1            another      5
2 ..

我想检索所有文章详细信息，包括与这两个表 shema 匹配 ref_id 和 dial_c_id 的 ALL 属性。

有点像这样:

SELECT 
     a.article_id,
     a.title, 
     attr.*
FROM test.articles_test a
INNER JOIN attributes attr ON a.article_id = attr.article_id
     AND ref_id = '127712' 
     AND dial_c_id = 51 

如何做到这一点？

Answer 1

您已经使用实体-属性-值表来记录您的属性​​。

这是规范化的对立面。

命名指导您将不同属性放入同一列的规范化规则。 你不能，因为这不是规范化实践。

要使用当前的 EAV 设计完成查询，您需要对结果进行透视，以便获得与原始表相同的结果。

SELECT * FROM (
    SELECT 
         a.article_id,
         a.title, 
         MAX(CASE attr_group WHEN 'ref_id' THEN attribute END) AS ref_id,
         MAX(CASE attr_group WHEN 'dial_c_id' THEN attribute END) AS dial_c_id
         -- ...others...
    FROM test.articles_test a
    INNER JOIN attributes attr ON a.article_id = attr.article_id
    GROUP BY a.article_id, a.title) AS pivot
WHERE pivot.ref_id = '127712' 
  AND pivot.dial_c_id = 51 

虽然上面的查询可以产生你想要的结果，但是性能会很糟糕。它必须为子查询创建一个临时表，包含两个表中的所有数据，然后对临时表应用 WHERE 子句。

在您的原始表格中，每个属性在其自己的列中确实更好。

---

我了解到您正试图在未来允许许多属性。这是一个常见问题。

看我的回答 How to design a product table for many kinds of product where each product has many parameters

但您不应该将其称为“规范化”，因为事实并非如此。它甚至没有非规范化。这是去关系。

你不能只用文字来描述你想要的任何东西——尤其是不能与这个词的意思相反。我不能放出自行车轮胎的气并说“我正在给它充气。”

您评论说您正在努力使您的数据库“可扩展”。您还误解了“可扩展”一词的含义。通过使用 EAV，您正在创建一个结构，其中所需的查询难以编写且执行效率低下，并且数据占用 10 倍的空间。 它与可扩展性相反。

您的意思是您正在尝试创建一个可扩展 的系统。这在 SQL 中实现起来很复杂，但我在我链接到的其他 Stack Overflow 答案中描述了几个解决方案。您可能也喜欢我的介绍Extensible Data Modeling with MySQL .