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

Check out [This Question](http://stackoverflow.com/questions/3242080/mysql-how-to-add-a-column-if-it-doesnt-already-exist) — nickmoriarty, Feb 21 '11 at 18:18
And [This question](http://stackoverflow.com/questions/3953833/if-not-exists-for-adding-mysql-column) — The Scrum Meister, Feb 21 '11 at 18:23

diagonalbatman · Accepted Answer · 2012-02-22T13:16:26.827

    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.

If you are going to copy a answer from another forum, its only right to add a [link to it](http://forums.codewalkers.com/database-help-83/alter-table-structure-conditional-adding-columns-if-they-dont-already-856291.html). I would like the same to be done when others link to a SO answer. — The Scrum Meister, Feb 21 '11 at 18:19

score 1 · Answer 2 · edited Feb 13 '12 at 10:10

1

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

edited Feb 13 '12 at 10:10

bluish

26,356
27
122
180

answered Feb 21 '11 at 18:20

Jody

8,021
4
26
29

score 0 · Answer 3 · answered Apr 24 '13 at 11:48

0

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

answered Apr 24 '13 at 11:48

burntblark

1,680
1
15
25

this doesn`t work, it still generates errors. @see here: http://stackoverflow.com/questions/22172221/alter-ignore-table-add-column-if-not-exists-using-the-sql-statement – Radon8472 Sep 20 '16 at 13:36
Sadly it didn't work for me. – stramin Jan 24 '23 at 17:20

score 0 · Answer 4 · answered Nov 26 '19 at 10:03

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;

ADD column if none exists in MySQL

4 Answers4