I have a column called \'column. I am able to use it in SELECT statements just like any other column. However, when I try to perform SHOW COLUMNS FROM myTable LIKE '\\''column' I get no results. I observed that it works if I double escape the backslash: '\\\\''column'.

I tested this from MariaDB console, but I also observed the same behaviour in MySQL 8.

How does the escaping work? How should I properly escape the value so that I can fetch the column information?

DB Fiddle

CodePudding user response：

From the mysql documentation

MySQL uses C escape syntax in strings (for example, \n to represent the newline character). If you want a LIKE string to contain a literal , you must double it. (Unless the NO_BACKSLASH_ESCAPES SQL mode is enabled, in which case no escape character is used.) For example, to search for \n, specify it as \\n. To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.