MySQL drop column is exists

Sometimes we need to drop column from a database, developers write script to drop the column by following:

ALTER TABLE <table>DROP COLUMN <column>
But if the column is already dropped from the database it will through a error as the column is no longer exist. We want to do is to drop a table or column, but only if it exists
Unfortunately there is an IF EXISTS clause for CREATE TABLE in MySQL but no one for ALTER TABLE ADD/DROP COLUMN.

DROP TABLE IF EXISTS <table>;
CREATE TABLE <table>
Solution : Creating following temporary stored procedure would resolve the problem in MySQL 5+:

delimiter //
CREATE PROCEDURE drop_column_sp() BEGIN
IF EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE table_name = ‘<table>‘ AND column_name = ‘<column>‘
and table_schema =‘<schema>‘
)
THEN
ALTER TABLE <table> DROP COLUMN <column>;
END IF;
END;
//
delimiter ‘;‘
CALL drop_column_sp(); DROP PROCEDURE drop_column_sp;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。