I am trying to create a Column for my table only if it does not exist. I have researched a lot but I could not find any solution yet.
Is this really possible to conditionally create Column ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
ALTER TABLE does not have
IF EXISTS specification.
You can do the following through using a stored proc or a programming language if this is something that you'll need to do on a regular basis:
Find if the column exists using the SQL below:
If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.
You can use this solution, already mentioned on another StackOverFlow post: (Ref.: https://stackoverflow.com/a/31989541/)
MySQL - ALTER TABLE to add a column if it does not exist:
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;
This below worked for me:
SELECT count(*) INTO @exist FROM information_schema.columns WHERE 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;