ADD column if none exists in MySQL

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 开发者_JAVA技巧EXISTS Survey_URL_Qualtrics VARCHAR(600);

    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.

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

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

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;