Mysql 查询 LIKE 搜索和使用带有符号的字符串 [英] Mysql query LIKE search and using strings with symbols

问题描述

我的问题是在搜索查询中使用了符号.我希望用户能够毫无问题地使用符号，但 mysql 中的 LIKE 函数似乎不是解决方案，所以我需要一些帮助.

My problem is having symbols used in the search query. I want users to be able to use symbols without being a problem, but the LIKE function in mysql seems to not be the solution so I need some help.

示例:如果有人搜索Blue's car"并且Blues car"在数据库中，则此查询将返回 0 个结果.OR viseversa，如果有人搜索Blues car"并且Blue's car"在数据库中，则此查询也将返回 0 个结果.

EXAMPLE: If someone searches for "Blue's car" and "Blues car" is in the database, this query will return with 0 results. OR viseversa, if someone searches for "Blues car" and "Blue's car" is in the database, this query will return with 0 results as well.

这是我目前使用的示例:

This is an example of what I'm currently using:

("SELECT Title FROM MyData WHERE Title LIKE '%".$search."%'")

还有其他方法可以提供更好的搜索结果吗?

Is there another way on providing better search results?

谢谢.

推荐答案

搜索字段

您可以有一个额外的搜索"字段，用于存储没有标点符号的标题，当用户输入搜索字符串时，在应用查询之前去掉标点符号.您还可以删除前导The".

You could have an additional "search" field, that stores the title without punctuation and when user enters search string, strip out punctuation before applying query. You could also remove leading "The".

我将进一步解释上述内容.

I'll further explain the above.

在数据库中，您有这样的记录.SearchTitle 是删除了前导The"和标点符号的标题:

In the database you have a record like this. The SearchTitle is the Title with the leading "The" and punctuation removed:

Title                  SearchTitle
------------------------------------------
The Blue's Clues       Blues Clues

在这里，用户知道确切的标题并输入以下搜索字符串:

Here, the user knows the exact title and enters the following search string:

The Blue's Clues

去掉开头的The"和标点符号，得到以下内容:

You strip out the leading "The" and the punctuation, yielding the following:

Blues Clues

您的查询看起来像这样

SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

在这里，用户不知道确切的标题并输入以下内容:

Here, the user doesn't know the exact title and enters the following:

Blues Clues

去掉开头的The"和标点符号，仍然产生同样的结果:

Stripping the leading "The" and the punctuation, still yields the same thing:

Blues Clues

您的查询保持不变，并与搜索字段中的内容相匹配:

Your query stays the same, and matches what's in the search field:

SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

这可以进一步改进.关键是对搜索字符串应用与搜索字段中存储的内容相同的规则.例如，将two"和II"转换为2"等，删除ON"和AND"等附加词.

This can be further improved. The key is to apply the same rules to the search string as to what's stored in the search field. For example, convert "two" and "II", to "2", etc., remove additional words like "ON" and "AND", etc.

全文搜索

MySQL 全文搜索使用自然语言"搜索.我不确定这是否能解决您的问题，但可能值得研究.

MySQL FullText searches use "Natural Language" searches. I'm not sure if this would do the trick in your case, but it might be worth researching.

这篇关于Mysql 查询 LIKE 搜索和使用带有符号的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！