MySQL: ALTER TABLE if column not exists

Question

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?

Answer 1

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; 

Answer 2

Use PREPARE/EXECUTE and querying the schema. The host doesn't need to have permission to create or run procedures :

SET @dbname = DATABASE(); SET @tablename = "tableName"; SET @columnname = "colName"; SET @preparedStatement = (SELECT IF(   (     SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS     WHERE       (table_name = @tablename)       AND (table_schema = @dbname)       AND (column_name = @columnname)   ) > 0,   "SELECT 1",   CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);") )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists;