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?
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_ESCAPESSQL 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.