MySQL: ALTER TABLE if column not exists

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.COLUMNSWHERE(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;

Here is a solution that does not involve querying INFORMATION_SCHEMA, it simply ignores the error if the column does exist.

DROP PROCEDURE IF EXISTS `?`;DELIMITER //CREATE PROCEDURE `?`()BEGINDECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;ALTER TABLE `table_name` ADD COLUMN `column_name` INTEGER;END //DELIMITER ;CALL `?`();DROP PROCEDURE `?`;

P.S. Feel free to give it other name rather than ?

Add field if not exist:

CALL addFieldIfNotExists ('settings', 'multi_user', 'TINYINT(1) NOT NULL DEFAULT 1');

addFieldIfNotExists code:

DELIMITER $$DROP PROCEDURE IF EXISTS addFieldIfNotExists $$DROP FUNCTION IF EXISTS isFieldExisting $$CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) RETURNS INTRETURN (SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.columns WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = table_name_IN AND COLUMN_NAME = field_name_IN)$$CREATE PROCEDURE addFieldIfNotExists (IN table_name_IN VARCHAR(100), IN field_name_IN VARCHAR(100), IN field_definition_IN VARCHAR(100))BEGINSET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);IF (@isFieldThere = 0) THENSET @ddl = CONCAT('ALTER TABLE ', table_name_IN);SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;SET @ddl = CONCAT(@ddl, ' ', field_name_IN);SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);PREPARE stmt FROM @ddl;EXECUTE stmt;DEALLOCATE PREPARE stmt;END IF;END;$$

• this is not original, but it IS copy-and-paste
• source: javajon.blogspot.com/2012/10/

I used this approach (Without using stored procedure):

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'column_name'

If it didnt return any rows then the column doesn't exists then alter the table:

ALTER TABLE tbl_nameADD COLUMN column_name TINYINT(1) NOT NULL DEFAULT 1

Hope this helps.

hope this will help you

SELECT COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE table_name = 'tbl_name'AND table_schema = 'db_name'AND column_name = 'column_name'

or

delimiter '//'CREATE PROCEDURE addcol() BEGINIF NOT EXISTS(SELECT * FROM information_schema.COLUMNSWHERE COLUMN_NAME='new_column' AND TABLE_NAME='tablename' AND TABLE_SCHEMA='the_schema')THENALTER TABLE `the_schema`.`the_table`ADD COLUMN `new_column` TINYINT(1) NOT NULL DEFAULT 1;;END IF;END;//delimiter ';'CALL addcol();DROP PROCEDURE addcol;

$sql="SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Your_Table_Name' AND COLUMN_NAME = 'Your_New_Column_Name'";$RESULT = mysqli_query($conn,$sql);

The abobe query return 0 if the column is not present in your table then you need to run alter query like below

if($RESULT){$sqll="ALTER TABLE Your_table_Name ADD COLUMN Your_New_Column_Name varchar(20) NOT NULL DEFAULT 0";}

SET @dbname = DATABASE();SET @tablename = "table";SET @columnname = "fieldname";SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNSWHERE(table_name = @tablename)AND (table_schema = @dbname)AND (column_name = @columnname)) > 0,"SELECT 1",CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(18,4) NULL;")));PREPARE alterIfNotExists FROM @preparedStatement;EXECUTE alterIfNotExists;DEALLOCATE PREPARE alterIfNotExists; 

This below worked for me:

 SELECT count(*)INTO @existFROM information_schema.columnsWHERE table_schema = 'mydatabase'and COLUMN_NAME = 'mycolumn'AND table_name = 'mytable' LIMIT 1;set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable` ADD COLUMN `mycolumn` MEDIUMTEXT NULL','select \'Column Exists\' status');prepare stmt from @query;EXECUTE stmt;

Sometimes it may happen that there are multiple schema created in a database.

So to be specific schema we need to target, so this will help to do it.

SELECT count(*) into @colCnt FROM information_schema.columns WHERE table_name = 'mytable' AND column_name = 'mycolumn' and table_schema = DATABASE();IF @colCnt = 0 THENALTER TABLE `mytable` ADD COLUMN `mycolumn` VARCHAR(20) DEFAULT NULL;END IF;

Using this as an example:https://dbabulletin.com/index.php/2018/03/29/best-practices-using-flyway-for-database-migrations/

DELIMITER $$DROP PROCEDURE IF EXISTS upgrade_database_4_to_5 $$CREATE PROCEDURE upgrade_database_4_to_5()BEGINIF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()AND COLUMN_NAME='multi_user' AND TABLE_NAME='settings') ) THENALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1;END IF;END $$CALL upgrade_database_4_to_5() $$DELIMITER ;

Simplified:

ALTER TABLE my_table ADD COLUMN IF NOT EXISTS my_column VARCHAR(255);