How to Escape Literal Percent Signs in MySQL\'s NO_BACKSLASH_ESCAPES Mode?

Escaping Literal Percent Sign in NO_BACKSLASH_ESCAPES MySQL Mode

In MySQL's NO_BACKSLASH_ESCAPES mode, escaping the % character using the usual method (%) is not recognized. This can pose a challenge when performing LIKE queries that involve literal percent signs.

To escape a literal % or _ in this mode, an escape character must be specified after the LIKE keyword. The syntax for doing so is:

SELECT * FROM table_name
WHERE column_name LIKE 'pattern_with_escaped_character' escape 'escape_character';

For example, consider a table with the following values:

• 5% off
• 50% off

To retrieve all rows containing the literal "% off", the following query can be used:

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

Note that the escape character is the backslash (), followed by the literal percent sign.

Alternatively, to create a query that works regardless of NO_BACKSLASH_ESCAPES mode, a different escape character can be used. For instance:

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

In this case, the pipe character (|) is used as the escape character. By specifying an escape character, it is possible to evade the default backslash-based escaping mechanism and achieve the desired results.

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