Why 'a like a' returns false in MySQL-CodePudding

I have a string in MySQL, when I select using like, it returns nothing. After some investigation, I found a strange behavior of MySQL.

select a,a=a,a like a from (
  select '123' as a union all
  select '\\"'
)x

result:

a	a=a	a like a
123	1	1
\"	1	0

http://sqlfiddle.com/#!9/9eecb/313965

I think a like a should always return 1 (expect for a=null)

CodePudding user response：

In the context of a LIKE expression in MySQL, backslash reserves a special meaning which is to escape the character which follows it. This is needed, for example, if we want to include a literal underscore in a LIKE expression.

Appreciate that the following is true:

'"' LIKE '\"'

This is true because the backslash in the LIKE expression is literal and is used to escape the double quote. The following two are identical:

'"' LIKE '\\"'
'"' LIKE '"'

Here the double backslash in what follows LIKE is interpreted as double escaping the double quote.

The correct version of what you were trying to do is:

'\\"' LIKE '\\\\"'

Here the LHS is a literal backslash followed by double quote. The RHS needs four backslashes for a literal backslash.