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 ?

zzzzz
  • 483
  • 1
  • 4
  • 5
  • I'm curious...What's wrong with just letting the alter fail on error if the column already exists? – Derek Downey Apr 07 '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 07 '17 at 19:54

5 Answers5

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);
Paroofkey
  • 591
  • 1
  • 4
  • 4
22

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.
mustaccio
  • 25,896
  • 22
  • 57
  • 72
thatsaru
  • 678
  • 4
  • 9
  • 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]'; – Jesus Alonso Abad Jul 02 '18 at 07: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 09 '18 at 09:44
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;
Anto Jose
  • 151
  • 1
  • 4
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;
Tejpal Sharma
  • 141
  • 2
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.

Max
  • 111
  • 3