The Escaping Trap: Why = and LIKE Treat Backslashes Differently in MySQL
When querying a database, you quickly learn that literals like single quotes or numbers are handled intuitively. If you want to find a row where title is exactly 'test\', you write:
SELECT * FROM titles WHERE title = 'test\\';
However, the moment you switch to pattern matching with LIKE, the rules seem to shift dramatically. You often see queries like this:
SELECT * FROM titles WHERE title LIKE 'test\\\\';
It feels counterintuitive that the equality operator (=) requires fewer escape characters than the LIKE operator. The confusion usually stems from overlooking how MySQL processes string literals versus pattern patterns before they ever reach the regex engine.
Two Layers of Escaping at Play
To understand why the syntax differs, you must distinguish between two distinct environments where backslashes exist: the SQL String Literal and the Pattern String.
1. The SQL String Literal (=)
When you write title = 'test\\', MySQL first parses the SQL string literal. In standard C-style escaping rules (which apply to most database clients), a single backslash represents an escape character. Therefore, within the quotes '...', two backslashes \\ are interpreted by the client/parser as a single literal backslash character.
By the time MySQL's internal engine sees the data for comparison, it is looking for a string containing exactly one backslash. If your table actually stores the text test\ (one backslash), the query works perfectly without extra effort from you. The "escaping" happens at the SQL syntax level to define what character you intend to store or match.
2. The Pattern String (LIKE)
The LIKE operator introduces a second layer of complexity because it does not just compare raw strings; it interprets special characters within the pattern itself. In MySQL's LIKE clause:
%matches any sequence of characters._matches any single character.\is treated as an escape character unlessSQL_MODEforbids it.
This means that if you want to match a literal backslash inside a LIKE pattern, MySQL needs to know that the backslash isn't acting as an escape for another special character (like %). Consequently, you must double-escape:
- The first pair of backslashes in your SQL code (
\\) becomes one literal backslash after the SQL parser processes the string. - That resulting single backslash is then interpreted by the
LIKEengine as an escape character.
To tell the LIKE engine "I mean this specific backslash, not an escape sequence," you must provide another layer of escaping in your input: \\\\. After SQL parsing reduces \\\\ to \\, the LIKE engine interprets that as a literal backslash match.
Practical Implementation Examples
Consider a table where the actual stored value is test\.
Scenario A: Exact Match (=)
SELECT * FROM titles WHERE title = 'test\\';
Result: Matches successfully. The SQL parser converts 'test\\' to the string test\, which matches the row.
Scenario B: Pattern Match (LIKE)
SELECT * FROM titles WHERE title LIKE 'test\\\\';
Result: Matches successfully. The SQL parser converts 'test\\\\' to test\\. The LIKE engine then sees the first \ and treats the second \ as the literal backslash you want to match.
If you tried using only two backslashes in the LIKE query ('test\\'), the LIKE engine would interpret it as "match 't', then escape the next character, which is 'e'". It would effectively be searching for a string starting with te, ignoring the rest of the pattern's intent to match a backslash.
Configuration and Edge Cases
This behavior relies on MySQL's default SQL_MODE. If you enable strict escaping modes like NO_BACKSLASH_ESCAPES via your server configuration or session variables, the logic shifts entirely. In that mode, backslashes are never treated as escape characters within patterns, meaning a single backslash in the SQL string literal would suffice for LIKE as well.
However, relying on default behavior is standard practice. When building dynamic queries in frameworks like Laravel, you must be cautious about how raw values are interpolated into these strings. Passing a user-generated string containing a backslash directly into a LIKE query without proper escaping can lead to silent failures where the search returns no results despite data existing. Always verify whether your application layer (like the Eloquent ORM or raw query builder) handles the double-escaping for you, or if you are responsible for managing the \\\\ manually in raw SQL strings.