使用LIKE和撇号查询问题

So I have an interesting issue that I have never come across and can't seem to find much information about correcting the issue. I have massive database that has an enormous amount of data in it (10 years worth), and attempting to search through it.

所以我有一个有趣的问题，我从来没有遇到过，似乎也找不到很多关于纠正这个问题的信息。我有一个庞大的数据库，里面有大量的数据(10年的数据)，并试图搜索它。

Now the search stuff works fine, but recently someone brought it to my attention about a 'bug' if you will. I've tried to trouble shoot it to get the expected results, but to no avail.

现在搜索功能还不错，但最近有人提醒我注意一个“bug”。我试着不去拍摄它以得到预期的结果，但毫无效果。

This is my issue:

这是我的问题:

When someone uses an apostrophe in the search, it's not that the search faults out, But it returns no results. So for example when searching Pete's the query executes but returns nothing. Yes, I make sure the query has the mysql_real_escape_string() so that Pete's becomes Pete\'s.

当有人在搜索中使用撇号时，并不是搜索错误，而是返回没有结果。例如，当搜索Pete的查询时，它会执行，但不会返回任何东西。是的，我确保这个查询有mysql_real_escape_string()，这样Pete就变成Pete了。

Even when I try to query it using phpmysql's search feature, I get strange results. The query is intended to be like this:

即使我尝试使用phpmysql的搜索功能来查询它，也会得到奇怪的结果。查询的目的是:

SELECT * FROM  `smd_article` WHERE  `copy` LIKE  '%Pete\'s%'

But when I use the search feature in phpmyadmin, it gives me:

但是当我使用phpadmin myadmin中的搜索功能时，它会告诉我:

SELECT * FROM  `smd_article` WHERE  `copy` LIKE  '%Pete''s%'

And when I actually type out the query in the sql tab, it still returns no results. But there are some 17K records that get returned when I just use Pete and some 3k records when I do just Petes.

当我在sql选项卡中输入查询时，它仍然没有返回任何结果。但是有一些17K的记录在我使用Pete时被返回，当我使用Petes时被返回3k的记录。

So I am curious as to what I am doing wrong, or missing to make it so that it can allow for the apostrophe in a query using the LIKE statement. Thoughts?

所以我很好奇我做错了什么，或者我没有做，这样它就可以使用LIKE语句在查询中使用撇号。想法吗?

2 个解决方案

#1

try by mysql_real_escape_string

试着通过mysql_real_escape_string

LIKE '%" . mysql_real_escape_string($find) . "%'

或

 LIKE  '%Pete%27s%'

or if you are using pdo you can do like

或者如果你在使用pdo，你也可以这样做

$search ='Pete\'s'
$stmt = $db->prepare("SELECT * FROM  `smd_article` WHERE  `copy` LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();

#2

The problem is not because of the ' single quote. You escaped it correctly. (as the search function in phpmyadmin):

问题不在于“单引号”。你逃脱了它正确。(作为phpmyadmin中的搜索功能):

String: Pete's

字符串:皮特的

MySQL: 'Pete''s'

MySQL:“皮特“s”

So the following query will work. (I've tested it)

因此，下面的查询将有效。(我测试过)

SELECT *
FROM `smd_article`
WHERE copy
LIKE '%Pete''s%'

You can also have a look at the IN statement:

你也可以看看IN语句:

SELECT *
FROM `smd_article`
WHERE copy
IN (
 'Pete', 'Pete''s'
)

#1