EAV模型（万能表）业务场景

在某些动态扩展属性较多的业务场景中，数据库常采用EAV（Entity-Attribute-Value，实体-属性-值）模型设计，即俗称的"万能表"或"动态扩展表"。这种设计允许在不修改表结构的情况下无限增加业务字段。

主表 entity_main 结构如下：

entity_id	entity_name	status
1001	研发一组	1
1002	研发二组	1

扩展属性表 entity_attribute 结构如下：

entity_id	attr_key	attr_value
1001	name	张三
1001	age	25
1001	gender	Male
1001	height	175

查询场景与SQL实现

当后台管理系统需要对这些动态属性进行联合筛选，并同时返回特定属性值（例如：按性别筛选，并返回姓名、年龄、身高）时，可根据具体需求采用以下方案。

场景一：仅作为过滤条件，不返回属性值

若只需利用扩展表进行条件过滤，无需在结果集中展示其字段，使用 EXISTS 子查询是最高效的方式，能够避免不必要的表连接和结果集膨胀。

SELECT m.*
FROM entity_main m
WHERE EXISTS (
    SELECT 1 
    FROM entity_attribute a 
    WHERE a.entity_id = m.entity_id 
      AND a.attr_key = 'age' 
      AND CAST(a.attr_value AS UNSIGNED) < 21
);

场景二：既需条件过滤，又需返回属性值（行转列）

此时需要将多行属性记录转换为单行多列。相较于使用 GROUP_CONCAT，在单值属性场景下，使用条件聚合（MAX 或 MIN 配合 CASE WHEN）性能更优，且避免了 group_concat_max_len 系统变量带来的字符串截断风险。

方案A：条件聚合（推荐）

通过分组和条件判断，将行数据折叠为列。此方案只需扫描一次扩展表，I/O 成本最低。

SELECT 
    m.entity_id, 
    m.entity_name, 
    ext.name, 
    ext.age, 
    ext.gender, 
    ext.height
FROM entity_main m
LEFT JOIN (
    SELECT 
        entity_id,
        MAX(CASE WHEN attr_key = 'name' THEN attr_value END) AS name,
        MAX(CASE WHEN attr_key = 'age' THEN attr_value END) AS age,
        MAX(CASE WHEN attr_key = 'gender' THEN attr_value END) AS gender,
        MAX(CASE WHEN attr_key = 'height' THEN attr_value END) AS height
    FROM entity_attribute
    GROUP BY entity_id
) ext ON m.entity_id = ext.entity_id
WHERE ext.gender = 'Male';

方案B：多次 LEFT JOIN 自关联

通过为每个需要提取的属性单独关联一次扩展表。注意必须将 attr_key 的过滤条件放在 ON 子句中，以防止 LEFT JOIN 隐式退化为 INNER JOIN。此方案在属性较少时直观，但属性增多时会导致 JOIN 数量膨胀，执行计划变差。

SELECT 
    m.entity_id, 
    m.entity_name, 
    a1.attr_value AS name, 
    a2.attr_value AS age, 
    a3.attr_value AS gender, 
    a4.attr_value AS height
FROM entity_main m
LEFT JOIN entity_attribute a1 ON m.entity_id = a1.entity_id AND a1.attr_key = 'name'
LEFT JOIN entity_attribute a2 ON m.entity_id = a2.entity_id AND a2.attr_key = 'age'
LEFT JOIN entity_attribute a3 ON m.entity_id = a3.entity_id AND a3.attr_key = 'gender'
LEFT JOIN entity_attribute a4 ON m.entity_id = a4.entity_id AND a4.attr_key = 'height'
WHERE a3.attr_value = 'Male' 
  AND CAST(a2.attr_value AS UNSIGNED) < 30;

EAV模型的架构缺陷分析

尽管EAV模型提供了极高的 Schema 灵活性，但在实际工程落地与后期维护中存在以下显著缺陷：

• 数据类型丢失：所有属性值均存储为字符串（VARCHAR/TEXT），丧失了数据库原生的类型校验、约束以及高效的数值/日期计算能力。
• 索引失效与查询复杂：对数值或日期的范围查询需要隐式或显式类型转换（如 CAST），导致该列上的常规索引失效，极易引发全表扫描。
• 聚合计算成本高：列表展示需在应用层或数据库层进行行转列重组。若将压力转移至数据库，会消耗大量 CPU 与内存资源进行分组和聚合计算。
• 存储冗余：主键、外键、创建时间、逻辑删除标识等公共字段在每一行属性记录中重复存储，不仅浪费磁盘空间，还增加了数据同步更新的开销。
• 数据一致性维护困难：更新或删除实体时，需同步操作多行属性记录，事务锁竞争加剧，死锁风险上升。
• 数值精度与比对问题：数值型数据以字符串形式存储时，'10' 与 '10.00' 在字典序比对中不相等。若使用 INSTR 或 FIND_IN_SET 等函数进行模糊匹配，会进一步使 SQL 逻辑复杂化并降低执行效率。