MySQL：如何转换为EAV - 第2部分？ [英] MySQL: how to convert to EAV - Part 2?

问题描述

这是第1部分： MySQL：如何转换为EAV？

现在我也想做一些不同的事情。请说明下表：

Now I want to also do something different. Say I have the following table:

TABLE: one
=======================================
| id | fk_id | attribute  | value     |
=======================================
| 1  | 10    | first_name | John      |
| 2  | 10    | last_name  | Doe       |
| 3  | 55    | first_name | Bob       |
| 4  | 55    | last_name  | Smith     |
---------------------------------------

我想将其转换为此EAV模型：

I want to convert it to this EAV model:

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

TABLE: value
=====================================
| id | attribute_id | fk_id | value |
=====================================
| 1  | 1            | 10    | John  |
| 2  | 2            | 10    | Doe   |
| 3  | 1            | 55    | Bob   |
| 4  | 2            | 55    | Smith |
-------------------------------------

假设表属性和 value 已经定义。如何将表中的数据一个插入到两个目标表中。对我来说一个大问题是如何获得关系（ attribute.id => value.attribute_id ）右。 / p>

Assume the tables attribute and value are already defined. How do I insert the data from table one into the two target tables. One big problem for me is how to get the relationship (attribute.id => value.attribute_id) right.

推荐答案

INSERT INTO attribute
  (attribute)
SELECT DISTINCT
  attribute
FROM one ;

INSERT INTO value
  (attribute_id, fk_id, value)
SELECT 
  attribute.id, one.fk_id, one.value
FROM one
  JOIN attribute
    ON attribute.attribute = one.attribute ;

这篇关于MySQL：如何转换为EAV - 第2部分？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！