Why Do I Need Double Backslashes When Searching for a Slash Using MySQL\'s LIKE Operator?

Understanding MySQL Escape Sequences for Search Queries

In MySQL, when searching for a slash character () using the WHERE clause, there is a difference in the need for escape sequences depending on the operator used.

WHERE clause with Equals (=) Operator

For the equals (=) operator, no additional escape sequence is required. The following query will correctly find a record with a title containing a slash character:

<code class="sql">SELECT * FROM `titles` WHERE title = 'test\'</code>

This is because the backslash is escaped internally by MySQL before executing the comparison.

WHERE clause with LIKE Operator

However, when using the LIKE operator, an additional escape sequence is required for the backslash. This is because the LIKE operator follows C escape syntax, where two backslashes are used to escape another backslash. Therefore, to search for a slash character using LIKE, you need to use the following syntax:

<code class="sql">SELECT * FROM `titles` WHERE title LIKE 'test\\'</code>

Default Escape Character

By default, the backslash () is used as the escape character in LIKE expressions. This can be changed by specifying a different escape character using the ESCAPE clause. For example, the following query will use the pipe character (|) as the escape character:

<code class="sql">SELECT * FROM `titles` WHERE title LIKE 'test\' ESCAPE '|'</code>

The above is the detailed content of Why Do I Need Double Backslashes When Searching for a Slash Using MySQL\'s LIKE Operator?. For more information, please follow other related articles on the PHP Chinese website!