Handling Backslashes in MySQL INSERT and SELECT Statements

MySQL treats many special symbols—such as single quotes, double quotes, and backslashes—as escape characters, which can cause unexpected results when storing file‑path strings. The article begins with a brief introduction to this issue.

1. INSERT statements containing backslashes

Test cases

INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\陈哈哈\加班');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\陈哈哈\\加班');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\\陈哈哈\\\加班');

Resulting rows:

+----+-----------------------+
| id | text                  |
+----+-----------------------+
| 1  | D:陈哈哈加班          |
| 2  | D:\陈哈哈\加班        |
| 3  | D:\陈哈哈\加班        |
| 4  | D:\\陈哈哈\\加班      |
| 5  | D:\\陈哈哈\\加班      |
+----+-----------------------+
5 rows in set (0.00 sec)

The observations are:

1 backslash in the literal becomes 0 stored backslashes.

2 backslashes become 1 stored backslash.

3 backslashes become 1 stored backslash.

4 backslashes become 2 stored backslashes.

5 backslashes become 2 stored backslashes.

The reason is that MySQL parses the string once, treating a backslash as an escape character; therefore every pair of backslashes resolves to a single stored backslash.

2. SELECT queries with backslashes (LIKE)

Using the same table, the article tests pattern matching:

SELECT * FROM demo0526 WHERE text LIKE '%\%';   -- returns empty set
SELECT * FROM demo0526 WHERE text LIKE '%\\%'; -- returns empty set

Because the LIKE pattern itself is parsed for escapes, a single backslash in the pattern matches nothing. To match a literal backslash, the pattern must contain double‑escaped backslashes:

SELECT * FROM demo0526 WHERE text LIKE '%\\\\%';
+----+-----------------------+
| id | text                  |
+----+-----------------------+
| 2  | D:\陈哈哈\加班        |
| 3  | D:\陈哈哈\加班        |
| 4  | D:\\陈哈哈\\加班      |
| 5  | D:\\陈哈哈\\加班      |
+----+-----------------------+
4 rows in set (0.00 sec)

For strings containing two backslashes, eight backslashes are needed in the LIKE pattern:

SELECT * FROM demo0526 WHERE text LIKE '%\\\\\\\\%';
+----+-----------------------+
| id | text                  |
+----+-----------------------+
| 4  | D:\\陈哈哈\\加班      |
| 5  | D:\\陈哈哈\\加班      |
+----+-----------------------+
2 rows in set (0.00 sec)

The article explains that LIKE performs a second level of escaping during its internal pattern‑matching step, so each literal backslash must be escaped twice.

For exact equality comparisons (=), only the first parsing step applies, so a single double‑escaped literal (e.g., '\\') works as expected.

3. Summary

MySQL’s handling of backslashes requires careful escaping: INSERT statements need one level of escaping, while LIKE patterns need two. Understanding this behavior prevents data inconsistencies when storing or querying paths and other strings containing backslashes.