sqlite 多标签查找，在现有条件下如何做到最优的写法？（有偿请教）

2019-06-09 21:24:49 +08:00
 mostkia

首先说明一下条件： 表的结构(精简了一下，去掉了不需要的)：

  ID             TAG
----------------------------------

'0001'   '["菠萝","苹果","橘子"]'

'0002'   '["核桃","樱桃","橘子","蓝莓"]'

'0003'   '["香蕉","西瓜"]'

这里表的结构无法调整，但 tag 的值如果需要调整，可以调整。 数据库使用 SQLITE。控制程序语言使用 PHP 现在说明一下业务需求（希望达到以下效果）：

客户搜索：#核桃，应该只能匹配到 0002
客户搜索：#核桃#橘子，应该可以匹配到 0001 和 0002
最好能做到的情况：
客户输入：青苹果，能够依旧匹配到 0001(单独一个标签 like 能做到但多标签真的不会)

刚学 SQL 语法不太熟练，多条件真的跪了，希望有大佬能够指点一下，能用的话发 WX 红包-给个买烟钱还是能做到的，谢谢了～

5091 次点击
所在节点    MySQL
12 条回复
Takamine
2019-06-09 22:20:52 +08:00
建议上搜索引擎。（｡ò ∀ ó｡）
dongyx
2019-06-09 22:21:11 +08:00
能够改表结构最好改表结构，这个结构不符合关系型数据库的范式，改了之后这个问题会非常容易。

确实不能改的情况下，先保证 tag 值是有序的，就按字典序，比如["Peach", "Apple", "Banana"]按照["Apple", "Banana", "Peach"]存储；然后对于用户的搜索，同样先把标签按字典序排序，比如#Banana#Apple 解析成 Apple 和 Banana, Apple 在前，然后用百分号拼接：'%"Apple"%"Banana"%'，丢给数据库做 like。

最后一个青苹果问题，我暂时没有想到解决办法。
kekxv
2019-06-09 22:24:27 +08:00
@dongyx 做个形容词判定去除
chinvo
2019-06-09 22:40:10 +08:00
多对多建议单独一个表存关系
kevinWHX
2019-06-09 22:59:35 +08:00
自己实现一个简单的倒排序还是很简单的，把桃子 核桃当成 key，id 当成值。至于你说的青苹果，可以先用分词的第三方包分词，然后再查一下，你刚才组织好的倒排序即可
ashine
2019-06-09 23:37:15 +08:00
青苹果的问题有几种方法解决，1.分词，这个会比较复杂，可以只选取优先级高的去匹配； 2.建立多义的标签关联表，就是几个标签成为一组互相关联，缺点就是需要预制这些标签。
其实单纯多标签搜索也比较简单，对匹配出的一个或多个标签无脑 and instr(`TAG`, ?) > 0 就行了，但是要效率，就只有老老实实做好每一个标签的索引表。
precisi0nux
2019-06-10 06:26:48 +08:00
这种问题不是 SQLite 来解决的，上 elastic search 吧。
wenzhoou
2019-06-10 07:25:20 +08:00
如果数据量不大的话，在内存中做一个 tag 到编号的索引。

输入的青苹果先和 tag 做相似度比较，按高到低排序，在这里，输入为青苹果，输出为，青苹果相似度 100%, 青苹果和红苹果，相似度 80%，苹果，相似度 50%, 红苹果相似度 20%。

然后依次输出，上面各种 tag 对应的号码，去重复。
workwonder
2019-06-10 07:50:40 +08:00
postgresql 有数据类型的字段
workwonder
2019-06-10 07:50:51 +08:00
postgresql 有数组类型的字段
jingxyy
2019-06-10 08:46:12 +08:00
标准解法就是新建一个多对多关系表啦，青苹果的问题得处理分词，当然也有简单的分词法，单字、双字等可以不用引入单独的库。
mostkia
2019-06-10 20:29:20 +08:00
@Takamine @dongyx @chinvo @kevinWHX @ashine @precisi0nux @wenzhoou @jingxyy
感谢以上朋友的解答，因为业务关系，表结构无法修改，目前已经使用了 php 辅助解决此问题，
我的做法是：
首先将 post 传入的多个 tag 参数以空格切片为数组，然后用 php 的 for 循环动态拼接出以下这样的 SQL 字符串
```
SELECT * FROM myform WHERE instr(tag,"苹果") > 0
UNION
SELECT * FROM myform WHERE instr(tag,"橘子") > 0
```
从数据库中汇总取出所有符合预期的内容后，读取数据库基本就结束了。
至于优先级的排序，目前想到的是使用 php 的 array_diff 函数寻找传入标签和数据库内每个条目数组的差级，然后进行排序。
目前的情况是能用就好，因为业务本身也不太追求性能，不然数据库本身也不会使用 sqlite 了。
（青苹果那个看来很困难，暂时不考虑了。）

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://tanronggui.xyz/t/572208

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX