Check if a column exists in a table with MySQL

Question

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 this would work, but it fails badly.

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;

Is there a way?

Answer 1

This works well for me.

SHOW COLUMNS FROM `table` LIKE 'fieldname';

With PHP it would be something like...

$result = mysql_query("SHOW COLUMNS FROM `table` LIKE 'fieldname'");
$exists = (mysql_num_rows($result))?TRUE:FALSE;

Answer 2

@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!

Answer 3

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.

Answer 4

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 |
+----------+

Answer 5

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'");

Answer 6

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.

Answer 7

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.

Answer 8

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());}     
}

Answer 9

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...

Answer 10

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/>';
}
?>

Answer 11

There are two functions below the first one let you check if a column in a database table exists which requires two arguments. Table name and column name.

function if_column_exists( $table, $column ) {
    global $db;
    
    if ( empty ( $table ) || empty ( $column ) ) {
        return FALSE;
    }
    if ( $result = $db->query( "SHOW COLUMNS FROM $table LIKE '$column'" ) ) {
        if ( $result->num_rows == 0 ) {
            return FALSE;
        } else {
            return TRUE;
        }
    }
}

To use the above function you have to call it by passing table name and column name like below.

if (  if_column_exists( 'accounts', 'group_id' ) == FALSE ) {
    $query  = "ALTER TABLE `accounts` ADD `group_id` bigint(20) NULL AFTER `memo`";
    $result = $db->query($query) or die($db->error);
}

The next function helps you to identify if a table exists or not and accepts the argument table name only.

function if_table_exists( $tablename ) {
    global $db; 

    if ( empty( $tablename ) ) {
        return FALSE;
    }
    if($result = $db->query("SHOW TABLES LIKE '$tablename'")) {
        if($result->num_rows == 0) {
            return FALSE;
        } else {
            return TRUE;
        }
    }
}

To call it use the following method with single table name argument.

if ( if_table_exists("messages") == FALSE ) { 
    $query = 'CREATE TABLE `messages` (
        `message_id` bigint(20) NOT NULL AUTO_INCREMENT,
        `message_datetime` datetime NOT NULL,
        `message_detail` varchar(1000) NULL,
        PRIMARY KEY (`message_id`)
    )'; 
    $result = $db->query($query) or die($db->error);
    echo 'Messages Table created.<br>';
}

Please note that you are passing global object of database as $db in the function so make sure its replaced with your database object or as below.

define ("DB_HOST", "localhost"); //Databse Host.
    define ("DB_USER", "root"); //Databse User.
    define ("DB_PASS", ""); //database password.
    define ("DB_NAME", "general_ledger"); //database Name.

    $db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }

Answer 12

Suggestions use COUNT(*) in place of *

SELECT
    COUNT(*) AS column_count
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = '$schema_name' AND
    TABLE_NAME = '$table_name' AND
    COLUMN_NAME = '$column_name'

Answer 13

Updated version of mfoo's answer for PHP7+

/* Check if a table contains a given field. */
function ColumnExists($conn, $table, $fieldname)
{
    $result = mysqli_query($conn, "SHOW COLUMNS FROM `$table` LIKE '$fieldname'");
    $exists = (mysqli_num_rows($result))?TRUE:FALSE;
    return $exists;
}