MySQL：如何转换为 EAV - 第 3 部分？

Question

以前的相关文章：

• MySQL：如何转换为 EAV？
• MySQL：如何转换为 EAV - 第 2 部分？

给定一个表：

TABLE: foo
===============================
| id | first_name | last_name |
===============================
| 1  | John       | Doe       |
| 2  | Jane       | Smith     |
| 3  | Ronald     | McDonald  |
-------------------------------

如何做我获取此表并将其转换为这些表（EAV 实现）？：

TABLE: attribute
===========================
| id | fk_id | attribute  |
===========================
| 1  | 100   | first_name |
| 2  | 100   | last_name  |
---------------------------

TABLE: value
=========================================
| id | attribute_id | row_id | value    |
=========================================
| 1  | 1            | 1      | John     |
| 2  | 2            | 1      | Doe      |
| 3  | 1            | 2      | Jane     |
| 4  | 2            | 2      | Smith    |
| 5  | 1            | 3      | Ronald   |
| 6  | 2            | 3      | McDonald |
-----------------------------------------

注意：

• 将提供 attribute.fk_id。
• value.row_id 用于标识如何将值分组为原始表中的记录。

更新：另外，如何查询 EAV 表，以便使其再次看起来像表 foo 。

Answer 1

我对@Phil 用于填充 EAV 表的解决方案给予+1。一次插入一个属性。

这是反转 EAV 转换的另一种解决方案：

SELECT v.row_id AS id,
  MAX(IF(a.attribute='first_name',v.value,NULL)) AS first_name,
  MAX(IF(a.attribute='last_name',v.value,NULL)) AS last_name
FROM value v INNER JOIN attribute a
  ON v.attribute_id = a.id 
GROUP BY v.row_id

除了使用 EAV 之外，您已将所有值放入 VARCHAR(255) 列或其他列中，因此您丢失了有关原始列各自数据类型的信息。

如果不对属性名称进行硬编码，无论是像 @Phil 所示的连接，还是像我所示的列，实际上没有办法“动态”地做到这一点。这本质上与尝试编写动态数据透视查询相同的问题。

我在演讲中写了更多关于 EAV 的内容 实用面向对象模型在 SQL 和我的书中，SQL 反模式卷 1：避免数据库编程的陷阱。

Answer 2

我认为您唯一的希望是使用 foo 表。如果没有 ID，bar 基本上就没用了。

尝试这样的事情（假设attribute.id是一个自动递增主键）

INSERT INTO `attribute` (`fk_id`, `attribute`)
VALUES (100, 'first_name');

INSERT INTO `value` (`attribute_id`, `row_id`, `value`)
SELECT LAST_INSERT_ID(), `id`, `first_name`
FROM `foo`;

INSERT INTO `attribute` (`fk_id`, `attribute`)
VALUES (100, 'last_name');

INSERT INTO `value` (`attribute_id`, `row_id`, `value`)
SELECT LAST_INSERT_ID(), `id`, `last_name`
FROM `foo`;

要重建foo表，尝试这个

SELECT
    `fn`.`row_id` AS `id`,
    `fn`.`value` AS `first_name`,
    `ln`.`value` AS `last_name`
FROM `value` `fn`
INNER JOIN `attribute` `fn_a`
    ON `fn`.`attribute_id` = `fn_a`.`id`
    AND `fn_a`.`attribute` = 'first_name'
INNER JOIN `value` `ln`
    ON `fn`.`row_id` = `ln`.`row_id`
INNER JOIN `attribute` `ln_a`
    ON `ln`.`attribute_id` = `ln_a`.`id`
    AND `ln_a`.`attribute` = 'last_name'

_{呃，谢谢你提醒我为什么我讨厌这种模式}