问一个复杂的基于产品属性的 SQL 查询

现在有一个简单的产品属性表, KV 形式, 比如:
pid,name,value
(1,"num","2"),
(1,"category","M"),
(1,"price","high"),
(1,"heat","true"),
(1,"tag","4TX")

现在要根据选择的属性找出产品, 如果是每种单选的话吗, 大概知道怎么做, 就是
select * from ps where value in ("2","M") group by pid having count(*)=2

虽然也不知道这个做法有没有问题, 但至少能用, 但是如果是更复杂的组合就完全不知道怎么写了, 比如要满足 num=2 同时 category=M, 然后 price=high 和 heat=true 两个任意满足一个, 这个完全不知道怎么写

有大神能帮忙吗, 或者这个属性表是不是不应该这么设计?

求求

pid

num

category

Heat

6 条回复 2023-09-22 12:33:23 +08:00

Waverly

2023 年 9 月 22 日

SELECT *
FROM ps
WHERE (name = 'num' AND value = '2' AND pid IN (SELECT pid FROM your_table WHERE name = 'category' AND value = 'M'))
AND (name = 'price' AND value = 'high' OR name = 'heat' AND value = 'true');

swulling

2023 年 9 月 22 日

表没问题。你这种多条件查询就只能二次筛选。就是 1L 的办法，先筛出一个条件，再筛第二个条件。

MoYi123

2023 年 9 月 22 日

建议放 json 里面, 然后建个倒排索引. 不然太折磨了.

yesterdaysun

2023 年 9 月 22 日

@MoYi123 大哥, 能详细说一下这个方案吗? 是不是要换 ES, MySQL 能搞么

opengps

2023 年 9 月 22 日

已经这么设计了吗？实在不行，用触发器自动更新到新的表结构吧，这种表写入量少，触发器没有太高并发还算适合

BeautifulSoap

2023 年 9 月 22 日

极其经典的 EAV 模型问题。本质上产生这个问题的原因是想用 RDS 来实现 NoSQL 的功能。mysql 后来添加了 json 字段就是为了解决这个问题

https://juejin.cn/post/6997230227868876830
https://zhuanlan.zhihu.com/p/404294076

我的项目实践经验就是，用 join 是最好的方法。如果你是 mysql 新版本之类的也可以改数据库结构，直接定义个 json 字段把商品信息的 json 存进去倒也行（反正已经这样定义数据了，你查询速度怎么都快不了了）。最好最彻底的解决办法就是上 NoSQL 或基于文档的数据库，如 mongodb ，elasticsearch 之类的