在 MySQL 中的全文搜索前添加 *(星号) [英] Prepending an * (asterisk) to a Fulltext Search in MySQL

问题描述

我知道星号是可以附加到全文搜索词末尾的通配符，但是如果我搜索的关键字是后缀怎么办?例如，我希望能够搜索ames"并返回包含名称james"的结果.这是我当前的查询，它不起作用，因为您不能在全文搜索前添加星号.

I understand that the asterisk is a wildcard that can be appended to the end of fulltext search words, but what if my searched keyword is a suffix? For example, I want to be able to search for "ames" and have a result that contains the name "james" returned. Here is my current query which does not work because you cannot prepend asterisks to fulltext searches.

SELECT * FROM table WHERE MATCH(name, about, address) AGAINST ("*$key*" IN BOOLEAN MODE)

我会简单地切换到使用 LIKE，但对于我的数据库大小来说它太慢了.

I would simply switch to using LIKE, but it would be way too slow for the size of my database.

推荐答案

你可以做的是在你的数据库中创建另一个带有全文搜索索引的列，这个新列应该有你想要的列的反转字符串搜索，您将反转搜索查询并使用它在反转的列上进行搜索，以下是查询的样子:

What you could do is create another column in your database with full-text search index, this new column should have the reversed string of the column you are trying to search on, and you will reverse the search query and use it to search on the reversed column, here is how the query will look like:

SELECT * FROM table WHERE MATCH(column1) AGAINST ("$key*" IN BOOLEAN MODE) OR MATCH(reversedColumn1) AGAINST ("$reveresedkey*" IN BOOLEAN MODE)

• 第一个条件MATCH(column1) 反对(布尔模式中的$key*")例子:reversedColumn1==>Jmaes $reveresedkey*==>ames*将搜索以 ames ==> 不匹配的单词开头的单词

  • the first condition MATCH(column1) AGAINST ("$key*" IN BOOLEAN MODE) example: reversedColumn1==>Jmaes $reveresedkey*==>ames* will search for words that start with ames ==> no match

    秒条件MATCH(reversedColumn1) AGAINST ("$reveresedkey*" IN BOOLEAN MODE)例子:reversedColumn1==>semaJ $reveresedkey*==>sema*将搜索以 ames 结尾的单词 ==> 我们有匹配项

    the seconds condition MATCH(reversedColumn1) AGAINST ("$reveresedkey*" IN BOOLEAN MODE) example: reversedColumn1==>semaJ $reveresedkey*==>sema* will search for words that end with ames ==> we have a match

    如果你的文字很短，这可能不是一个坏主意:

    This might not be a bad idea if your text is short:

    这篇关于在 MySQL 中的全文搜索前添加 *(星号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！