MYSQL WHERE LIKE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15810404/
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 WHERE LIKE Statement
提问by Mike
I am attempting to use the LIKE clause in a mysql statement as follows:
我试图在 mysql 语句中使用 LIKE 子句，如下所示：
SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956%'
The data matched data within the 'FilePath' Column is:
'FilePath' 列中的数据匹配数据为：
C:\SCAN\Parker_Apple_Ben_20-10-1830\TEST
The above SQL statement works fine and picks up the relevent row, but if I for example add the "\" character into the end of the LIKE clause (like follows) it does not pick up the row correctly:
上面的 SQL 语句工作正常并提取相关行，但是如果我例如将“\”字符添加到 LIKE 子句的末尾（如下所示），它不会正确提取该行：
SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\%'
Funnily enough though if I place a '\' character at the beginning of LIKE clause (like follows) it picks up the row perfectly fine - this has me baffled.
有趣的是，如果我在 LIKE 子句的开头放置一个 '\' 字符（如下所示），它会很好地选择该行 - 这让我感到困惑。
SELECT * FROM batches WHERE FilePath LIKE '%\Parker_Apple_Ben_20-10-1956%'
回答by Simon at My School Portal
Within LIKE, _is a wildcard matching a single character and so that needs escaping to correctly match a literal '_' instead of potentially matching anything. Additionally you are mentioning trying to match a string ending in 1830 with a like ending 1956. Finally as mentioned by J W and the MySQL documentation you need to escape backslashes twice
在 LIKE 中，_是匹配单个字符的通配符，因此需要转义以正确匹配文字“_”而不是可能匹配任何内容。此外，您还提到尝试将以 1830 年结尾的字符串与以 1956 年结尾的字符串匹配。最后，如 JW 和 MySQL 文档所述，您需要两次转义反斜杠
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
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”。要搜索“\”，请将其指定为“\\”；这是因为反斜杠被解析器剥离一次，并在进行模式匹配时再次剥离，留下一个反斜杠进行匹配。
Try
尝试
SELECT * FROM batches
WHERE FilePath LIKE '%Parker\_Apple\_Ben\_20-10-1830\\%'
回答by John Woo
adding \in the current query escapes %, try this
添加\当前查询转义%，试试这个
SELECT * FROM batches WHERE FilePath LIKE '%Parker\_Apple\_Ben\_20-10-1956\\%'
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.
要搜索“\”，请将其指定为“\\\\”；这是因为反斜杠被解析器剥离一次，并在进行模式匹配时再次剥离，留下一个反斜杠进行匹配。
回答by BobTheBuilder
Use quadruple escape:
使用四重转义：
SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\\%'
回答by Jordan Robinson
This is because \%is an escape character in MySQL, for more information, see the following:
这是因为\%是 MySQL 中的转义字符，有关详细信息，请参阅以下内容：
回答by Mohan Raj B
You should escape special characters like \, ', _ etc in mysql like \\, \', \_
您应该转义像\\, \', mysql 中的 \, ', _ 等特殊字符\_
回答by Muc
You should escape your '\' character. Try this:
你应该转义你的 '\' 字符。尝试这个：
SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\%'
