关于扩展表的查询问题

一张主表(user)，存人员基本信息
user_id,name,age,gender 等等
一张扩展属性表(user_info)
user_info_id,user_id,key,value

因为不同类型的人员下面一直会增加属性，因此除了基本的人员表，还使用了一张属性扩展表用来存额外的属性，现在有需求要做多条件筛选，可能会有十个以上的条件。
比如 age>20,gender = ‘男’，salary> 5000，address=‘广东’。其中 salary 和 address 是存在扩展表 info 中的，那么多条件查询的时候 sql 要怎么写才高效呢

目前的写法是
SELECT * FROM user
WHERE age> 20 AND gender = ‘男’ AND user_id IN (
SELECT user_id FROM user_info
WHERE (`key` ='salary' AND value > 5000) OR (`key` ='address' AND value = ‘广东’)
GROUP BY user_id )
但是用 IN 查询太慢了

而且而且扩展表里的属性有可能包含时间，value 是 string 格式的，如果涉及到时间区间的筛选又该怎么处理呢
请教各位大手子

user_id

value

Gender

属性

10 replies • 2020-04-29 10:06:15 +08:00

haidii

Apr 28, 2020

可以使用 elasticsearch 、或者 mongoDB 来做。
关系型数据库的话，最好这些信息放到一张表里面，
否则没有好的办法。

U7Q5tLAex2FI0o0g

Apr 28, 2020

没细看，感觉你这里的 OR 会有问题

IMFFA

Apr 28, 2020

@littleylv 暂时用起来没发现异常，这里的 OR 可能会导致什么问题呢

U7Q5tLAex2FI0o0g

Apr 28, 2020

@IMFFA #3 总感觉没法同时满足 salary> 5000 和 address=‘广东’？

IMFFA

Apr 28, 2020

@littleylv 啊啊，太低级了，居然会犯这种错(￣ω￣;)，我已经给逻辑绕晕了，感谢指出。那现在这个也不能用了 o(╥﹏╥)o

asAnotherJack

Apr 28, 2020

关于 2 楼说的问题，用下边这种方式是不是可以解决，子查询改成
SELECT user_id FROM user_info
GROUP BY user_id
HAVING COUNT(IF( `key` ='salary' AND value > 5000 , 1, null)) > 0
AND COUNT(IF( `key` ='address' AND value = ‘广东’, 1, null)) > 0

另外这种方式可行的话，是不是可以两个表 join 然后 group by XXX having XXXX

xuanbg

Apr 28, 2020

直接两表 join 不就好了吗？

xuanbg

Apr 28, 2020

如果用有包含在 info 表的条件，就 join 一次 info 表，如果是楼主说的情况，就这样写：select u.* from uesr u join user_info s on s.user_id = u.id and s.key = 'salary' and s.value > 5000 join user_info a on a.user_id = u.id and a.key = 'address' and a.value = '广东';

xizismile

Apr 28, 2020 via Android

感觉这张扩展属性表没有必要，趁早把一个人的多条数据合并成一条数据，要不然后续的查询需求搞死你

lumious

Apr 29, 2020

用 exists 条件查询

SELECT *
FROM USER
WHERE AGE > 20
AND GENDER = '男'
AND EXISTS (SELECT ''
FROM USER_INFO
WHERE USER.USER_ID = USER_INFO.USER_ID
AND KEY = 'salary'
AND VALUE > 5000
UNION ALL
SELECT ''
FROM USER_INFO
WHERE USER.USER_ID = USER_INFO.USER_ID
AND KEY = 'address'
AND VALUE = '广东')