V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
nikoo
V2EX  ›  问与答

SQL 求教:标签相关问题

  •  
  •   nikoo · 2017-12-18 06:09:14 +08:00 · 2212 次点击
    这是一个创建于 2292 天前的主题,其中的信息可能已经有所发展或是发生改变。
    表 doc
    id   name
    --------------------------------
    1   文章 A
    2   文章 B
    3   文章 C

    表 tag
    doc_id   tag
    --------------------------------
    1    标签 A
    1    标签 B
    2    标签 A
    2    标签 C
    2    标签 D
    3    标签 B

    SQL 问题:
    1、如何列出包含"标签 A"的 doc 记录?
    2、如何列出不包含"标签 B"的 doc 记录?
    第 1 条附言  ·  2017-12-18 09:11:06 +08:00
    以下是我做的尝试:

    方案 1:
    select * from `tag` left join `doc` on (tag.doc_id=doc.id) where tag_id="标签 A"
    //这是 select tag 表去获取 doc 记录,问题是无法解决"问题 2",即没办法找出不包含"标签 B"的内容

    方案 2:
    select * from `doc` where "标签 A" in (select `tag_id` from `tag` where doc_id=doc.id)
    //可以用"in"与"not in"来解决问题 1 与问题 2,但这种子查询感觉效率堪忧,虽然 explain 看并没有全表扫描

    以上方案是我新手瞎试各位见笑,希望能有更好的解决方案,谢谢各位!
    27 条回复    2017-12-18 16:27:03 +08:00
    jamesxu
        1
    jamesxu  
       2017-12-18 08:39:27 +08:00 via iPhone
    这是非常非常基本的 SQL,楼主一看就是根本没有学过 SQL,连基本概念都不知道,买本 SQL 必知必会看两章吧
    18583826786
        2
    18583826786  
       2017-12-18 08:44:56 +08:00 via Android
    关联查询,百度下就知道了
    nikoo
        3
    nikoo  
    OP
       2017-12-18 08:53:15 +08:00
    @jamesxu 是啊,我是非常初级的新手

    如何在避免全表扫描情况下得出有效率的 SQL 语句呢?(特别是问题 2 ),请教各位。。。
    lhx2008
        4
    lhx2008  
       2017-12-18 09:13:21 +08:00 via Android
    这种多对多的情况,tag 再建一个表,旧表这边就是一个 doc id 对多个 tag id 就快很多了,全表扫描,数据库层那边肯定会扫的,但是有索引的话快很多
    nikoo
        5
    nikoo  
    OP
       2017-12-18 09:16:39 +08:00
    @lhx2008 谢谢!请问再建一个 tag 表是什么结构?这种情况下解决问题 1 与 2 的具体 SQL 是什么?
    halo
        6
    halo  
       2017-12-18 09:20:54 +08:00
    @jamesxu 如果真像你说的是 “非常非常基本的 SQL ”,你打 SQL 不比打这么多字容易多了?
    lhx2008
        7
    lhx2008  
       2017-12-18 09:25:56 +08:00 via Android   ❤️ 1
    @nikoo 去重
    tag 表就是
    tagid tagname
    然后标签 a b c d 就只用了 4 个 tagid
    就的 tag 表变成
    docid tagid
    1 1
    1 2
    2 1
    2 3
    ....
    第一个问题
    先查 tagid=1,然后在 join doc 表取出来就可以
    第二个问题,查 tagid 不等于 2 去重再 join doc 表
    更快的暂时没想出来
    lhx2008
        8
    lhx2008  
       2017-12-18 09:27:56 +08:00 via Android
    还有就是 tagid 和 docid 要建外键,就可以自动索引了,速度快很多的
    nikoo
        9
    nikoo  
    OP
       2017-12-18 09:31:34 +08:00
    @lhx2008 非常感谢!实际就是这样的,即 tag 表字段是 doc_id 与 tag_id,我是为了表述方便才把 tag_id 字段写了"标签 A"这样的字串

    你说的:第二个问题,查 tagid 不等于 2 去重再 join doc 表
    这个怎么实现?(我 APPEND 的内容写了我做的尝试,但用 left join 似乎没法实现“不包含”某标签)
    clino
        10
    clino  
       2017-12-18 09:37:45 +08:00
    方案 1 直接改成 where tag_id!="标签 B", 这样不就是'不包含"标签 B"的 doc 记录' ?
    lhx2008
        11
    lhx2008  
       2017-12-18 09:42:27 +08:00
    @nikoo select distinct d.* from tag_doc td join doc d where td.tagid != 2 and td.id = d.id
    tag_doc 是中间表,按照旧表就直接 !=标签 B 就可以
    lhx2008
        12
    lhx2008  
       2017-12-18 09:45:06 +08:00
    select distinct d.* from tag_doc td join doc d where td.tagid != 2 and td.docid = d.id
    刚刚打少了
    NullPoint
        13
    NullPoint  
       2017-12-18 09:55:09 +08:00   ❤️ 1
    1、select doc_id from tag where tag="标签 A" 如果有重复再去重
    2、select id from doc where id not in (select doc_id from tag where tag="标签 B")
    nikoo
        14
    nikoo  
    OP
       2017-12-18 10:07:46 +08:00
    @NullPoint 谢谢,就第 2 个 SQL 来说
    select id from doc where id not in (select doc_id from tag where tag="标签 B")

    select * from `doc` where "标签 B" not in (select `tag_id` from `tag` where doc_id=doc.id)

    哪个效率会更好一些?
    nikoo
        15
    nikoo  
    OP
       2017-12-18 10:23:52 +08:00
    @clino @lhx2008 我测试这个 SQL 是无法列出“不包含”的

    mysql> select * from tag where doc_id=1;
    +--------+--------+
    | doc_id | tag_id |
    +--------+--------+
    | 1 | 1 |
    | 1 | 2 |
    +--------+--------+
    2 rows in set

    可以确认 doc_id=1 包含 tag_id 1、2

    用 td.tag_id != 2 的方法尝试列出 “不包含” tag_id 2 的 doc 记录:
    mysql> select distinct d.* from `tag` td join `doc` d where td.tag_id != 2 and td.doc_id = d.id;
    +----+-------+
    | id | name |
    +----+-------+
    | 1 | doc_1 |
    | 2 | doc_2 |
    +----+-------+
    2 rows in set

    可以看到因为 doc_id=1 有两条 tag 记录,所以这条 SQL 并无法正确排除 doc_id=1
    halo
        16
    halo  
       2017-12-18 10:26:21 +08:00
    @NullPoint 这似乎和楼主的方案差不多?有没有可能实现包含 "标签 A" 同时不包含 "标签 B" 的方法?
    halo
        17
    halo  
       2017-12-18 10:32:25 +08:00
    @lhx2008 @clino select tag 表是列出所有 tag 赋值记录,不能简单的用 tagid != 2 来列出不包含

    兄弟是掉坑里了,感觉这可以用来当面试题
    SuperMild
        18
    SuperMild  
       2017-12-18 12:15:41 +08:00
    tag 那个表的结构根本就错了。改成这种结构问题就会迎刃而解:

    tag_id | tag_name | doc_ids

    每一个 tag 都是独立的,有一个 doc 列表,这样不管要找有标签 A 的文章,还是不包括含 A 的文章(补集),还是“包含 A 同时不包含 B ”( B 的补集与 A 的交集)都可以轻松筛选出来。
    tomczhen
        19
    tomczhen  
       2017-12-18 12:23:38 +08:00
    @SuperMild doc_ids 这个字段长度会非常大,而且文章修改标签时会相当蛋疼 。
    SuperMild
        20
    SuperMild  
       2017-12-18 12:29:42 +08:00
    @tomczhen 修改也不算很蛋疼,一篇文章最多十几个标签,就算 30 个吧,再多就失去标签的意义了,而标签的总量也不会太多(一般来说文章数量越多,标签总数与文章总数的比就越小)。至于这个字段的长度,的确需要评估一下。
    nikoo
        21
    nikoo  
    OP
       2017-12-18 12:32:46 +08:00
    @SuperMild 请问 doc_ids 这个字段是什么类型的?根据这个字段排除某标签的 SQL 应怎么写?
    nikoo
        22
    nikoo  
    OP
       2017-12-18 12:35:49 +08:00
    @SuperMild #19 的意思是,doc_ids 这个字段包含的是文章 ID,那么普通项目一个"技术"类标签下有个几千篇文章,于是这个 doc_ids 字段要存几千个 doc_id ?
    SuperMild
        23
    SuperMild  
       2017-12-18 12:39:36 +08:00
    SuperMild
        24
    SuperMild  
       2017-12-18 12:46:35 +08:00
    @nikoo 如果只有几千,那完全可以接受
    tomczhen
        25
    tomczhen  
       2017-12-18 12:52:05 +08:00
    JSON 类型也避免不了数据量过大的问题,反正查找算法还有数据结构就那些。可以预见的是热门标签的 doc_ids 增加是非常迅速的,大表还能分表解决,大字段该怎么解决?

    通过文章来查标签好解决,但是标签反查文章,用关系数据库真不好解决。无论采取那种结构,查询速度明显会因为标签对应文章数据量增加而变慢。

    感觉要么做成延迟生效,将查询结果缓存一段时间(不然被 cc 也够呛),追求实时的高一致性技术要求很高。
    NullPoint
        26
    NullPoint  
       2017-12-18 16:25:36 +08:00   ❤️ 1
    @halo select doc_id from tag where tag = 'A' and
    doc_id not in (select doc_id from tag where tag = 'B')
    NullPoint
        27
    NullPoint  
       2017-12-18 16:27:03 +08:00
    @nikoo 你这 SQL 语句都是错的,你具体操作下试试
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1138 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 18:38 · PVG 02:38 · LAX 11:38 · JFK 14:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.