使用LIKE和撇号的MySQL查询问题 [英] Mysql Query issues using LIKE and apostrophe

问题描述

所以我有一个有趣的问题，我从未遇到过，并且似乎找不到更多有关纠正问题的信息.我有一个庞大的数据库，其中包含大量数据(价值10年)，并试图对其进行搜索.

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.

现在搜索的东西工作正常，但是最近有人提请我注意一个有关错误"的信息.我尝试麻烦射击以获得预期的结果，但无济于事.

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.

这是我的问题:

当某人在搜索中使用撇号时，并不是搜索失败，而是没有返回结果.因此，例如，当搜索 Pete's 时，查询将执行，但不返回任何内容.是的，我确保查询具有mysql_real_escape_string()，以便 Pete's 变为 Pete \'s .

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.

即使当我尝试使用phpmysql的搜索功能对其进行查询时，我也得到了奇怪的结果.该查询旨在像这样:

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

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

但是当我使用phpmyadmin中的搜索功能时，它会给我:

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

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

当我实际上在sql选项卡中键入查询时，它仍然不返回任何结果.但是，当我仅使用Pete时，就会返回一些17K记录，而当我仅使用Petes时，会返回一些3k记录.

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.

所以我很好奇我在做什么错，或者错过了做错什么，以便可以使用LIKE语句在查询中使用撇号.有想法吗?

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?

推荐答案

通过 mysql_real_escape_string

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

或

 LIKE  '%Pete%27s%'

或者如果您使用的是pdo，您可以像

or if you are using pdo you can do like

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

这篇关于使用LIKE和撇号的MySQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！