mysql - 数据库EAV模型，根据搜索记录列表

Question

我正在构建一个动态应用程序。我有三个表:(EAV 模型样式)

1. 项目(项目 ID、项目名称)
2. 字段(FieldId、FieldName)
3. 字段值(ItemID、FieldId、Value)

您能告诉我如何编写单个查询以从 FieldId=4 等于 TRUE 的所有项目中获取起始 20 条记录吗？

预期结果:

Columns =>  ItemID | Name  | Field1 | Field2 |  Field3  
Each Row=>  ItemId | ItemName| Value1 | Value2 | Value3

重要问题:

1. 每个项目的字段数量未知
2. 我需要一个来编写一个查询。
3. 查询将在 100K 条记录上运行，因此性能值得关注。
4. 我使用的是 MySQL 5.0，因此需要 MYSQL 的解决方案

如果上述查询根本不可能，我是否应该对表进行非规范化？有什么建议吗？

Answer 1

EAV 设计是非规范化的。也就是说，它是一种非关系型设计。没有任何规范化规则会引导您使用 EAV 设计。

SQL 要求您在编写查询时知道列，并且结果集的每一行都具有相同的列。使用 EAV，如果您不知道每个项目有多少字段，唯一的解决方案是将它们作为行而不是列获取。

SELECT i.ItemID, i.ItemName, f.FieldName, v.Value
FROM Items i
JOIN FieldsValues v4 ON (v4.ItemID, v4.FieldID, v4.Value) = (i.ItemID, 4, TRUE)
JOIN FieldsValues v ON i.ItemID = v.ItemID
JOIN Fields f ON v.FieldID = f.FieldID;

您必须处理应用程序中的行。例如，使用 PHP:

<?php

$pdo = new PDO(...);
$sql = "...above query...";

$collection = array();

foreach ($pdo->query($sql) as $row) {
  $id = $row["ItemID"];
  if (!array_key_exists($id, $collection)) {
    $collection[$id] = new stdClass();
    $collection[$id]->Name = $row["ItemName"];
  }
  $collection[$id]->$row["FieldName"] = $row["Value"];
}

现在您有了一个对象数组，每个对象对应于数据库中的一个项目。每个对象都有其各自的字段集。