MySQL: How to Check if a Column Exists in a Table with SQL

To check if a column exists in a table with SQL using a MySQL database is pretty easy, thanks to the nifty SHOW COLUMNS command.

To find if a specific column exists, we simply extend the standard SHOW COLUMNS FROM statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standard % modifier. If the statement returns a non-empty result set, we know the column exists.

For example:

SHOW COLUMNS FROM `table` LIKE 'column-name';

will return a empty result set if your table doesn’t have a column named ‘column-name’ in it. Similarly,

SHOW COLUMNS FROM `table` LIKE 'column-name%';

will return a result set containing all columns that start with ‘column-name’, e.g. ‘column-name-a’ and ‘column-name-b’ as well as plain old ‘column-name’ of course!

Nifty.