MySQL通过关联从句子中获取任何位置的完整单词匹配(而不是字符)

Question

我试图从存储在数据库中的至少一个准确的单词匹配(最好是文章或gerund)的句子(字符串)中获得准确的单词(子字符串)匹配，如下所示：

SELECT q 
FROM q 
WHERE q LIKE '%$q%' OR '$q' LIKE CONCAT('%', q, '%')
ORDER BY CASE WHEN q = '$q' THEN 0  
              WHEN q LIKE '$q%' THEN 1  
              WHEN q LIKE '%$q%' THEN 2  
              WHEN q LIKE '%$q' THEN 3  
              ELSE 4
         END, q ASC 
         LIMIT 10;

但是，它为值为$q = "best seo company for small business";的查询返回以下结果

best seo company for small business
best seo
best seo company
mpa
seo
seo com
seo company
small
small b
small bu

预期产出：

best seo company for small business
best seo
best seo company
seo
seo company
small

不期望的产出：

mpa
seo com
small b
small bu

如何排除不想要的输出？

备注：以下问题：

在MySQL中搜索“全文匹配”

使MySQL类似于返回完整单词匹配

MySQL查找子字符串匹配和按匹配全词分组

建议一些与我正在寻找的解决方案无关的答案。

Answer 1

解决这个问题的方法可以是REGEXP q+ It matches any string containing at least one q。

尽管它将与seo company匹配：

affordable seo company
atlanta seo company
austin seo company
automotive seo company
best atlanta seo company
best local seo company
best los angeles seo company
best organic seo company
best real estate seo company
best seo company
best seo company for google
best seo company for lawyers
best seo company for small business
best seo company in india
best seo company in the usa
best seo company in the world
best seo company india
best seo company los angeles
best seo company miami
boise seo company
boston seo company
california seo company
charleston seo company
charlotte seo company
cheap seo company

除了它本身之外，它不能将best seo company for small business与上述任何一个术语相匹配。

试试这个：

SELECT q 
FROM q 
WHERE q REGEXP '$q+'
ORDER BY CASE WHEN q = '$q' THEN 0  
              WHEN q LIKE '$q%' THEN 1  
              WHEN q LIKE '%$q%' THEN 2  
              WHEN q LIKE '%$q' THEN 3  
              ELSE 4
         END, q ASC 
         LIMIT 10

Answer 2

WHERE '$q' REGEXP '[[:<:]]q[[:>:]]'

但是，小心点。如果$q中有标点符号，则可能会出现语法错误。所以，一定要逃离它。