Odd behavior when doing LIKE using wildcards that look for backslashes in MySQL

Question

I ran into a very unusual problem with MySQL, including a backslash. Basically, when I do a wildcard with LIKE for \ n, which is in the database as text, and not the actual newline, it only returns a match if I only have a right-handed wildcard:

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

Now, if I ask like this, it will return nothing:

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

As you can see from the data that I have in the table, both queries should match. I’m not sure if this is what I am missing, or I’m incorrectly avoiding a new line, but it doesn’t make sense for the first request to work, and the second one 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'); 

Thanks for taking the time to read this.

Answer 1

It works for me with 6 backslashes when using the left 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, as I understand it, the expression for the LIKE operator must be parsed twice, so \\\\ goes to \ when used with LIKE.

But how to explain it (using the expression "back \ slash"):

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 think ..)

However, if you are only looking for "\":

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)

In this particular question, you can use another escape character | and generally bypass the problem (if the | character is not found):

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

So maybe some mysql guru there can explain it. I just can not. also tested with mysql 5.1.53 on another machine. The same behavior was observed. As I started with the comments, this is a pretty interesting question.

Answer 2

Based on the QSP answer (as I thank him for saving my day).

I would say using 5 black eye. I don’t understand why, like QSP and Karolis, but if you replace 1 slash with 5 slaches in mySQL (or 10 in php), it still works like QSP.

BUT the good thing is, if you replace '\\' with 12 backslashes (or 24 in php), as suggested by QSP, this will not work. But if you replace it with 10 backslashes (or 20 in php), it will still work.

I hope this can help someone as the QSP response is already accepted.

EDIT:
It works fine if you do this:
% \\\\\%
% \\\\\\\\\\%
% K \\\\\\\\\\% s
% \\\\\\\\\\% s
% k \\\\\ s% (if only 1 slash, like back \ slash)
But this will not work if you do:
% K \\\\\\\\\\%
% K \\\\\%
% \\\\\% s
I will continue to watch why.