MySQL: ALTER TABLE if column not exists

alter-table, ddl, mysql

Solution

Use the following in a stored procedure:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

Problem

I have this code: ``` ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 ``` And I want to alter this table only if this column doesn't exist. I'm trying a lot of different ways, but nothing works: ``` ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1 ``` With procedure: ``` DELIMITER $$ CREATE PROCEDURE Alter_Table() BEGIN DECLARE _count INT; SET _count = ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'settings' AND COLUMN_NAME = 'multi_user'); IF _count = 0 THEN ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 END IF; END $$ DELIMITER ; ``` I got error in END IF, then in END and then in 1 How can I make this as simple as possible?

Original source

Related problems