MySQL drop column is exists

浏览数：26 / 时间：2015年06月12日

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;

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

MySQL drop column is exists