13

I want to add a column to a table, but I don't want it to fail if it has already been added to the table. How can I achieve this?

# Add column fails if it already exists 
ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Andrew
  • 227,796
  • 193
  • 515
  • 708
  • 2
    possible duplicate of [add column to mysql table if it does not exist](http://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist) – derobert Feb 28 '14 at 10:13

1 Answers1

18

Use the following in a stored procedure:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

Reference:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Why the `!=` comparison. I suspect there must be a good reason for it. – Saad Farooq Dec 14 '12 at 23:03
  • @SaadFarooq: Corrected the column check, but the syntax is otherwise correct -- [COLUMN is optional if you check the documentation](http://dev.mysql.com/doc/refman/5.1/en/alter-table.html) – OMG Ponies Dec 15 '12 at 01:42
  • 2
    I get a syntax error. Seems to choke after `INFORMATION_SCHEMA.COLUMNS`. Did the schema change in MySQL 5.6? – bafromca Aug 27 '14 at 15:54