mysql在字符串中查找单词并按“大多数匹配项”排序

thtygnil 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(321)

我有一张table：

--------------------------------------------------------------
|author_id   |  content                                      |
--------------------------------------------------------------
| 54         | I ate an apple and an orange for breakfast.   |
| 63         | Going to the store.                           |
| 12         | Should I wear the orange shirt?               |
--------------------------------------------------------------

我想找出有单词的行 apple , orange 运行查询 SELECT * FROM books WHERE content LIKE "%apple%" OR content LIKE "%orange%" 返回正确的值，但是因为true查询将包含2个以上的值，所以我想知道是否可以告诉sql按大多数匹配对结果进行排序，例如首先是包含apple和orange的行

mysql

来源：https://stackoverflow.com/questions/54116594/mysql-find-words-in-string-and-sort-by-most-matches

2条答案

按热度按时间

wh6knrhe1#

你可以用 MATCH(..) AGAINST (... IN BOOLEAN MODE) 计算一个你可以 ORDER BY 但要使用它，你必须像塔德曼在评论中说的那样添加全文索引。

SELECT 
 *
 , MATCH (Test.content) AGAINST ('apple orange' IN BOOLEAN MODE) AS relevance 
FROM 
 Test
WHERE 
 MATCH (Test.content) AGAINST ('apple orange' IN BOOLEAN MODE)
ORDER BY 
 MATCH (Test.content) AGAINST ('apple orange' IN BOOLEAN MODE) DESC

| author_id | content                                     | relevance            |
| --------- | ------------------------------------------- | -------------------- |
| 54        | I ate an apple and an orange for breakfast. | 0.25865283608436584  |
| 12        | Should I wear the orange shirt?             | 0.031008131802082062 |

请参见演示

赞(0）回复(0）举报 2021-06-16

ldxq2e6h2#

如果你对同一个词在同一个词中出现的次数不感兴趣 content ，您可以尝试以下操作：

SELECT
  *,
  (content LIKE "%orange%") + (content LIKE "%apple%") AS matches
FROM
  test
WHERE
  content LIKE "%orange%"
OR
  content LIKE "%apple%"
ORDER BY
  matches DESC;

在线示例：
小提琴

赞(0）回复(0）举报 2021-06-16

我来回答

mysql在字符串中查找单词并按“大多数匹配项”排序

2条答案

相关问题

热门标签

最新问答