V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
IMFFA
V2EX  ›  MySQL

关于扩展表的查询问题

  •  
  •   IMFFA · 2020-04-28 16:20:34 +08:00 · 2690 次点击
    这是一个创建于 1652 天前的主题,其中的信息可能已经有所发展或是发生改变。
    一张主表(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 格式的,如果涉及到时间区间的筛选又该怎么处理呢
    请教各位大手子
    10 条回复    2020-04-29 10:06:15 +08:00
    haidii
        1
    haidii  
       2020-04-28 16:44:40 +08:00
    可以使用 elasticsearch 、或者 mongoDB 来做。
    关系型数据库的话,最好这些信息放到一张表里面,
    否则没有好的办法。
    littleylv
        2
    littleylv  
       2020-04-28 16:48:38 +08:00
    没细看,感觉你这里的 OR 会有问题
    IMFFA
        3
    IMFFA  
    OP
       2020-04-28 17:06:25 +08:00
    @littleylv 暂时用起来没发现异常,这里的 OR 可能会导致什么问题呢
    littleylv
        4
    littleylv  
       2020-04-28 17:12:52 +08:00
    @IMFFA #3 总感觉没法同时满足 salary> 5000 和 address=‘广东’?
    IMFFA
        5
    IMFFA  
    OP
       2020-04-28 17:17:36 +08:00
    @littleylv 啊啊,太低级了,居然会犯这种错( ̄ω ̄;),我已经给逻辑绕晕了,感谢指出。那现在这个也不能用了 o(╥﹏╥)o
    asAnotherJack
        6
    asAnotherJack  
       2020-04-28 17:46:55 +08:00
    关于 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
        7
    xuanbg  
       2020-04-28 19:26:05 +08:00
    直接两表 join 不就好了吗?
    xuanbg
        8
    xuanbg  
       2020-04-28 19:35:46 +08:00
    如果用有包含在 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
        9
    xizismile  
       2020-04-28 19:39:37 +08:00 via Android
    感觉这张扩展属性表没有必要,趁早把一个人的多条数据合并成一条数据,要不然后续的查询需求搞死你
    lumious
        10
    lumious  
       2020-04-29 10:06:15 +08:00
    用 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 = '广东')
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3089 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 13:50 · PVG 21:50 · LAX 05:50 · JFK 08:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.