mysql - 如何在 MySQL 中使用属性/值表

Question

我继承了一个 mysql 数据库，该数据库有一个包含如下列的表:

object_id, property, value

它保存这样的数据:

1,first_name,Jane
1,last_name,Doe
1,age,10
1,color,red
2,first_name,Mike
2,last_name,Smith
2,age,20
2,color,blue
3,first_name,John
3,last_name,Doe
3,age,20
3,color,red
...

基本上我想要做的就是将此表视为常规表。如何获取 20 岁的人的 ID 号(或所有属性(property))(按姓氏和名字排序)？到目前为止我已经:

SELECT object_id FROM table WHERE property = 'age' AND value = '20'
union
SELECT object_id FROM table WHERE property = 'color' AND value = 'red'

但我不知道如何订购数据。

谢谢

Answer 1

在 EAV 模型中重构行的困难正是您所看到的。

您必须将数据从行转换为列才能获取“对象”，然后对其进行查询。

通常可以通过以下方式完成数据透视:

SELECT object_id
    ,MAX(CASE WHEN property = 'age' THEN value ELSE NULL END) AS age
    ,MAX(CASE WHEN property = 'color' THEN value ELSE NULL END) AS color
-- ...
FROM EAVTABLE
GROUP BY object_id

然后:

SELECT *
FROM (
    SELECT object_id
        ,MAX(CASE WHEN property = 'age' THEN value ELSE NULL END) AS age
        ,MAX(CASE WHEN property = 'color' THEN value ELSE NULL END) AS color
    -- ...
    FROM EAVTABLE
    GROUP BY object_id
) AS objects
WHERE age = '20' -- Note you may want to cast from char to proper types above (and these casts can fail).
ORDER BY last_name, first_name