如何在match-against中使用前缀通配符(例如"* abc") [英] How to use prefix wildcards like '*abc' with match-against

问题描述

我有以下查询:

SELECT * FROM `user` 
WHERE MATCH (user_login) AGAINST ('supriya*' IN BOOLEAN MODE)

哪个输出以'supriya'开头的所有记录.
现在我想要一种可以找到所有以例如结尾的记录的东西'abc'.
我知道*不能被预先添加，并且也不起作用，我进行了很多搜索，但找不到任何与此相关的内容.

Which outputs all the records starting with 'supriya'.
Now I want something that will find all the records ending with e.g. 'abc'.
I know that * cannot be preappended and it doesn't work either and I have searched a lot but couldn't find anything regarding this.

如果我给查询字符串priya ..它应该返回所有以priya结尾的记录.
我该怎么做?

If I give query the string priya ..it should return all records ending with priya.
How do I do this?

推荐答案

匹配不适用于开头的通配符，因此与*abc*匹配将不起作用.您将必须使用LIKE来实现:

Match doesn't work with starting wildcards, so matching with *abc* won't work. You will have to use LIKE to achieve this:

SELECT * FROM user WHERE user_login LIKE '%abc';

但是这将非常慢.

如果您确实需要匹配字符串的结尾，并且在性能使您丧命的时候不得不经常这样做，则解决方案是创建一个单独的列，在其中您可以反转字符串，因此您可以:

If you really need to match for the ending of the string, and you have to do this often while the performance is killing you, a solution would be to create a separate column in which you reverse the strings, so you got:

user_login user_login_rev
xyzabc     cbazyx

然后，您可以寻找'cba%'而不是寻找'%abc'，如果为该列建立索引，这会更快.如果您要搜索'cba*'，则可以再次使用MATCH.您只需要反转搜索字符串即可.

Then, instead of looking for '%abc', you can look for 'cba%' which is much faster if the column is indexed. And you can again use MATCH if you like to search for 'cba*'. You will just have to reverse the search string as well.

这篇关于如何在match-against中使用前缀通配符(例如"* abc")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！