How to Escape Literal Percent and Underscore Characters in MySQL\'s NO_BACKSLASH_ESCAPES Mode?

Escaping Literal Percent and Underscore Characters in MySQL's NO_BACKSLASH_ESCAPES Mode

In MySQL, the NO_BACKSLASH_ESCAPES mode disables the traditional backslash character () as an escape mechanism within LIKE queries. This mode introduces challenges when attempting to escape literal percent (%) and underscore (_) characters.

Standard Escape Method

The standard method to escape the percent character in a LIKE query is to precede it with a backslash (). However, in NO_BACKSLASH_ESCAPES mode, this method will not work.

Escape Character Alternative

To escape literal characters without using the backslash, you can specify an alternative escape character using the ESCAPE keyword. For example:

SELECT * FROM mytable
WHERE mycol LIKE '5\% off' ESCAPE '\';

Version-Independent Escape

To create a query that works regardless of the NO_BACKSLASH_ESCAPES mode setting, you can use a different escape character. For instance, the pipe character (|) can be defined as the escape character:

SELECT * FROM mytable
WHERE mycol LIKE '5|% off' ESCAPE '|';

By specifying an alternative escape character, you can effectively escape literal % and _ characters even in NO_BACKSLASH_ESCAPES mode. This allows for the creation of LIKE queries that work reliably in different MySQL configurations.

The above is the detailed content of How to Escape Literal Percent and Underscore Characters in MySQL\'s NO_BACKSLASH_ESCAPES Mode?. For more information, please follow other related articles on the PHP Chinese website!