mysql - Why I need to double-escape (use 4 ) to find a backslash ( ) in pure SQL?

Question

I do not understand this MySQL behaviour : if I want to display a, I can just select "a\b" which work without problem :

mysql> select "a\b";
+-----+
| a |
+-----+
| a |
+-----+
1 row in set (0.05 sec)

But if I wnat to search a string containing a in a table using LIKE, I need to double-escape my "". Why ?

Here is an example.

We prepare a small table.

create table test ( test varchar(255) );
insert into test values ( "a\b" ) , ( "a\b\c" ) , ( "abcd" );

mysql> select * from test;
+-------+
| test  |
+-------+
| a   |
| ac |
| abcd  |
+-------+
3 rows in set (0.05 sec)

We try to get entries beginning by "a" ...

mysql> select * from test where test LIKE "a\b%";
+------+
| test |
+------+
| abcd |
+------+
1 row in set (0.05 sec)

Why \ is just ignored there? Why I need to double-escape basckslash to get my expected result?

mysql> select * from test where test LIKE "a\\b%";
+-------+
| test  |
+-------+
| a   |
| ac |
+-------+
2 rows in set (0.04 sec)

Answer 1

You escape first for the string syntax, then for LIKE syntax.

In LIKE characters % and _ have special meaning, so if you want to search for literal %, you need to use \%, and if you want to search for literal \% you need to escape the backslash as in \%.

In string syntax " obviously has special meaning, so if you want to include quote in the string you need to escape it as ", and to include literal " in the string you have to escape the backslash as in ".

So in both syntaxes you have to escape .

---

If you don't want to use to escape the LIKE pattern , you can use ESCAPE keyword. For example:

...  where test LIKE "a\b%" ESCAPE '|';

This way, you'll need to write |%, |_ or || to escape these special chars.