The situation is this: You are running a database update script, and you need to add an additional field to a table in a way such that the script will not fail if the column already exists (from a previous update etc.). Most people would simply use a php script to get the layout of the table and then only execute the ALTER TABLE command if the column does not already exist, but sometimes we are bound by scripting restrictions, so here is the SQL code to do it:

delimiter ‘//’

CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(

SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME=’t4′ AND TABLE_NAME=’testtbl’ AND TABLE_SCHEMA=’testdb’
)
THEN

ALTER TABLE `testdb`.`testtbl`
ADD COLUMN `t4` bigint(20) unsigned NOT NULL default 1;

END IF;
END;
//

delimiter ‘;’

CALL addcol();

DROP PROCEDURE addcol;

One day they will add IF NOT EXISTS to the ALTER TABLE syntax, and then this post will be redundant