MySQL: How to create Column if not exists?

Question

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 ?

Answer 1

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.

Answer 2

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

Answer 3

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;

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

Answer 5

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.