add column to mysql table if it does not exist

Here is a working solution (just tried out with MySQL 5.0 on Solaris):

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
CREATE PROCEDURE upgrade_database_1_0_to_2_0()
BEGIN

-- rename a table safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME='my_old_table_name') ) THEN
    RENAME TABLE 
        my_old_table_name TO my_new_table_name,
END IF;

-- add a column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
    ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;

On a first glance it probably looks more complicated than it should, but we have to deal with following problems here:

• IF statements only work in stored procedures, not when run directly, e.g. in mysql client
• more elegant and concise SHOW COLUMNS does not work in stored procedure so have to use INFORMATION_SCHEMA
• the syntax for delimiting statements is strange in MySQL, so you have to redefine the delimiter to be able to create stored procedures. Do not forget to switch the delimiter back!
• INFORMATION_SCHEMA is global for all databases, do not forget to filter on TABLE_SCHEMA=DATABASE(). DATABASE() returns the name of the currently selected database.

If you are on MariaDB, no need to use stored procedures. Just use, for example:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

See here

Note that INFORMATION_SCHEMA isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.

One solution used by frameworks that use database migrations is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.

Another solution would be to just try the ALTER TABLE ADD COLUMN command. It should throw an error if the column already exists.

ERROR 1060 (42S21): Duplicate column name 'newcolumnname'

Catch the error and disregard it in your upgrade script.

Most of the answers address how to add a column safely in a stored procedure, I had the need to add a column to a table safely without using a stored proc and discovered that MySQL does not allow the use of IF Exists() outside a SP. I'll post my solution that it might help someone in the same situation.

SELECT count(*)
INTO @exist
FROM information_schema.columns 
WHERE table_schema = database()
and COLUMN_NAME = 'original_data'
AND table_name = 'mytable';

set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3', 
'select \'Column Exists\' status');

prepare stmt from @query;

EXECUTE stmt;

Another way to do this would be to ignore the error with a declare continue handler:

delimiter ;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
end;;
call foo();;

I think its neater this way than with an exists subquery. Especially if you have a lot of columns to add, and you want to run the script several times.

more info on continue handlers can be found at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html