MySQL: How to create Column if not exists?
Actually exists now for Maria DB 10.219
ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255);
Bonus, it works for MODIFY as well
ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);
MySQL 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:
Pseudocode:
Find if the column exists using the SQL below:
SELECT
column_name
FROMINFORMATION_SCHEMA
.COLUMNS
WHERETABLE_SCHEMA
=[Database Name] ANDTABLE_NAME
=[Table Name];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;