MySQL LIKE％string％不太宽容。还有什么我可以用吗？ [英] MySQL LIKE %string% not quite forgiving enough. Anything else I can use?

问题描述

我有一个客户询问他们的搜索是否搜索可以根据用户输入以多种格式搜索的公司名称，例如存储在数据库中的公司是AJR Kelly Ltd，如果是用户搜索AJR Kelly找到，使用;

I've got a client who's asking if their search, which searches for company names which could be searched for in several formats depending on user input, such as the company stored in the database being A J R Kelly Ltd for instance, If a user searches "A J R Kelly" it's found, using;

<cfif pctermsCount gt 0>
AND (LOWER(p.name)  LIKE '%#pcTerms#%')
</cfif>

如果他们搜索Kelly的公司被发现，但如果他们搜索一个破碎的版本

If they search for "Kelly" the company is found, but if they search for a broken version of the string like "A J Kelly" or "AJ Kelly" it's not found.

有没有什么我可以做，只是一点点更宽容？

Is there anything I can do to make it just a little bit more forgiving?

感谢。

推荐答案

如果您使用MyISAM，文本索引。 请参阅本教程

If you're using MyISAM, you can use full text indexing. See this tutorial

如果您'重新使用不同的存储引擎，则可以使用第三方全文本引擎（如sphinx），该引擎可充当存储引擎

If you're using a different storage engine, you could use a third party full text engine like sphinx, which can act as a storage engine for mysql or a separate server that can be queried.

使用MySQL全文索引搜索 AJ Kelly 会匹配 AJ Kelly （不会混淆事情，但A，J和AJ将被忽略，因为它们默认太短，它将匹配Kelly。）一般全文是更宽容的（通常比LIKE'％string％'）更快，因为允许部分匹配，然后可以根据相关性进行排名。

With MySQL full text indexing a search on A J Kelly would match AJ Kelly (no to confuse matters but A, J and AJ would be ignored as they are too short by default and it would match on Kelly.) Generally Fulltext is much more forgiving (and usually faster than LIKE '%string%') because allows partial matches which can then be ranked on relevance.

您也可以使用 SOUNDEX 通过索引单词的语音等价物使搜索更宽容，并通过在搜索项上应用SOUNDEX搜索它们，然后使用这些搜索索引来搜索索引。使用soundex mary ， marie 和 marry ，例如

You can also use SOUNDEX to make searches more forgiving by indexing the phonetic equivalents of words and search them by applying SOUNDEX on your search terms and then using those to search the index. With soundex mary, marie, and marry will all match, for example.

这篇关于MySQL LIKE％string％不太宽容。还有什么我可以用吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！