How to Check If Column Exists Before Altering Table in MySQL

ddlMySQLPHP

I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use. 

SELECT COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

Unfortunately the DATABASE() function returns information_schema instead of current connection DB at least in PHPMyAdmin. It may be different through the PHP mysql driver. Therefore, it returns zero for the column count.

SELECT DATABASE(), COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

This returns: "Information_schema", "0" despite the fact that the original connection was to a different database.

I have come up with a workaround in PHP but would still like to know if this is possible using only SQL.

Best Answer

You were on the right track. Add one thing before your code:

USE YourDb;  -- You need to be in _your_ db to make `DATABASE()` work right

SELECT COUNT(*) as col_count
    FROM information_schema.COLUMNS
    WHERE  TABLE_SCHEMA = DATABASE()
      AND  TABLE_NAME = 'mytable'
      AND  COLUMN_NAME = 'status';
Related Question