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
  • 314,254
  • 77
  • 507
  • 490
Andrew
  • 212,236
  • 189
  • 499
  • 691
  • 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
  • 314,254
  • 77
  • 507
  • 490
  • 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