使用 ALTER 删除 MySQL 中存在的列

如果存在 MySQL 表中的列，如何使用 ALTER 删除该列？

我知道我可以使用 ALTER TABLE my_table DROP COLUMN my_column，但是如果 my_column不存在，那将抛出一个错误。有条件删除列的替代语法吗？

我使用的是 MySQL 4.0.18版本。

151235 次浏览
小开
最佳答案

对于 MySQL，没有: MySQL 特性请求。

无论如何，允许这样做可以说是一个非常糟糕的主意: IF EXISTS表明您正在对一个结构未知的数据库运行破坏性操作。在某些情况下，这对于快速而肮脏的本地工作是可以接受的，但是如果您试图对生产数据运行这样的语句(在迁移中等) ，那么您就是在玩火。

但是，如果您坚持这样做，那么首先在客户机中检查是否存在，或者捕获错误并不困难。

MariaDB also supports the following starting with 10.0.2:

DROP [COLUMN] [IF EXISTS] col_name

也就是说。

ALTER TABLE my_table DROP IF EXISTS my_column;

但是，依赖 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;

我在 博客文章中写了一些更详细的信息。

小开

Chase Seibert 的回答是可行的，但是我要补充的是，如果你有几个模式，你可以这样修改 SELECT:

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

我意识到这个帖子现在已经很旧了，但是我也有同样的问题。 这是我使用 MySQL 工作台的非常基本的解决方案，但它工作得很好..。

  1. 获取一个新的 sql 编辑器并执行 SHOW TABLES 以获取表的列表
  2. 选择所有行，并从上下文菜单中选择“复制到剪贴板(未引号)”
  3. 将名称列表粘贴到另一个编辑器选项卡中
  4. 写下你的查询，即 ALTER TABLE x DROP a;
  5. 执行一些复制和粘贴操作，因此最终为每个表提供单独的查询
  6. 切换工作台是否在发生错误时停止
  7. 点击执行并查看输出日志

any tables which had the table now haven't 任何没有显示的表将在日志中显示错误

然后您可以找到/替换’下降 a’改为’添加列 b INT NULL’等，并再次运行整个事情... 。

a bit clunky, but at last you get the end result and you can control/monitor the whole process and remember to save you sql scripts in case you need them again.

小开

我知道这是一个老线程，但是有一种简单的方法可以不使用存储过程来处理这个需求。这也许能帮到某人。

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 ;

希望这对某人有所帮助，就像它对我一样(经过大量的试验和错误)。

小开

我刚刚构建了一个可重用的程序，它可以帮助使 DROP COLUMN幂等:

-- 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 ;

用法:

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

例如:

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
小开

也许解决这个问题最简单的方法是:

  • CREATE new _ table AS SELECT id，col1，col2，... (只包括您在最终表中实际需要的列) FROM my _ table;

  • RENAME my _ table TO old _ table，new _ table TO my _ table;

  • DROP old _ table;

或者保留 old _ table 以便在需要时进行回滚。

这将工作，但外键将不会被移动。稍后您必须将它们重新添加到 my _ table; 还必须修复引用 my _ table 的其他表中的外键(指向新的 my _ table)。

祝你好运..。

小开

您可以使用这个脚本，使用您的列、架构和表名

 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;

提交答案