多分类动态字段帖子的数据库Schema设计与搜索优化问题

可落地的调整方案 #

方案1：改用JSON类型存储动态字段（改造成本最低，优先推荐） #

现在主流数据库（MySQL 5.7+、PostgreSQL等）均原生支持JSON数据类型，你只需将原ad_body字段的类型从varchar/text改为JSON即可：

• 存储时直接把动态字段拼接为键值对格式的JSON，不需要手动用逗号拼接：
  汽车类存储内容示例：{"production_year":2015,"mileage":"999km","door_count":"4+"}
  图书类存储内容示例：{"author":"James","damage_status":"no damage"}
• 搜索时直接用数据库自带的JSON函数做精准匹配，比如MySQL中查询里程大于1000km的汽车，语法为：

  SELECT * FROM post WHERE JSON_EXTRACT(ad_body, '$.mileage') > 1000
  

• 高频搜索的字段还可以创建虚拟列并添加索引，搜索性能和普通实体列完全一致，完全满足精准搜索需求。

方案2：采用EAV（实体-属性-值）模型（扩展性最强，适合分类多的场景） #

如果后续会频繁新增分类、动态字段数量多，可调整为三表结构：

• 分类配置表：存储分类基础信息，字段包括category_id、category_name
• 分类字段配置表：存储每个分类对应的动态字段规则，字段包括field_id、category_id、field_name、field_type
• 帖子动态属性表：存储每个帖子的动态字段值，字段包括post_id、field_id、field_value
  搜索时通过联表过滤即可实现精准匹配，比如查询作者为James的图书，只需关联帖子表、分类配置表、动态属性表，过滤分类为图书、字段为作者、值等于James即可。后续新增分类不需要修改表结构，只需在配置表新增对应规则即可。

方案3：结构化字符串存储（仅适合小数据量临时过渡） #

如果暂时没法升级数据库或调整表结构，可将原逗号拼接的格式改为键值对拼接格式，比如用key1:value1|key2:value2的格式存储，搜索时用LIKE模糊匹配，比如查询作者为James的图书语法为：

SELECT * FROM post WHERE ad_body LIKE '%author:James%'

该方案不需要改造表结构，但性能差、无法加索引，仅适合数据量小于1万条的临时场景，不建议长期使用。

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