ADD column if none exists in MySQL

Question

I figure this is very easy, but I am trying to run this command only if those columns already don't exist:

ALTER TABLE `surveytable` ADD IF NOT EXISTS  Survey_Name_Qualtrics VARCHAR(20);
ALTER TABLE `surveytable` ADD IF NOT EXISTS Survey_URL_Qualtrics VARCHAR(600);

Answer 1

    CREATE PROCEDURE addcol() BEGIN
      IF NOT EXISTS(
        SELECT * FROM information_schema.COLUMNS
        WHERE COLUMN_NAME='new_column' AND TABLE_NAME='the_table' AND TABLE_SCHEMA='the_schema'
        )
    THEN
        ALTER TABLE `the_schema`.`the_table`
        ADD COLUMN `new_column` bigint(20) unsigned NOT NULL default 1;

       END IF;
    END;


     delimiter ';'

     CALL addcol();

DROP PROCEDURE addcol;

This is the code that i use, i dont think you can use if not exists on a ALTER column.

An alternative you can watch for the error you get if you try to insert a column that already exists, i think its 1062. And handle the error.

Of course the better way is to not get the error in the first place.

Answer 2

This is all over the internet. Basic idea is to check the system table INFORMATION_SCHEMA.COLUMNS for your column. I'm assuming "none exists" applies to the name of the column only.

http://snippets.dzone.com/posts/show/4663

http://www.genexbs.com/blog/gbs.php/2008/08/09/how-to-check-if-column-exists-in-mysql-t

Answer 3

Another straight forward query for this would be to tell MySQL to IGNORE any issues that comes up during the query.

ALTER IGNORE TABLE `surveytable` ADD COLUMN Survey_Name_Qualtrics VARCHAR(20);

Answer 4

The answer by diagnonalbatman did not work for me; MySQL 5.7 would complain about syntax errors when I tried it. It probably worked fine with an earlier version of MySQL, but the comment is more than 8 years old after all.

After some trial and error, this is what I ended up with:

DELIMITER //
DROP PROCEDURE IF EXISTS addColumn //
CREATE PROCEDURE addColumn()
BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM information_schema.COLUMNS
    WHERE COLUMN_NAME = 'column_name'
      AND TABLE_NAME = 'table_name'
      AND TABLE_SCHEMA = DATABASE()
  ) THEN
    ALTER TABLE `table_name`
    ADD COLUMN `column_name` bigint(20) UNSIGNED NOT NULL DEFAULT 1;
  END IF;
END //
DELIMITER ;
CALL addColumn;
DROP PROCEDURE addColumn;