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

Question

我正在构建一个动态应用程序。 我有三个表：（EAV模型样式）

1. 项目（ItemId，ItemName）
2. 字段（FieldId，FieldName）
3. 字段值（ItemID，FieldId，Value）

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

预期结果：

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"];
}

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