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
mostkia
V2EX  ›  MySQL

sqlite 多标签查找,在现有条件下如何做到最优的写法?(有偿请教)

  •  
  •   mostkia · 2019-06-09 21:24:49 +08:00 · 4833 次点击
    这是一个创建于 1753 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

      ID             TAG
    ----------------------------------
    
    '0001'   '["菠萝","苹果","橘子"]'
    
    '0002'   '["核桃","樱桃","橘子","蓝莓"]'
    
    '0003'   '["香蕉","西瓜"]'
    
    

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

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

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

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

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

    然后依次输出,上面各种 tag 对应的号码,去重复。
    workwonder
        9
    workwonder  
       2019-06-10 07:50:40 +08:00 via Android
    postgresql 有数据类型的字段
    workwonder
        10
    workwonder  
       2019-06-10 07:50:51 +08:00 via Android
    postgresql 有数组类型的字段
    jingxyy
        11
    jingxyy  
       2019-06-10 08:46:12 +08:00
    标准解法就是新建一个多对多关系表啦,青苹果的问题得处理分词,当然也有简单的分词法,单字、双字等可以不用引入单独的库。
    mostkia
        12
    mostkia  
    OP
       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 了。
    (青苹果那个看来很困难,暂时不考虑了。)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3313 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 13:20 · PVG 21:20 · LAX 06:20 · JFK 09:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.