38

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 ?

2
  • I'm curious...What's wrong with just letting the alter fail on error if the column already exists? Apr 7, 2017 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, 2017 at 19:54

5 Answers 5

21

MySQL ALTER TABLE does not have the IF EXISTS option.

You can do the following in a stored procedure or a program 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>'
    AND `COLUMN_NAME`='<Column Name>'; -- column name you are searching for
  • If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.
2
  • 1
    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]'; Jul 2, 2018 at 7:37
  • 4
    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, 2018 at 9:44
49

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
  • If the column exists it produces a warning, that can be "catched" with SHOW WARNINGS;
    – Max Muster
    Dec 23, 2020 at 13:49
15

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;
4

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;
0
1

I know you're looking for a mysql-answer. Nevertheless mysql is mostly used inside other programming languages, and sent to the server by those. Many languages have mechanisms to catch runtime errors an react accordingly. So you could just send the command to the DB and catch the error. Which would allow your program to keep running and having made sure the column exists. Example in php:

try {
      $DB->query("ALTER TABLE myTable ADD `example_Col` int NOT NULL");
}catch(Exception $e)
{
  echo "\nWarning example_Col already exists\n";
}

This is assuming $DB is an objct that allows you to send a query of course.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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