MYSQL WHERE LIKE 语句

Question

我正尝试在 mysql 语句中使用 LIKE 子句，如下所示:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956%'

“文件路径”列中的数据匹配数据是:

C:\SCAN\Parker_Apple_Ben_20-10-1830\TEST

上面的 SQL 语句工作正常并选择了相关行，但是如果我将“\”字符添加到 LIKE 子句的末尾(如下所示)它不会正确选择行:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\%'

有趣的是，如果我在 LIKE 子句的开头放置一个“\”字符(如下所示)，它会完美地选择该行 - 这让我感到困惑。

SELECT * FROM batches WHERE FilePath LIKE '%\Parker_Apple_Ben_20-10-1956%'

Answer 1

在 LIKE 中，_ 是匹配单个字符的通配符，因此需要转义以正确匹配文字“_”而不是可能匹配任何内容。此外，您还提到尝试将以 1830 结尾的字符串与以 1956 结尾的字符串匹配。最后，正如 J W 和 MySQL 文档所述，您需要两次转义反斜杠

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.

尝试

SELECT * FROM batches 
WHERE FilePath LIKE '%Parker\_Apple\_Ben\_20-10-1830\\\\%'

http://sqlfiddle.com/#!2/3ce74/3