Using ALTER to drop a column if it exists in MySQL

For MySQL, there is none: MySQL Feature Request.

Allowing this is arguably a really bad idea, anyway: IF EXISTS indicates that you're running destructive operations on a database with (to you) unknown structure. There may be situations where this is acceptable for quick-and-dirty local work, but if you're tempted to run such a statement against production data (in a migration etc.), you're playing with fire.

But if you insist, it's not difficult to simply check for existence first in the client, or to catch the error.

MariaDB also supports the following starting with 10.0.2:

DROP [COLUMN] [IF EXISTS] col_name 

i. e.

ALTER TABLE my_table DROP IF EXISTS my_column;

But it's arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL.

There is no language level support for this in MySQL. Here is a work-around involving MySQL information_schema meta-data in 5.0+, but it won't address your issue in 4.0.18.

drop procedure if exists schema_change;

delimiter ';;'
create procedure schema_change() begin

    /* delete columns if they exist */
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column1') then
        alter table table1 drop column `column1`;
    end if;
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column2') then
        alter table table1 drop column `column2`;
    end if;

    /* add columns */
    alter table table1 add column `column1` varchar(255) NULL;
    alter table table1 add column `column2` varchar(255) NULL;

end;;

delimiter ';'
call schema_change();

drop procedure if exists schema_change;

I wrote some more detailed information in a blog post.

Chase Seibert's answer works, but I'd add that if you have several schemata you want to alter the SELECT thus:

select * from information_schema.columns where table_schema in (select schema()) and table_name=...

You can use this script, use your column, schema and table name

 IF EXISTS (SELECT *
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName' 
                                             AND TABLE_SCHEMA = SchemaName)
    BEGIN
       ALTER TABLE TableName DROP COLUMN ColumnName;
    END;

Perhaps the simplest way to solve this (that will work) is:

• CREATE new_table AS SELECT id, col1, col2, ... (only the columns you actually want in the final table) FROM my_table;

• RENAME my_table TO old_table, new_table TO my_table;

• DROP old_table;

Or keep old_table for a rollback if needed.

This will work but foreign keys will not be moved. You would have to re-add them to my_table later; also foreign keys in other tables that reference my_table will have to be fixed (pointed to the new my_table).

Good Luck...