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

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

这是一个创建于 1994 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

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

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

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

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

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

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

橘子

核桃

sqlite

标签

12 条回复 • 2019-06-10 20:29:20 +08:00

Takamine

2019-06-09 22:20:52 +08:00 via Android

建议上搜索引擎。（｡ò ∀ ó｡）

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 via Android

@dongyx 做个形容词判定去除

chinvo

2019-06-09 22:40:10 +08:00 via iPhone

多对多建议单独一个表存关系

kevinWHX

2019-06-09 22:59:35 +08:00 via iPhone

自己实现一个简单的倒排序还是很简单的，把桃子核桃当成 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 via Android

这种问题不是 SQLite 来解决的，上 elastic search 吧。

wenzhoou

2019-06-10 07:25:20 +08:00 via Android

如果数据量不大的话，在内存中做一个 tag 到编号的索引。

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

然后依次输出，上面各种 tag 对应的号码，去重复。

workwonder

2019-06-10 07:50:40 +08:00 via Android

postgresql 有数据类型的字段

workwonder

2019-06-10 07:50:51 +08:00 via Android

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 了。
（青苹果那个看来很困难，暂时不考虑了。）