[mysql] MySQL, Check if a column exists in a table with SQL

I am trying to write a query that will check if a specific table in MySQL has a specific column, and if not — create it. Otherwise do nothing. This is really an easy procedure in any enterprise-class database, yet MySQL seems to be an exception.

I thought something like

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME='prefix_topic' AND column_name='topic_last_update') 
BEGIN 
ALTER TABLE `prefix_topic` ADD `topic_last_update` DATETIME NOT NULL;
UPDATE `prefix_topic` SET `topic_last_update` = `topic_date_add`;
END;

would work, but it fails badly. Is there a way?

Select just column_name from information schema and put the result of this query into variable. Then test the variable to decide if table needs alteration or not.

P.S. Don't foget to specify TABLE_SCHEMA for COLUMNS table as well.

@julio

Thanks for the SQL example. I tried the query and I think it needs a small alteration to get it working properly.

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'

That worked for me.

Thanks!

I am using this simple script:

mysql_query("select $column from $table") or mysql_query("alter table $table add $column varchar (20)");

It works if you are already connected to the database.

Just to help anyone who is looking for a concrete example of what @Mchl was describing, try something like

 SELECT * FROM information_schema.COLUMNS 
 WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table' 
 AND COLUMN_NAME = 'my_column'`

If it returns false (zero results) then you know the column doesn't exist.

I threw this stored procedure together with a start from @lain's comments above, kind of nice if you need to call it more than a few times (and not needing php):

delimiter //
-- ------------------------------------------------------------
-- Use the inforamtion_schema to tell if a field exists.
-- Optional param dbName, defaults to current database
-- ------------------------------------------------------------
CREATE PROCEDURE fieldExists (
OUT _exists BOOLEAN,      -- return value
IN tableName CHAR(255),   -- name of table to look for
IN columnName CHAR(255),  -- name of column to look for
IN dbName CHAR(255)       -- optional specific db
) BEGIN
-- try to lookup db if none provided
SET @_dbName := IF(dbName IS NULL, database(), dbName);

IF CHAR_LENGTH(@_dbName) = 0
THEN -- no specific or current db to check against
  SELECT FALSE INTO _exists;
ELSE -- we have a db to work with
  SELECT IF(count(*) > 0, TRUE, FALSE) INTO _exists
  FROM information_schema.COLUMNS c
  WHERE 
  c.TABLE_SCHEMA    = @_dbName
  AND c.TABLE_NAME  = tableName
  AND c.COLUMN_NAME = columnName;
END IF;
END //
delimiter ;

Working with fieldExists

mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_option', NULL) //
Query OK, 0 rows affected (0.01 sec)

mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_options', 'etrophies') //
Query OK, 0 rows affected (0.01 sec)

mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        1 |
+----------+

DO NOT put ALTER TABLE/MODIFY COLS or any other such table mod operations inside a TRANSACTION. Transactions are for being able to roll back a QUERY failure not for ALTERations...it will error out every time in a transaction.

Just run a SELECT * query on the table and check if the column is there...

Following is another way of doing it using plain PHP without the information_schema database:

$chkcol = mysql_query("SELECT * FROM `my_table_name` LIMIT 1");
$mycol = mysql_fetch_array($chkcol);
if(!isset($mycol['my_new_column']))
  mysql_query("ALTER TABLE `my_table_name` ADD `my_new_column` BOOL NOT NULL DEFAULT '0'");

This work for me with sample PDO :

public function GetTableColumn() {      
$query  = $this->db->prepare("SHOW COLUMNS FROM `what_table` LIKE 'what_column'");  
try{            
    $query->execute();                                          
    if($query->fetchColumn()) { return 1; }else{ return 0; }
    }catch(PDOException $e){die($e->getMessage());}     
}

Many thanks to Mfoo who has put the really nice script for adding columns dynamically if not exists in the table. I have improved his answer with PHP. The script additionally helps you find how many tables actually needed 'Add column' mysql comand. Just taste the recipe. Works like charm.

<?php
ini_set('max_execution_time', 0);

$host = 'localhost';
$username = 'root';
$password = '';
$database = 'books';

$con = mysqli_connect($host, $username, $password);
if(!$con) { echo "Cannot connect to the database ";die();}
mysqli_select_db($con, $database);
$result=mysqli_query($con, 'show tables');
$tableArray = array();
while($tables = mysqli_fetch_row($result)) 
{
     $tableArray[] = $tables[0];    
}

$already = 0;
$new = 0;
for($rs = 0; $rs < count($tableArray); $rs++)
{
    $exists = FALSE;

    $result = mysqli_query($con, "SHOW COLUMNS FROM ".$tableArray[$rs]." LIKE 'tags'");
    $exists = (mysqli_num_rows($result))?TRUE:FALSE;

    if($exists == FALSE)
    {
        mysqli_query($con, "ALTER TABLE ".$tableArray[$rs]." ADD COLUMN tags VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL");
        ++$new;
        echo '#'.$new.' Table DONE!<br/>';
    }
    else
    {
        ++$already;
        echo '#'.$already.' Field defined alrady!<br/>';    
    }
    echo '<br/>';
}
?>