How to Escape Special Characters in MySQL\'s NO_BACKSLASH_ESCAPES Mode?

Escaping Special Characters in NO_BACKSLASH_ESCAPES Mode

When NO_BACKSLASH_ESCAPES option is enabled in MySQL, the standard way to escape a literal "%" or "_" using "%" doesn't work. This presents a challenge when performing LIKE queries.

Solution 1: Using an Escape Character

In NO_BACKSLASH_ESCAPES mode, you can use an escape character to protect special characters like "%". The escape character is specified in the LIKE query after the escape keyword.

For example:

select * from mytable
where mycol like '5\% off' escape '\';

In this query, "" is used as the escape character, so "%" represents the literal "%" character.

Solution 2: Using a Different Special Character

If you can't use a backslash as an escape character, you can choose a different special character and use it instead. For example:

select * from mytable
where mycol like '5|% off' escape '|';

Here, "|" is used as the escape character, so "5|% off" represents a string with a literal "%" character.

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