在MySQL中匹配单词/后缀的相似/变体 [英] Match similar/variations of words / suffixes in MySQL

问题描述

如何匹配MySQL中单词的变体，例如，对会计的搜索应与会计，会计，会计等相匹配.我在共享主机上，因此无法向levenshtein等MySQL添加任何功能.

How can I match variations on words in MySQL, for example a search for accountancy should match accountant, accountants, accounting etc. I'm on shared hosting so can't add any functions to MySQL such as levenshtein.

我想要类似于Google在搜索"会计课程"时如何匹配"会计课程"和"会计课程"的方法.

I want something similar to how Google matches 'accounting course' and 'accountancy courses' when searching for 'accountant courses'. Example.

我的服务器语言是php，如果只能在其中实现而不是在SQL中实现.

My server language is php, if it's only possible to implement it there and not in SQL.

当前语句如下.

SELECT 
  pjs.title,
  MATCH (pjs.title) AGAINST ('accountancy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) AS rel1,
  MATCH (pjs.description) AGAINST ('accountancy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) AS rel2,
  MATCH (
    pjs.benefits,
    pjs.experienceRequirements,
    pjs.incentives,
    pjs.qualifications,
    pjs.responsibilities,
    pjs.skills
  ) AGAINST ('accountancy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) AS rel3
FROM
  pxl_jobsearch AS pjs 
ORDER BY (rel1 * 5) + (rel2 * 1.5) + (rel3) DESC;

推荐答案

MySQL在全文搜索方面不是很擅长，您可能希望使用其他引擎.我最喜欢的一个是Sphinx( http://sphinxsearch.com/)，但是还有其他一些.这些支持大多数都是开箱即用的.

MySQL isn't very good at full text search and you'd probably want to use other engines. My favorite one is Sphinx (http://sphinxsearch.com/) but there are others as well. Most of these support stemming out of the box.

如果您有大表并打算使用阻止，则MySQL的性能可能会很差.

If you have large tables and are going to use stemming the performance of MySQL will probably be very bad.

如果您不能使用Sphinx，请查看以下php脚本 http://tartarus.org/~martin/PorterStemmer/php.txt

If you can't use Sphinx, take a look at this php script http://tartarus.org/~martin/PorterStemmer/php.txt

使用此功能，您可以使用词干，以及搜索词干

With this you can use stemming, and the search on the stemmed words.

这篇关于在MySQL中匹配单词/后缀的相似/变体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！