Check for column existence in mySQL

Question

I know this question has been asked before, but for some reason the suggested solutions do not work in my setup. I am calling a mySQL database from within matlab on windows, and need to check whether a given table has a specific column before my script commences calculating values for that column. From previous answers, I get that the following should help me determine if col1 exists:

select exists (select * from table1 where col1=val1)

And this works fine if col1 exists. However, when it doesn't I get the following:

>> fetch(conn,'select exists (select * from model12B where col1=.5)')

Error using database/fetch (line 37)
[MySQL][ODBC 5.3(a) Driver][mysqld-5.5.37-log]Unknown column 'col1' in 'where clause'

This looks more like a MySQL error than a matlab error, hence why I'm framing it as a MySQL question. I can of course wrap this in a try-catch block, but that feels wrong. Can anyone suggest a good way to ask for existence without generating an error?

Answer 1

You can use the information_schema to tell you if the column exists in specified database and specified table :

 select * from information_schema.columns where table_name='tablename' and table_schema='databasename' and and column_name='columnname'

Or you can use "show columns " command:

 show columns from databasename.tablename where like 'columnname';

Of course, you can use the try - catch to test if a column exists of not. I don't think there is any side effect if you analyze the error message carefully.