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

Escaping Literal Percentage Signs in MySQL NO_BACKSLASH_ESCAPES Mode

In MySQL, when the NO_BACKSLASH_ESCAPES mode is enabled, the standard backslash () character escaping is disabled. This can pose a challenge when attempting to escape literal percent (%) or underscore (_) characters in LIKE queries.

The conventional method of escaping a percent sign, using %, becomes ineffective in NO_BACKSLASH_ESCAPES mode. As exemplified in the provided query:

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

...this query will fail to return the desired result when NO_BACKSLASH_ESCAPES mode is enabled.

Solution with Escape Character

To escape a literal percent sign in NO_BACKSLASH_ESCAPES mode, you must use an escape character. The escape character specifies that the following character should be interpreted literally, not as a special character.

In MySQL, the escape character can be any non-alphanumeric character. For example, let's use the backslash () character as the escape character:

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

In this query, the backslash preceding the percent sign indicates that the percent sign should be treated as a literal character, not a wildcard.

Alternative Solution with Different Escape Character

To ensure compatibility with both NO_BACKSLASH_ESCAPES mode and standard mode, you can use a different character as the escape character. This allows the query to work regardless of the mode setting.

For instance, let's use the pipe (|) character as the escape character:

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

In this case, the pipe character preceding the percent sign serves as the escape character, allowing the query to successfully execute in both standard mode and NO_BACKSLASH_ESCAPES mode.

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