Mastering Backslash Escapes in MySQL: Insert & SELECT Gotchas

1. Backslash in INSERT statements

1. Practical test

We insert strings containing backslashes into a table demo0526 and observe how MySQL stores them.

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

The result set shows that one backslash becomes zero, two become one, three become one, four become two, and five become two after insertion.

2. Why does this happen?

In MySQL a backslash is an escape character. During parsing the first backslash escapes the second, so the stored value loses one backslash each time. Therefore a string like D:\陈哈哈\加班 is stored as D:\陈哈哈\加班 after the parser processes the third backslash.

2. Backslash in SELECT queries

1. Practical test

Using LIKE with backslashes shows that a single‑backslash pattern ( '%\%') matches nothing, and '%\\%' also matches nothing. To match a single backslash you need '%\\\\%', which returns rows containing one backslash. To match two backslashes you need '%\\\\\\\\%', and so on.

2. Why?

The LIKE pattern is first parsed (escaping once) and then processed by the pattern‑matching engine, which performs a second escape. Consequently each backslash in the pattern must be doubled twice to represent a literal backslash in the data.

For exact equality ( =) only the parser escape applies, so WHERE text = '\\' correctly matches a stored double backslash.

Conclusion

MySQL’s handling of backslashes can be unintuitive in both INSERT and SELECT statements. Developers should be aware of the double‑escaping behavior and adjust their queries or input validation accordingly.