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

Escaping Literal Percent and Underscore Characters in MySQL with NO_BACKSLASH_ESCAPES Mode

When MySQL is running in NO_BACKSLASH_ESCAPES mode, the standard method of escaping literal percent (%) and underscore (_) characters using the backslash () character is not applicable. This article provides alternative ways to escape these characters specifically in this mode.

Issue

Consider the following example: A column in a MySQL table contains values such as "5% off" and "50% off." The following LIKE query, which uses the standard backslash escape character, will not work in NO_BACKSLASH_ESCAPES mode:

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

Solution

In NO_BACKSLASH_ESCAPES mode, you can escape percent and underscore characters using a different escape sequence. One option is to use the backslash character preceded by the escape keyword. For example:

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

Alternative Solution

Alternatively, you can use a different character for escaping, such as the pipe (|) character. The query below will work regardless of the NO_BACKSLASH_ESCAPES mode setting:

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

By using these techniques, you can effectively escape literal percent and underscore characters in MySQL queries even when in NO_BACKSLASH_ESCAPES mode.

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