Why Does MySQL Require Backslash Escaping for LIKE but Not for =?

Escaping Backslash in MySQL: Unraveling the Differences between Equal (=) and LIKE Operators

In MySQL, the backslash () character serves a crucial role in string manipulation. When using the equal (=) operator, escaping the backslash is unnecessary, unlike the LIKE operator, where an additional backslash is required.

Consider the following query:

(SELECT * FROM `titles` WHERE title = 'test\')
UNION ALL
(SELECT * FROM `titles` WHERE title LIKE 'test\\')

The output of this query reveals that both conditions match and return the same row. This may seem counterintuitive since the second condition contains an extra backslash.

Why is Escaping Not Required for (=) but Is Required for LIKE?

The answer lies in the unique behavior of the LIKE operator. Unlike the equal (=) operator, LIKE uses backslashes as escape characters. This means that any backslash in a LIKE pattern must be doubled to be matched literally.

MySQL performs this doubling in two stages:

• Once by the parser, which strips one backslash.
• Again when the pattern match is made, leaving a single backslash to be matched against the string.

Therefore, in the first condition (title = 'test'), MySQL automatically escapes the backslash, resulting in a match. However, in the second condition (title LIKE 'test\'), the additional backslash is necessary because it compensates for the two rounds of backslash stripping.

Custom Escape Character for LIKE Patterns

In certain scenarios, it may be desirable to use a different escape character for LIKE patterns. This can be achieved using the ESCAPE keyword, as shown below:

SELECT * FROM `titles` WHERE title LIKE 'test\' ESCAPE '|'

By specifying the ESCAPE keyword, the '|' character becomes the escape character instead of the default ''. This allows you to use the backslash character literally in LIKE patterns without having to double it.

The above is the detailed content of Why Does MySQL Require Backslash Escaping for LIKE but Not for =?. For more information, please follow other related articles on the PHP Chinese website!