Testing for existence of a column

Question

Say I am not sure if table t in database d has a column x or not. Perhaps this is because some of the databases the application uses have been upgraded and some have not, so some have t.x and some don't.

Now say I just need a simple query to get a row from d.t and I want the value of d.t.x if the column exists and some default if not.

I know how to do it with two queries in which the first looks to see if column x exists and the app adjusts the second query accordingly. But can I move this logic into the SQL and do it in one query? If there were a COLUMN_EXISTS() function then the query would be:

SELECT s, t, IF(COLUMN_EXISTS(x), x, 'NO_COL_x') AS x FROM d.t ...;

Answer 1

Here's the query you would be interested in:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘tablename’ AND COLUMN_NAME = ‘column’)

You could probably wrap that up in a function that takes the table name and column name as parameters, then use that in your query.

Still not sure if that would work though, since the query might still error out due to the reference to the non-existant column in your query (even though the IF would always return false if the column didn't exist)

Another option would be to use that function to dynamically build your query rather than trying to use it in the query itself. But that would of course get a bit more complicated.

Ultimately, the best way to do it would be to use a stored procedure rather than putting the query in your code. That way, when the database gets upgraded the sproc would get "upgraded" as well to match the new schema and you wouldn't have to worry (as much) about whether the code version matched the database version or not.

Answer 2

There is a view INFORMATION_SCHEMA.COLUMNS which contains a table column and column_name. If you need to see if a column exists in a table, you can select from there.

Answer 3

You might be looking for this:

SHOW COLUMNS FROM my_table LIKE 'my_column'