gpt4 book ai didi

Mysql - 用单个字符串搜索多个关键字?

转载 作者:行者123 更新时间:2023-11-29 00:09:38 26 4
gpt4 key购买 nike

我遇到了如何实现此搜索方法的问题。如何在单个消息的表列中实现多个关键字?

我当前的sql查询

SELECT *FROM rules WHERE keyword LIKE '%$message%'    

ruleskeyword 列中的示例数据(其中包含多个逗号分隔的关键字的字符串):

ASK, QUESTION

示例消息:

I want to ask and question for this thing

我该怎么做?

最佳答案

我完全支持 Barmars 关于规范化您的表格的建议。

然而,使用相对简单的测试数据,假设单词在单个查询中用常量(例如空格)分隔,则可以执行您想要的操作(尽管可能不可取)。

以下处理最多 100 个单词的搜索短语。

SELECT rules_id, COUNT(*) AS keyword_match_count, GROUP_CONCAT(message_word)
FROM rules
INNER JOIN
(
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(message, ' ', anInt), ' ', -1) AS message_word
FROM
(
SELECT 'I want to ask and question for this thing' AS message
) sub1
CROSS JOIN
(
SELECT units.i + tens.i * 10 + 1 AS anInt
FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub2
) sub3
ON FIND_IN_SET(message_word, rules.keyword)
GROUP BY rules_id
ORDER BY keyword_match_count DESC

SQL fiddle for it here:-

http://www.sqlfiddle.com/#!2/7678fa/2

关于Mysql - 用单个字符串搜索多个关键字?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25789312/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com