How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?

Consider this QUERY 

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

Output:

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

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.

Table information:

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\\');

 Answers

\ functions as an escape character in LIKE by default.

From the manual for 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.

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

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

---

LIKE accepts two wildchar characters, % and _.

To be able to match these characters, escaping can be used: \%, \_. This also means that if you want to match \, it has to be escaped as well.

All this is documented in the manual.