Tumbled Logic

Adding a column to a MySQL table if it doesn’t exist

Sometimes, through the course of developing something, you end up with a SQL script containing various statements which you intend to run on a staging or live database once you’re happy with it. The snag is, if your statements add any columns to a table, you can only run it once. You could split your script up into two, but if your script is fairly straightforward this might seem to be overkill.

As a workaround for the lack of ALTER TABLE foo ADD COLUMN IF NOT EXISTS, I recently used the following:

DROP PROCEDURE IF EXISTS someuniquename;

delimiter '//'

CREATE PROCEDURE someuniquename(IN tblname VARCHAR(32), IN colname VARCHAR(32), IN colspec TEXT) BEGIN

IF NOT EXISTS(

SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME=colname AND TABLE_NAME=tblname AND TABLE_SCHEMA=DATABASE()

)

THEN

SET @stmt = CONCAT('ALTER TABLE ', tblname, ' ADD COLUMN ', colname, ' ‘, colspec);

PREPARE s1 FROM @stmt;

EXECUTE s1;

END IF;

END;

//

delimiter ';'

CALL someuniquename('mytable', 'mycolumn', 'varchar(50) default NULL');

Of course, the use of someuniquename is to make up for the lack of a CREATE TEMPORARY PROCEDURE.