I have experienced a really unusual trouble with MySQL, including backslashes. Essentially after i perform a wildcard complement LIKE for n which is incorporated in the database as text instead of a real newline, it'll only return a match basically have only a right hands wildcard:

SELECT * 
FROM  `tmptest` 
WHERE  `a` LIKE  '\\\\n%'

Now, basically query such as this, it won't return anything:

SELECT *  
FROM `tmptest` 
WHERE `a` LIKE '%\\\\n%'

As you can tell in the data I've within the table both queries ought to be matching. I am unsure whether it's something which I am missing, or I am improperly getting away the newline however it does not seem sensible for that first query to operate and also the second not.

Table structure:

CREATE TABLE IF NOT EXISTS `tmptest` (
`a` varchar(22) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample data:

INSERT INTO `tmptest` (`a`) VALUES
('\\n'),
('\\n\\ndfsdfsdfs\\n');

Thank you for spending time to see this.

It really works for me personally with 6 backslashes while using the left side wildcard:

mysql> SELECT * FROM `tmptest` WHERE `a` LIKE '%\\\\\\n%';
+-----------------+
| a               |
+-----------------+
| \n              |
| \n\ndfsdfsdfs\n |
+-----------------+
2 rows in set (0.00 sec)

Using mysqld Ver 5.1.49


@Karolis so far as i realize the expression for that LIKE operator ought to be parsed two times, hence \\\\ becomes \ when combined with LIKE.

But exactly how to describe this (while using expression "backslash"):

SELECT 'back\\slash' LIKE '%back\\\\slash%'; -> TRUE (normal behavior)

SELECT 'back\\slash' LIKE '%back\\\\\slash%'; -> TRUE (5 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\slash%'; -> TRUE (6 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\slash%'; -> TRUE (7 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\\slash%'; -> FALSE (normal behavior, I suppose..)

Nonetheless if searching just for the "":

mysql> SELECT 'back\\slash' LIKE '%\\\\%'; --> FALSE (but should work)

mysql> SELECT 'back\\slash' LIKE '%\\\\\%'; --> TRUE (5 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\%'; --> TRUE (6 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\\%'; --> FALSE (7 backslashes)

With this particular question, you could make use of a different escape character | and bypass the issue altogether (if no | character happens):

mysql> SELECT 'back\\slash' LIKE '%\\%' ESCAPE '|'; --> TRUE

So perhaps some mysql guru available can explain this. I merely can't. also examined with mysql 5.1.53 on the different machine. Same behavior was observed. When i began by leaving comments, its an extremely interesting question.