如何在MySql中搜索O'%这样的短语?

I have a MySql database that contains people's names. Of course there are names stored that begin with O'.

我有一个MySql数据库，它包含了人们的名字。当然，有些名称是以O开头的。

If I try to retrieve the name "O'Brien" with sql like this:-

如果我尝试用sql来检索“O'Brien”这个名字:-

SELECT person_sname
FROM people
WHERE person_sname = 'O\\\'Brien'

Then I get the expected result. However, if I try to retrieve all names beginning with "O'" like this:-

然后我得到了预期的结果。但是，如果我试图检索以“O”开头的所有名称:-

SELECT person_sname
FROM people
WHERE person_sname like 'O\\\'Brien'

Then I get an empty result set. My goal is to search for like "O\\\'".

然后我得到一个空的结果集。我的目标是搜索“O\\\ \\\”。

Is there any explanation of why this is happening? I have searched the MySql manual and can find no explanation of why my search shouldn't work as expected.

这是为什么呢?我已经搜索了MySql手册，没有找到为什么我的搜索不应该按预期工作的解释。

Other questions I have found deal with storing the escaped string or with retrieving it using = which works faultlessly, I specifically want to use the like clause on this occassion.

我发现的其他关于存储转义字符串或使用=检索它的问题都是正确的，我特别想在这种情况下使用like子句。

To clarify: I have tried the following also:-

澄清:我也试过下面的方法:-。

SELECT person_sname
FROM people
WHERE person_sname like 'O\'Brien' escape "\'"

SELECT person_sname
FROM people
WHERE person_sname like 'O\'Brien'

SELECT person_sname
FROM people
WHERE person_sname LIKE 'O\'Brien'
ESCAPE '~'

The string is escaped on insertion to the db usimg mysql_real_escape_string() and is stored as O\'Brien (for example).

该字符串在插入到db usimg mysql_real_escape_string()时被转义，存储为O\ Brien(例如)。

4 个解决方案

#1

\ is the default ESCAPE character for LIKE so you need to either escape it again or define a different ESCAPE character.

\是LIKE的默认转义字符，所以您需要重新转义它，或者定义一个不同的转义字符。

I wouldn't store names with escape characters like that anyway.

不管怎样，我不会用转义字符来存储名称。

SELECT 
        CASE WHEN name LIKE 'O\\\'Brien' THEN 1 ELSE 0 END,             /*0*/
        CASE WHEN name LIKE 'O\\\\\'Brien' THEN 1 ELSE 0 END,           /*1*/        
        CASE WHEN name LIKE 'O\\\'Brien' ESCAPE '~' THEN 1 ELSE 0 END,  /*1*/
        CASE WHEN name = 'O\\\'Brien' THEN 1 ELSE 0 END                 /*1*/
FROM
(SELECT 'O\\\'Brien' as name) T

#2

You were injecting a literal backslash. This is enough:

您正在注入一个文本反斜杠。这就足够了:

SELECT person_sname
FROM people
WHERE person_sname = 'O\'Brien'

To test:

测试:

SELECT 'O\\\'Brien', 'O\'Brien'

Update: There's already an accepted answer so I'll just add a couple of clarifications.

更新:已经有了一个公认的答案，所以我将添加一些澄清。

First, I had overlooked the fact that stored data was corrupted. Needless to say, that explains why person_sname = 'O\\\'Brien' is true.

首先，我忽略了存储数据被损坏的事实。不用说，这就解释了为什么person_sname = 'O\\\ \'Brien' Brien'是对的。

Second, the \ character has two usages in MySQL syntax:

其次，\字符在MySQL语法中有两个用法:

It's the character used to compose escape sequences: \n, \t...
它是用来组成转义序列的字符:\n， \t…
It's the default escape char to insert literal % and _ characters in LIKE expressions: foo LIKE '%abc\%def%'
在LIKE表达式中插入文本%和_characters是默认的转义字符:foo LIKE '%abc\%def%'

The problem is that inserting a \ symbol in a LIKE expression triggers both behaviours so you actually need to insert four backslashes to get one:

问题是在LIKE表达式中插入一个\符号会触发这两种行为，所以实际上你需要插入四个反斜杠才能得到一个:

person_sname like 'O\\\\\'Brien'

... unless you change the second meaning with the ESCAPE clause:

…除非你用免责条款改变第二个意思:

person_sname like 'O\\\'Brien' escape '|'

Of course, if data was not corrupted you could simply:

当然，如果数据没有被破坏，你可以简单地:

person_sname = 'O\'Brien'
person_sname like 'O\'Brien'

#3

This is pretty standard in most SQL dialects:

在大多数SQL方言中，这是相当标准的:

SELECT person_sname
FROM people
WHERE person_sname like 'O''Brien' -- same as = 'O''Brien'

SELECT person_sname
FROM people
WHERE person_sname like 'O''Brien%' -- O'Brien plus more stuff

SELECT person_sname
FROM people
WHERE person_sname like 'O''%' -- O'plus more stuff

Note that you cannot use " for strings if ANSI_QUOTES are on, since "" are for identifiers.

注意，如果ANSI_QUOTES打开，则不能使用“for string”，因为“”是用于标识符。

#4

like "O'%" should be sufficient. You don't need any backslashes here.

比如“O'%”就足够了。你不需要任何的反斜线。

#1