mysql - 如何使用特殊字符 /（正斜杠）和 \（反斜杠）处理查询搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16490325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql - How to handle query search with special characters /(forward slash) and \(backslash)
提问by JAVAC
I am having table where a column allows special characters like '/' forward slash and '\' back slash.
我有一个表，其中一列允许特殊字符，如 '/' 正斜杠和 '\' 反斜杠。
Now when I am trying to search such records from table, I am unable to get those.
现在，当我尝试从表中搜索此类记录时，我无法获得这些记录。
example :abc\def or abc/def I am generating search query which is like
示例：abc\def 或 abc/def 我正在生成类似于
select * from table1_1 where column10 like '%abc\def%'
It is returning 0 rows but actaully there is 1 record existing. how to write a query in this case, let me know.
它返回 0 行，但实际上存在 1 条记录。在这种情况下如何编写查询，请告诉我。
Thanks.
谢谢。
回答by xchiltonx
Barmar is partially correct (so +1),
Barmar 部分正确（所以 +1），
So the trick is to double escape ONLY the backslash, for string escapes only a single escape is needed.
所以诀窍是只对反斜杠进行双重转义，对于字符串转义，只需要一次转义。
For example
例如
- The single quote
'only needs escaping onceLIKE '%\'%' - But to query backslash
\you need to double escape toLIKE '%\\\\%' - If you wanted to query backslash+singlequote
\'thenLIKE '%\\\\\'%'(with 5 backslashes)
- 单引号
'只需要转义一次LIKE '%\'%' - 但是要查询反斜杠，
\您需要双重转义LIKE '%\\\\%' - 如果你想查询反斜杠+单引号
\'那么LIKE '%\\\\\'%'（有5个反斜杠）
Explanation Sourceexcerpt:
解释来源摘录：
Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\”that you use in LIKE strings. For example, to search for “\n”, specify it as “\n”. To search for “\”, specify it as “\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
由于 MySQL 在字符串中使用 C 转义语法（例如，“\n”表示换行符），因此您必须将 LIKE 字符串中使用的任何“\”加倍。例如，要搜索“\n”，请将其指定为“\n”。要搜索“\”，请将其指定为“\\”；这是因为反斜杠被解析器剥离一次，并在进行模式匹配时再次剥离，留下一个反斜杠进行匹配。
回答by Barmar
回答by Explosion Pills
You have to escape the \with another \
你必须\和另一个人一起逃离\
select * from table1_1 where column10 like '%abc\def%'
回答by Ed Gibbs
Escaping the LIKEvalue didn't work when I tried it on MySQL v5.5. After a few tries, what did work was a regex (and I had to escape there too, plus hide it in a character class). I finally got it to work like this:
LIKE当我在 MySQL v5.5 上尝试时，转义值不起作用。经过几次尝试，什么起作用的是正则表达式（我也不得不在那里转义，并将其隐藏在字符类中）。我终于让它像这样工作：
select * from table1_1 where column10 rlike 'abc[\]def'
These didn't work:
这些没有用：
... column10 like '%abc\def%'
... column10 like concat('%abc', char(92), 'def%')
... column10 rlike 'abc\def'
Note you also have this tagged as PL/SQL, which is Oracle. The query you posted works as-is on Oracle. Is the PL/SQL tag a mistake?
请注意，您还将此标记为 PL/SQL，即 Oracle。您发布的查询在 Oracle 上按原样运行。PL/SQL 标签是错误的吗？
回答by Tamas
Use escaping.
使用转义。
https://dev.mysql.com/doc/refman/5.0/en/string-literals.html
https://dev.mysql.com/doc/refman/5.0/en/string-literals.html
Table 9.1. Special Character Escape Sequences
表 9.1。特殊字符转义序列
Escape Sequence Character Represented by Sequence \0 An ASCII NUL (0x00) character.
由序列表示的转义序列字符 \0 一个 ASCII NUL (0x00) 字符。
\' A single quote (“'”) character.
\' 单引号（“'”）字符。
\" A double quote (“"”) character.
\" 双引号 (""") 字符。
\b A backspace character.
\b 退格字符。
\n A newline (linefeed) character.
\n 换行（换行）字符。
\r A carriage return character.
\r 回车符。
\t A tab character.
\t 制表符。
\Z ASCII 26 (Control+Z). See note following the table.
\Z ASCII 26 (Control+Z)。请参阅表格后面的注释。
\ A backslash (“\”) character.
\ 一个反斜杠（“\”）字符。
\% A “%” character. See note following the table.
\％ 一个角色。请参阅表格后面的注释。
_ A “_” character. See note following the table.
_ 一个角色。请参阅表格后面的注释。
回答by Derek Gogol
If you use PHP sprintf()to put together your query where you want:
如果您使用 PHP sprintf()将您的查询放在您想要的位置：
AND column10 LIKE '%/%'
AND column10 LIKE '%/%'
This worked for me inside sprintf():
这在 sprintf() 中对我有用：
AND column10 LIKE '%%/%%'
AND column10 LIKE '%%/%%'
(MySSQL 8 - WAMP)
（MySQL 8 - WAMP）
