Mysql script check whether column exist or not before adding column in table

Question

I need mysql script (not a stored procedure) that check whether column exist or not before altering the table.

Answer 1

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

If the above returns 0 rows, well, you know the column doesn't exist.

Answer 2

Try this

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

Answer 3

You can do it in this way

SHOW columns from `yourtable` where field='yourfield'

Answer 4

You can retrieve the existence of the field ...

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'your_db_name' 
AND TABLE_NAME = 'your_table_name' 
AND COLUMN_NAME = 'your_column_name'

...but you cannot add an ALTER-statement depending on the outcome. SQL just can't do that.

The place for that kind of logic is either in a stored procedure or in an application language.

Answer 5

Try this

function add_column_if_not_exist($db, $column, $column_attr ="VARCHAR(255) NULL")
{
    $exists = false;
    $columns = mysql_query("show columns from $db");
    while($c = mysql_fetch_assoc($columns)){
        if($c['Field'] == $column){
            $exists = true;
            break;
        }
    }      
    if(!$exists){
        mysql_query("ALTER TABLE `$db` ADD `$column`  $column_attr");
    }
}