7

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 ?

  • I'm curious...What's wrong with just letting the alter fail on error if the column already exists? – Derek Downey Apr 7 '17 at 19:37
  • Actually I need to provide .sql file to my client that would have all queries related to DB structure Changes. I can't send whole database them. I just want to send them db changes. there are many more queries in that sql file. If this query about creating columns failed then it would fail all queries. So that is why I want to use if condition to create column. – zzzzz Apr 7 '17 at 19:54
  • If your database supports it you could use a trigger. BEFORE INSERT. – cybernard Apr 8 '17 at 1:07
4

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 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=[Database Name] AND TABLE_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.

  • In MySQL/MariaDB I get an error using that query, stating that column_name does not exist. I reformulated the query to: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='[Schema_name]' AND TABLE_NAME='[Table_name]' and column_name='[Column_name]'; – Jesus Alonso Abad Jul 2 '18 at 7:37
  • For a simple 0 or 1 answer on both MySQL and (I think) MariaDB: SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='[Schema_name]' AND TABLE_NAME='[Table_name]' AND column_name='[Column_name]' – piojo Nov 9 '18 at 9:44
6

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);
  • 1
    > "MYSQL 5.5.5"? This is not correct, this is a Maria DB feature only. – Excalibur Jul 12 at 23:18
  • thanks for the correction – Paroofkey 19 hours ago
5

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.