ThoseKind ThoseKind - 3 months ago 13
MySQL Question

Error when selecting colum names from table in MySQL information schema

I am trying to get a list of the column names from a specific table in MySQL. I am running:

SELECT column_name
FROM information_schema.columns
WHERE table_name = `test 2.2`
AND table_schema = test


The database is called
test
and the table name is
test 2.2
and the rest of the syntax looks correct. However I keep receiving the error


Error Code: 1054. Unknown column 'test 2.2' in 'where clause'


Is there another way that I can do what I want and/or how do I get around this error?

Answer

Object names (in this case: a table name) are stored in the information schema as string literals, so they should be queried with single quotes ('):

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'test 2.2' AND table_schema = 'test'
-- Here -----------^--------^--------------------^----^