Using ALTER to delete a column if it exists in MySQL

Question

How can I use ALTER to delete a column in a MySQL table if that column exists?

I know that I can use ALTER TABLE my_table DROP COLUMN my_column , but this will result in an error if my_column does not exist. Is there an alternative syntax for conditionally deleting a column?

I am using MySQL version 4.0.18.

Answer 1

For MySQL it is not: MySQL Feature Request .

In any case, allowing this may be a really bad idea: IF EXISTS indicates that you are performing destructive operations on a database with (for you) an unknown structure. There may be situations where it is acceptable for fast and dirty local work, but if you are tempted to make such a statement against production data (during migration, etc.), you play with fire.

But if you insist, it is easy to first check for the presence in the client or to catch an error.

MariaDB also supports the following, starting with 10.0.2:

DROP [COLUMN] [IF EXISTING] col_name

those.

ALTER TABLE my_table DROP IF EXISTS my_column;

But it might be a bad idea to rely on a non-standard function supported by only one of several MySQL forks.

Answer 2

There is no language level support in MySQL for this. Here is a workaround including the MySQL information_schema metadata in 5. 0+, but it will not solve your problem 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 more information on the blog .

Answer 3

I know this is an old thread, but there is an easy way to handle this requirement without using stored procedures. It might help someone.

 set @exist_Check := ( select count(*) from information_schema.columns where TABLE_NAME='YOUR_TABLE' and COLUMN_NAME='YOUR_COLUMN' and TABLE_SCHEMA=database() ) ; set @sqlstmt := if(@exist_Check>0,'alter table YOUR_TABLE drop column YOUR_COLUMN', 'select ''''') ; prepare stmt from @sqlstmt ; execute stmt ; 

Hope this helps someone like me (after a lot of trial and error).

Answer 4

I just created a reusable procedure that can help make DROP COLUMN idempotent:

 -- column_exists: DROP FUNCTION IF EXISTS column_exists; DELIMITER $$ CREATE FUNCTION column_exists( tname VARCHAR(64), cname VARCHAR(64) ) RETURNS BOOLEAN READS SQL DATA BEGIN RETURN 0 < (SELECT COUNT(*) FROM 'INFORMATION_SCHEMA'.'COLUMNS' WHERE 'TABLE_SCHEMA' = SCHEMA() AND 'TABLE_NAME' = tname AND 'COLUMN_NAME' = cname); END $$ DELIMITER ; -- drop_column_if_exists: DROP PROCEDURE IF EXISTS drop_column_if_exists; DELIMITER $$ CREATE PROCEDURE drop_column_if_exists( tname VARCHAR(64), cname VARCHAR(64) ) BEGIN IF column_exists(tname, cname) THEN SET @drop_column_if_exists = CONCAT('ALTER TABLE '', tname, '' DROP COLUMN '', cname, '''); PREPARE drop_query FROM @drop_column_if_exists; EXECUTE drop_query; END IF; END $$ DELIMITER ; 

Using:

 CALL drop_column_if_exists('my_table', 'my_column'); 

Example:

 SELECT column_exists('my_table', 'my_column'); -- 1 CALL drop_column_if_exists('my_table', 'my_column'); -- success SELECT column_exists('my_table', 'my_column'); -- 0 CALL drop_column_if_exists('my_table', 'my_column'); -- success SELECT column_exists('my_table', 'my_column'); -- 0 

Answer 5

Chase Seibert's answer works, but I would add that if you have multiple schemas, you want to change SELECT this way:

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

Answer 6

Perhaps the easiest way to solve this problem (this will work):

• CREATE new_table AS SELECT id, col1, col2, ... (only those columns that you really need in the final table) FROM my_table;

• EDIT my_table TO old_table, new_table TO my_table;

• DROP old_table;

Or save old_table for rollback if necessary.

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

Good luck ...

Answer 7

I understand that this thread is quite old, but I had the same problem. This was my most basic solution using MySQL Workbench, but it worked perfectly ...

• get a new sql editor and run SHOW TABLES to get a list of your tables.
• select all lines and select a copy to the clipboard (without quotes) from the context menu
• paste the list of names into another tab of the editor.
• write your request, i.e. ALTER TABLE x DROP a ;
• do some copy and paste, so you get a separate query for each table.
• Toggle whether the desktop should stop when an error occurs.
• Hit executes and scans the output log

any tables in which the table was no longer any tables that did not display an error in the logs

then you can find / replace "drop a " change it to "ADD COLUMN b INT NULL" etc. and run it all again.

a bit awkward, but finally you get the final result, and you can control / control the whole process and remember to save SQL scripts if you need them.