如何从符合特定条件的规范化 MySQL 5.7 结构中检索值 [英] How to retrieve values from a normalised MySQL 5.7 structure that match certain criterias
问题描述
我正在尝试规范我的 MySQL 5.7 数据模式并努力替换 SQL 查询:
I am trying to normalise my MySQL 5.7 data shema and strugle with replacing the SQL queries:
目前有一个表格包含每篇文章的所有属性:
At the moment there is one table containing all attributes of each article:
article_id | title | ref_id | dial_c_id
任务是检索与两个给定属性(ref_id 和 dial_c_id)匹配的所有文章，并检索所有其他属性.
The task is to retrieve all articles which match two given attributes (ref_id and dial_c_id) and also retrieve all their other attributes.
只有一张桌子，这很简单:
With just one table, this is straightforward:
SELECT *
FROM test.articles_test
WHERE
ref_id = '127712'
AND dial_c_id = 51
现在为了规范化，我创建了第二个表，其中存储了每篇文章的属性并删除了表文章中的属性:
Now in my effort to normalise, I have created a second table, which stores the attributes of each article and removed the ones in table articles:
表 1:
article_id | title
表 2:
article_id | attr_group | attribute
1 ref_id 51
1 dial_c_id 33
1 another 5
2 ..
我想检索所有文章详细信息，包括将 ref_id 和 dial_c_id 与这两个表 shema 匹配的 ALL 属性.
I would like to retrieve all article details including ALL attributes which match ref_id and dial_c_id with this two table shema.
有点像这样:
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
如何做到这一点?
推荐答案
您已使用实体-属性-值表来记录您的属性.
You have used an Entity-Attribute-Value table to record your attributes.
这与规范化相反.
命名引导您将不同属性放入同一列的规范化规则.您不能，因为这不是规范化做法.
Name the rule of normalization that guided you to put different attributes into the same column. You can't, because this is not a normalization practice.
要使用当前的 EAV 设计完成查询，您需要对结果进行透视，以便获得与原始表格一样的东西.
To accomplish your query with your current EAV design, you need to pivot the result so you get something as if you had your original table.
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 子句.
While the above query can produce the result you want, the performance will be terrible. It has to create a temp table for the subquery, containing all data from both tables, then apply the WHERE clause against the temp table.
将每个属性放在原始表中各自的列中确实会更好.
You're really better off with each attribute in its own column in your original table.
我了解您正试图在未来允许使用许多属性.这是一个常见问题.
I understand that you are trying to allow for many attributes in the future. This is a common problem.
看我的回答如何为多种产品设计产品表，其中每种产品都有很多参数
但你不应该称之为规范化"，因为它不是.它甚至没有非规范化.这是关系脱节.
But you shouldn't call it "normalised," because it isn't. It's not even denormalised. It's derelational.
你不能只用词来描述你想要的任何东西——尤其是不能与这个词的意思相反.我不能让自行车轮胎中的空气排出并说我在给它充气."
You can't just use words to describe anything you want — especially not the opposite of what the word means. I can't let the air out of my bicycle tire and say "I'm inflating it."
您评论说您正在努力使您的数据库可扩展".您还误解了可扩展"一词的含义.通过使用 EAV，您将创建一个结构，其中所需的查询难以编写且执行效率低下，并且数据占用 10 倍空间.它与可扩展性相反.
You commented that you're trying to make your database "scalable." You also misunderstand what the word "scalable" means. By using EAV, you're creating a structure where the queries needed are difficult to write and inefficient to execute, and the data takes 10x space. It's the opposite of scalable.
您的意思是您正在尝试创建一个可扩展的系统.这在 SQL 中实现起来很复杂，但我在链接到的其他 Stack Overflow 答案中描述了几种解决方案.您可能还喜欢我的演示文稿使用 MySQL 进行可扩展数据建模.
What you mean is that you're trying to create a system that is extensible. This is complex to implement in SQL, but I describe several solutions in the other Stack Overflow answer to which I linked. You might also like my presentation Extensible Data Modeling with MySQL.
这篇关于如何从符合特定条件的规范化 MySQL 5.7 结构中检索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
