忽略搜索查询中的特殊字符

I've been wracking my brain trying to find a solution for this for a couple of days. I'm trying to make a "smart" query that can handle a wide range of search terms. The queries run fine until there are special characters involved and I've had some success w/ the REPLACE method on some characters such as commas and dashes. Other characters such as quotes and ampersands will result in empty queries.

这几天我一直在绞尽脑汁地寻找解决办法。我正在尝试做一个“智能”查询，可以处理广泛的搜索词。查询运行良好，直到涉及到特殊字符为止，我已经在一些字符(如逗号和破折号)上获得了一些成功。其他字符(如引号和&号)将导致空查询。

Here's a few examples:

这里是几个例子:

the original name I'm searching for is "French Is Fun, Book 1 - 1 Year Option" and with this query below, I get results returned with these search terms:

我搜索的最初名字是“French is Fun, Book 1 - 1 Year Option”，通过下面的查询，我得到了这些搜索词返回的结果:

1.  "French Is Fun"
2.  "French Is Fun, book"
3.  "French Is Fun, book"
4.  "French Is Fun, Book 1"


SELECT * FROM `products` WHERE ( (LOWER(name) LIKE '%french is fun book%' OR
LOWER(replace(name, '  ','')) LIKE '%french is fun book%' OR
LOWER(replace(name, ' ','')) LIKE '%french is fun book%' OR
LOWER(replace(name, '-','')) LIKE '%french is fun book%')

However, when the original title has an ampersand in it like such: "Global History & Geography: The Growth of Civilizations - 1 Year Option" - I get an empty query when I try these different search terms:

然而，当最初的标题中有一个&号(如:“全球历史与地理:文明的增长——一年的选择”)时，当我尝试这些不同的搜索词时，我得到的是一个空的查询:

1.  "Global History & Geography"
2.  "Global History Geography"

I've tried this to no avail

我已经试过了，但没有用

SELECT * FROM `products` WHERE  
(LOWER(name) LIKE '%global history geograph%' OR  
    LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
    LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR 
    LOWER(replace(name, ',','')) LIKE '%global history geography%' OR 
    LOWER(replace(name, '&','')) LIKE '%global history geography%' OR  
    LOWER(replace(name, '-','')) LIKE '%global history geography%');

I also tried adding an escape character to the ampersand and it doesn't help:

我还尝试在&符中添加一个转义字符，但没有帮助:

SELECT * FROM `products` WHERE  
(LOWER(name) LIKE '%global history geography%' OR  
    LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
    LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR 
    LOWER(replace(name, ',','')) LIKE '%global history geography%' OR 
    LOWER(replace(name, '\&','')) LIKE '%global history geography%' OR  
    LOWER(replace(name, '-','')) LIKE '%global history geography%');

And commas in the name also return empty results. As a demonstration, the original name is this:

名称中的逗号也返回空结果。作为示范，最初的名字是:

"Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option"

"Amsco's AP Calculus AB/BC准备进阶考试- 1 Year Option"

This attempt always returns empty queries:

这个尝试总是返回空查询:

SELECT * FROM `products` WHERE 
( (LOWER(name) LIKE '%amscos ap calculus%' OR
     LOWER(replace(name, ' ','')) LIKE '%amscos ap calculus%' OR
     LOWER(replace(name, '\'','')) LIKE '%amscos ap calculus%' OR
     LOWER(replace(name, ',','')) LIKE '%amscos ap calculus%' OR
     LOWER(replace(name, '-','')) LIKE '%amscos ap calculus%')
    ) AND ( (`products`.`type` = 'Rental' ) );

Any ideas?

什么好主意吗?

1 个解决方案

#1

The way you're going you're going to have your DB server doing the same work over and over until it dies of resource exhaustion.

你的方法是让你的数据库服务器重复做同样的工作直到它耗尽资源耗尽。

Short Version:

短版:

Add another column to the table that contains the title already stripped of special chars and lower-cased. Select based on that.

将另一列添加到表中，该列包含已被删除的特殊字符和小写字母。选择基于这个。

Medium Version:

媒介版本:

Use Full-Text Indexing/Searching.

使用全文索引/搜索。

Long Version

长版本

Create a separate table structure that tracks individual words and their associations to various titles and create queries/program logic to search with it.

创建一个单独的表结构，跟踪单个单词及其与各种标题的关联，并创建查询/程序逻辑来搜索。

#1