MySQL如何实现EAV表结构的多属性条件联合查询

EAV模型的核心特点是单条属性值记录只能存储一个实体的一个属性值，因此不能直接在单轮关联里叠加多个属性的筛选条件——因为同一行数据不可能同时匹配两个不同的属性标识，否则永远查不出结果。

核心实现思路 #

有两种经过验证的常用写法，适配不同的场景：

写法1：多轮关联逐条件匹配（效率最优） #

这种写法通过对属性值表做多次独立关联，每一次关联只匹配一个筛选条件，只有当所有关联都成功命中时，对应的实体才会被保留。

1. 先筛选出满足全部条件的实体ID集合：

SELECT o.id
FROM objects o
-- 匹配color=black的属性值
INNER JOIN object_attribute_values oav_color 
    ON oav_color.object_id = o.id
    AND oav_color.attribute_id = 1 -- 对应attributes表里color的属性ID
    AND oav_color.value = 'black'
-- 匹配rating在(5,10)开区间的属性值
INNER JOIN object_attribute_values oav_rating
    ON oav_rating.object_id = o.id
    AND oav_rating.attribute_id = 2 -- 对应attributes表里rating的属性ID
    -- 由于value是字符串类型，必须转成数值类型再做范围判断，避免字典序比较的错误
    AND CAST(oav_rating.value AS DECIMAL(10,2)) > 5
    AND CAST(oav_rating.value AS DECIMAL(10,2)) < 10
-- 匹配size=10的属性值
INNER JOIN object_attribute_values oav_size
    ON oav_size.object_id = o.id
    AND oav_size.attribute_id = 3 -- 对应attributes表里size的属性ID
    AND oav_size.value = '10'

2. 基于筛出来的实体ID，关联取出实体的全量属性值，拼接成要求的属性明细格式：

SELECT 
    matched_obj.id AS 对象ID,
    GROUP_CONCAT(
        CONCAT(a.attribute, ':', oav.value) 
        SEPARATOR ','
    ) AS 属性明细
FROM (
    -- 这里把上一步筛出来的符合条件的实体集合作为子查询
    SELECT o.id
    FROM objects o
    INNER JOIN object_attribute_values oav_color 
        ON oav_color.object_id = o.id
        AND oav_color.attribute_id = 1
        AND oav_color.value = 'black'
    INNER JOIN object_attribute_values oav_rating
        ON oav_rating.object_id = o.id
        AND oav_rating.attribute_id = 2
        AND CAST(oav_rating.value AS DECIMAL(10,2)) > 5
        AND CAST(oav_rating.value AS DECIMAL(10,2)) < 10
    INNER JOIN object_attribute_values oav_size
        ON oav_size.object_id = o.id
        AND oav_size.attribute_id = 3
        AND oav_size.value = '10'
) matched_obj
-- 关联取出这些实体的所有属性值，用于拼接明细
INNER JOIN object_attribute_values oav 
    ON oav.object_id = matched_obj.id
INNER JOIN attributes a 
    ON a.id = oav.attribute_id
GROUP BY matched_obj.id

注：GROUP_CONCAT是MySQL的字符串聚合函数，如果用的是其他数据库，换成对应的拼接函数即可：PostgreSQL用STRING_AGG，SQL Server用STRING_AGG，Oracle用LISTAGG。

写法2：聚合统计逐条件校验（代码最简洁） #

这种写法只需要关联一次属性值表，通过分组聚合统计每个实体满足的条件数量，只有三个条件全部满足时才会被保留，适合条件多、动态拼接查询的场景：

SELECT 
    o.id AS 对象ID,
    GROUP_CONCAT(
        CONCAT(a.attribute, ':', oav.value) 
        SEPARATOR ','
    ) AS 属性明细
FROM objects o
INNER JOIN object_attribute_values oav 
    ON oav.object_id = o.id
INNER JOIN attributes a 
    ON a.id = oav.attribute_id
GROUP BY o.id
-- 通过HAVING逐行校验每个实体是否满足全部条件
HAVING
    -- 命中color=black的条件计数为1
    SUM(a.attribute = 'color' AND oav.value = 'black') = 1
    -- 命中rating在(5,10)开区间的条件计数为1
    AND SUM(
        a.attribute = 'rating' 
        AND CAST(oav.value AS DECIMAL(10,2)) > 5 
        AND CAST(oav.value AS DECIMAL(10,2)) < 10
    ) = 1
    -- 命中size=10的条件计数为1
    AND SUM(a.attribute = 'size' AND oav.value = '10') = 1

常见踩坑提醒 #

• 你之前写的单属性查询有个小笔误：INNER JOIN join attributes多写了一个join关键字，运行时会报语法错误，修正为INNER JOIN attributes即可。
• 不要试图在单轮关联的结果上直接叠加多个属性的筛选条件——比如写WHERE a.attribute='color' AND a.attribute='rating'，这种写法因为同一行不可能同时匹配两个属性标识，永远查不出结果。
• 由于object_attribute_values.value是varchar类型，涉及数值范围比较时一定要做显式类型转换，否则会按字符串字典序比较，出现比如'10' < '6'的错误判定。
• 需求里的rating范围是开区间(5,10)，不包含5和10，不要写成BETWEEN 5 AND 10，否则会把边界值错误纳入结果。

内容的提问来源于stack exchange，提问作者Игорь