如何在 MySQL 中搜索斜杠 (\)?为什么在 where (=) 不需要转义 (\) 但对于 Like 是必需的? [英] How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?

问题描述

考虑这个查询(演​​示在这里)

(SELECT * FROM `titles` where title = 'test\\')
UNION ALL
(SELECT * FROM `titles` where title LIKE 'test\\\\')

输出:

| ID | TITLE |
--------------
|  1 | test\ |
|  1 | test\ |

问题:

为什么 (=) 不需要额外的 (\) 而对于(比如)额外的 \\ 则需要?很明显，MySQL 使用 (test\\) 转义了 (test\) 然后使用 (test\\\\) 对 LIKE 来说是合乎逻辑的.

QUESTION:

Why no extra (\) required for (=) but for (like) additional \\ is required? Its clear that MySQL escaped the (test\) with (test\\) then using (test\\\\) is logical for LIKE.

表格信息:

CREATE TABLE IF NOT EXISTS `titles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `titles`
--

INSERT INTO `titles` (`id`, `title`) VALUES
(1, 'test\\');

推荐答案

\ 默认用作 LIKE 中的转义字符.

\ functions as an escape character in LIKE by default.

来自 手册 喜欢:

因为 MySQL 在字符串中使用 C 转义语法(例如，\n"表示换行符)，您必须将 LIKE 字符串中使用的任何\"加倍.例如，要搜索\n"，请将其指定为\\n".要搜索\"，请将其指定为\\\\"；这是因为反斜杠被解析器删除一次，并且在进行模式匹配时再次删除，留下一个要匹配的反斜杠.

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.

您可以通过指定另一个转义字符来更改此设置，例如:

You can change this by specifying another escape character, as in:

SELECT * FROM `titles` WHERE title LIKE 'test\\' ESCAPE '|'

这篇关于如何在 MySQL 中搜索斜杠 (\)?为什么在 where (=) 不需要转义 (\) 但对于 Like 是必需的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！