mysql查询来匹配句子和字段中的关键字 [英] mysql query to match sentence against keywords in a field

问题描述

我有一个包含关键字列表的mysql表格：

  id |关键字
 --- + -------------------------------- 
 1 |苹果，橘子，梨
 2 |桃子，菠萝，橘子
  

我试图弄清楚如何使用输入字符串：
约翰喜欢吃苹果

有没有一种mysql查询类型可以查询带有句子的字段并返回结果（在我的例子中，记录＃ 1）？

解决方案

一种方法可以将苹果，橙子，梨至 apple | oranges | pear 并使用 RLIKE （即正则表达式）与之匹配。

$ b $例如，'约翰喜欢吃苹果'匹配正则表达式'apple | orange | pears'。'

首先，将'apple，oranges，梨'到正则表达式，用'|'替换全部'，'，使用 REPLACE 。然后使用 RLIKE 选择匹配的关键字条目：

  SELECT * 
 FROM keywords_table 
其中'John喜欢吃苹果'RLIKE REPLACE（关键字，'，'，'|'）; 
  

但是，这取决于您的逗号分隔是否一致（即，如果有一行看起来像苹果，桔子这不会起作用，因为 REPLACE 会替换逗号后跟一个空格（按照您的示例行）。

我也不认为它会很好地放大。

而且，如果你有一个像'约翰喜欢吃菠萝'这样的句子，它会匹配上面的两行（因为它有'apple'），然后你可以尝试给正则表达式添加单词边界（即 WHERE $ sentence RLIKE'[[：<：]]（apple | oranges | pears）[[：>]]'），但这会在您有复数时'苹果'不匹配'[wordboundary] apple [wordboundary]'）。

I have a mysql table with a list of keywords such as:

id | keywords
---+--------------------------------
 1 | apple, oranges, pears
 2 | peaches, pineapples, tangerines

I'm trying to figure out how to query this table using an input string of: John liked to eat apples

Is there a mysql query type that can query a field with a sentence and return results (in my example, record #1)?

解决方案

One way to do it could be to convert apple, oranges, pears to apple|oranges|pears and use RLIKE (ie regular expression) to match against it.

For example, 'John liked to eat apples' matches the regex 'apple|orange|pears'.

First, to convert 'apple, oranges, pears' to the regex form, replace all ', ' by '|' using REPLACE. Then use RLIKE to select the keyword entries that match:

SELECT * 
FROM keywords_table
WHERE 'John liked to eat apples' RLIKE REPLACE(keywords,', ','|');

However this does depend on your comma-separation being consistent (i.e. if there is one row that looks like apples,oranges this won't work as the REPLACE replaces a comma followed by a space (as per your example rows).

I also don't think it'll scale up very well.

And, if you have a sentence like 'John liked to eat pineapples', it would match both of the rows above (as it does have 'apple' in it). You could then try to add word boundaries to the regex (i.e. WHERE $sentence RLIKE '[[:<:]](apple|oranges|pears)[[:>:]]'), but this would screw up matching when you have plurals ('apples' wouldn't match '[wordboundary]apple[wordboundary]').

这篇关于mysql查询来匹配句子和字段中的关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！