Don't understand the search behavior of MySQL backslash-CodePudding

I am trying to search for backslashes and percentages in a select statement. I understand that I need six backslashes to search the following records

id	name
1	\

select * from test_table where name like '%\\\\\\%'

However, to search for the following records requires six backslashes and four backslashes, not six and six.

id	name
2	\a\

select * from test_table where name like '%\\\\\\a\\\\%';
-- select * from test_table where name like '%\\\\\\a\\\\\\%'; ←unsearchable

What is the reason that the number of backslashes to escape depends on their position?

MySQL: 5.7.34
table/column collation: utf8_unicode_ci

CodePudding user response：

First of all, your first example does not look correct (more on that later). You don't get any match with 6 slashes. You need as few as 3 slashes (more on that later too), as illustrated here.

with test_table (name) as (
    select '\\'
)
select name,
    name like '%\\\\\\%' as 6_slashes,
    name like '%\\\%' as 3_slashes
from test_table;

name	6_slashes	3_slashes
\	0	1

The backslash character is used as escape character in several contexts, including:

That means that you need to escape backslash itself just to produce a raw backslash, and all this adds up if you nest contexts.

2 symbols in a string literal represent 1 actual character
```
 select '\\'; -- \
```
(Demo)
2 characters in a LIKE pattern represent a literal backslash. However, if you're using a string literal to generate them, you need to escape every of them. You wouldn't need that double-escaping if you were using a different source; for instance, using the client language of your choice, you could read the string from a text file and feed it into SQL using prepared statements.
In many cases, it isn't mandatory to escape raw backslashes if there's no ambiguity.
```
SELECT '\' -- Parse error because \' is a valid entity
SELECT '\%' -- OK because because \% is not a valid entity
SELECT '\\%' -- OK and identical to previous one
```
Demo

This covers the second "more on that later" and explains why you sometimes get the same result with a variable number of slashes.
If you use another language to generate your SQL, that language might also use backslash as escape character (in particular, PHP and JavaScript do), so there's another layer of escaping involved. This is the first "more on that later".

CodePudding user response：

In order to use a literal backslash inside LIKE clause you need four:

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.

select *
     , name like '%\\\\%' as like_test_2
     , name like '%\\\\a\\\\%' as like_test_2
from t

Result:

id	name	like_test_2	like_test_2
1	\	1	0
2	\a\	1	1

To make searching for \ easier you could simply disable escaping (you won't be able to match literal % or _ then):

select *
     , name like '%\\%' escape '' as like_test_2
     , name like '%\\a\\%' escape '' as like_test_2
from t

Result:

id	name	like_test_2	like_test_2
1	\	1	0
2	\a\	1	1