SQL 求教：标签相关问题

[nikoo]

表 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：
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 看并没有全表扫描

以上方案是我新手瞎试各位见笑，希望能有更好的解决方案，谢谢各位！

[jwk345]

这是非常非常基本的 SQL，楼主一看就是根本没有学过 SQL，连基本概念都不知道，买本 SQL 必知必会看两章吧

[18583826786]

关联查询，百度下就知道了

[nikoo]

@jamesxu 是啊，我是非常初级的新手

如何在避免全表扫描情况下得出有效率的 SQL 语句呢？（特别是问题 2 ），请教各位。。。

[lhx2008]

这种多对多的情况，tag 再建一个表，旧表这边就是一个 doc id 对多个 tag id 就快很多了，全表扫描，数据库层那边肯定会扫的，但是有索引的话快很多

[nikoo]

@lhx2008 谢谢！请问再建一个 tag 表是什么结构？这种情况下解决问题 1 与 2 的具体 SQL 是什么？

[halo]

@jamesxu 如果真像你说的是 “非常非常基本的 SQL ”，你打 SQL 不比打这么多字容易多了？

[lhx2008]

@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]

还有就是 tagid 和 docid 要建外键，就可以自动索引了，速度快很多的

[nikoo]

@lhx2008 非常感谢！实际就是这样的，即 tag 表字段是 doc_id 与 tag_id，我是为了表述方便才把 tag_id 字段写了"标签 A"这样的字串

你说的：第二个问题，查 tagid 不等于 2 去重再 join doc 表
这个怎么实现？（我 APPEND 的内容写了我做的尝试，但用 left join 似乎没法实现“不包含”某标签）

[clino]

方案 1 直接改成 where tag_id!="标签 B", 这样不就是'不包含"标签 B"的 doc 记录' ?

[lhx2008]

@nikoo select distinct d.* from tag_doc td join doc d where td.tagid != 2 and td.id = d.id
tag_doc 是中间表，按照旧表就直接 !=标签 B 就可以

[lhx2008]

select distinct d.* from tag_doc td join doc d where td.tagid != 2 and td.docid = d.id
刚刚打少了

[NullPoint]

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]

@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]

@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]

@NullPoint 这似乎和楼主的方案差不多？有没有可能实现包含 "标签 A" 同时不包含 "标签 B" 的方法？

[halo]

@lhx2008 @clino select tag 表是列出所有 tag 赋值记录，不能简单的用 tagid != 2 来列出不包含

兄弟是掉坑里了，感觉这可以用来当面试题

[SuperMild]

tag 那个表的结构根本就错了。改成这种结构问题就会迎刃而解：

tag_id | tag_name | doc_ids

每一个 tag 都是独立的，有一个 doc 列表，这样不管要找有标签 A 的文章，还是不包括含 A 的文章（补集），还是“包含 A 同时不包含 B ”（ B 的补集与 A 的交集）都可以轻松筛选出来。

[tomczhen]

@SuperMild doc_ids 这个字段长度会非常大，而且文章修改标签时会相当蛋疼 。

[SuperMild]

@tomczhen 修改也不算很蛋疼，一篇文章最多十几个标签，就算 30 个吧，再多就失去标签的意义了，而标签的总量也不会太多（一般来说文章数量越多，标签总数与文章总数的比就越小）。至于这个字段的长度，的确需要评估一下。

[nikoo]

@SuperMild 请问 doc_ids 这个字段是什么类型的？根据这个字段排除某标签的 SQL 应怎么写？

[nikoo]

@SuperMild #19 的意思是，doc_ids 这个字段包含的是文章 ID，那么普通项目一个"技术"类标签下有个几千篇文章，于是这个 doc_ids 字段要存几千个 doc_id ？

[SuperMild]

@nikoo JSON_ARRAY
https://dev.mysql.com/doc/refman/5.7/en/json.html

[SuperMild]

@nikoo 如果只有几千，那完全可以接受

[tomczhen]

JSON 类型也避免不了数据量过大的问题，反正查找算法还有数据结构就那些。可以预见的是热门标签的 doc_ids 增加是非常迅速的，大表还能分表解决，大字段该怎么解决？

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

感觉要么做成延迟生效，将查询结果缓存一段时间（不然被 cc 也够呛），追求实时的高一致性技术要求很高。

[NullPoint]

@halo select doc_id from tag where tag = 'A' and
doc_id not in (select doc_id from tag where tag = 'B')

[NullPoint]

@nikoo 你这 SQL 语句都是错的，你具体操作下试试