寻求基于jsonb的合适EAV结构构建方案

嘿，针对你想在PostgreSQL里用JSONB构建EAV，同时保留属性表管控属性定义的需求，我来梳理下靠谱的实现方式～

你要的其实是「半结构化EAV」：用JSONB字段灵活存储实体的属性值，同时通过单独的attributes表来管控属性的元数据（名称、类型、约束等）。这种方式既避开了传统EAV模式表膨胀、查询复杂的问题，又能保证属性的规范性，不会出现随意乱加属性的情况。

1. 完善属性元数据表 #

首先要给attributes表增强约束和元数据字段，确保属性的唯一性、类型可控：

CREATE TABLE attributes (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) UNIQUE NOT NULL, -- 强制属性名唯一，避免重复定义
    data_type VARCHAR(50) NOT NULL CHECK (data_type IN ('text', 'integer', 'boolean', 'date', 'jsonb')), -- 限制属性值的类型
    is_required BOOLEAN DEFAULT false, -- 标记是否为必填属性
    default_value JSONB -- 用JSONB存储默认值，适配不同数据类型
);

2. 定义实体表并添加约束 #

entity表的JSONB字段需要和attributes表联动，确保只有已定义的属性才能被存储：

CREATE TABLE entity (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title TEXT NOT NULL,
    attributes JSONB DEFAULT '{}'::JSONB,
    -- 检查attributes中的所有键都存在于attributes表的name字段中
    CHECK (jsonb_object_keys(attributes) <@ (SELECT array_agg(name) FROM attributes))
);

这个CHECK约束能防止用户插入未在attributes表中定义的属性。如果属性表变动频繁，担心性能问题，可以换成触发器实现更灵活的校验。

3. 用触发器保证属性值类型正确 #

光约束属性名还不够，得确保属性值的类型符合attributes表的定义，这里用触发器来实现：

CREATE OR REPLACE FUNCTION validate_entity_attributes()
RETURNS TRIGGER AS $$
DECLARE
    attr RECORD;
    attr_value JSONB;
BEGIN
    FOR attr IN SELECT name, data_type FROM attributes WHERE name = ANY(jsonb_object_keys(NEW.attributes)) LOOP
        attr_value := NEW.attributes -> attr.name;
        -- 根据属性定义的类型校验值的合法性
        CASE attr.data_type
            WHEN 'text' THEN
                IF jsonb_typeof(attr_value) != 'string' THEN
                    RAISE EXCEPTION '属性 % 的值必须是文本类型', attr.name;
                END IF;
            WHEN 'integer' THEN
                IF jsonb_typeof(attr_value) != 'number' OR (attr_value::text !~ '^-?\d+$') THEN
                    RAISE EXCEPTION '属性 % 的值必须是整数类型', attr.name;
                END IF;
            WHEN 'boolean' THEN
                IF jsonb_typeof(attr_value) != 'boolean' THEN
                    RAISE EXCEPTION '属性 % 的值必须是布尔类型', attr.name;
                END IF;
            WHEN 'date' THEN
                IF jsonb_typeof(attr_value) != 'string' OR (attr_value::text !~ '^\d{4}-\d{2}-\d{2}$') THEN
                    RAISE EXCEPTION '属性 % 的值必须是YYYY-MM-DD格式的日期', attr.name;
                END IF;
            WHEN 'jsonb' THEN
                IF jsonb_typeof(attr_value) NOT IN ('object', 'array') THEN
                    RAISE EXCEPTION '属性 % 的值必须是JSON对象或数组', attr.name;
                END IF;
        END CASE;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给entity表绑定触发器，插入/更新时自动校验
CREATE TRIGGER trigger_validate_entity_attributes
BEFORE INSERT OR UPDATE ON entity
FOR EACH ROW EXECUTE FUNCTION validate_entity_attributes();

4. 数据插入示例 #

先定义属性，再插入符合规则的实体：

-- 插入属性定义
INSERT INTO attributes (name, data_type, is_required) VALUES
('color', 'text', true),
('weight', 'integer', false),
('in_stock', 'boolean', true),
('expiry_date', 'date', false);

-- 插入合法实体
INSERT INTO entity (title, attributes) VALUES
('红色纯棉T恤', '{"color": "red", "weight": 200, "in_stock": true}'::JSONB),
('蓝色牛仔裤', '{"color": "blue", "in_stock": false, "expiry_date": "2025-12-31"}'::JSONB);

如果尝试插入不符合类型的属性值（比如把weight设为字符串"200"），触发器会直接报错阻止操作。

• 属性表维护：删除属性时要先清理entity表中用到该属性的记录，或者给属性表加一个is_active字段标记废弃，避免破坏约束。
• 性能优化：给attributes字段创建GIN索引，能大幅提升JSONB的查询效率：

  CREATE INDEX idx_entity_attributes ON entity USING GIN (attributes);
  

• 默认值处理：可以在触发器中扩展逻辑，自动给未指定的必填属性填充attributes表中定义的默认值。

• 查询所有颜色为红色的实体：

SELECT id, title, attributes ->> 'color' AS color
FROM entity
WHERE attributes @> '{"color": "red"}'::JSONB;

• 查询重量大于150的实体：

SELECT id, title, (attributes ->> 'weight')::INTEGER AS weight
FROM entity
WHERE (attributes ->> 'weight')::INTEGER > 150;

• 把JSONB属性展开，关联属性表元数据（模拟传统EAV的查询结果）：

SELECT
    e.id,
    e.title,
    a.name AS attribute_name,
    a.data_type,
    e.attributes ->> a.name AS attribute_value
FROM entity e
CROSS JOIN attributes a
WHERE e.attributes ? a.name
ORDER BY e.id, a.name;

内容的提问来源于stack exchange，提问作者Ivanov