使用 ALTER 删除 MySQL 中存在的列

Question

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

我知道我可以使用ALTER TABLE my_table DROP COLUMN my_column，但如果my_column不存在，则会抛出错误。 是否有替代语法来有条件地删除列？

我正在使用 MySQL 版本 4.0.18。

Answer 1

对于 MySQL，没有： MySQL 功能请求 。

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

但如果您坚持的话，首先在客户端检查是否存在或捕获错误并不困难。

从 10.0.2 开始，MariaDB 还支持以下功能：

DROP [COLUMN] [IF EXISTS] col_name 

即

ALTER TABLE my_table DROP IF EXISTS my_column;

但是依赖于仅由 MySQL 的几个分支之一支持的非标准功能可能是一个坏主意。

Answer 2

MySQL 中没有对此的语言级别支持。 这是 5.0+ 中涉及 MySQL information_schema 元数据的解决方法，但它不会解决 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;

我在 博客文章。

Answer 3

我知道这是一个旧线程，但是有一种简单的方法可以在不使用存储过程的情况下处理此要求。 这可能会对某人有所帮助。

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 ;

希望这对某人有帮助，就像对我一样（经过大量的尝试和错误）。

Answer 4

我刚刚构建了一个可重用的过程，可以帮助使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

Answer 5

Chase Seibert 的答案有效，但我要补充一点，如果您有多个模式，则需要更改 SELECT：

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

Answer 6

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

 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;

Answer 7

也许解决这个问题的最简单方法（可行）是：

• CREATE new_table AS SELECT id, col1, col2, ...（仅在最终表中实际想要的列）
  FROM my_table;

• 将 my_table 重命名为 old_table，将 new_table 重命名为 my_table；

• DROP old_table;

或者保留 old_table 以供需要时回滚。

这可以工作，但外键不会被移动。 您必须稍后将它们重新添加到 my_table 中； 引用 my_table 的其他表中的外键也必须修复（指向新的 my_table）。

祝你好运...

Answer 8

这只是所有好的答案的摘要：

1. IF EXISTS not isn'tiens in ALTER TABEL your_table DROP COLUMN your_column
2. 要获得方便的解决方案，请创建以下过程

delimiter //
CREATE PROCEDURE `drop_column_if_exists`(IN TNAME VARCHAR(255), IN CNAME VARCHAR(255))
BEGIN
SET @column_exists := (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE TABLE_NAME = TNAME 
    AND COLUMN_NAME = CNAME
    AND TABLE_SCHEMA = database()
) ;
SET @sqlstmt := IF(
        @column_exists > 0,
        CONCAT("ALTER TABLE ", TNAME, " DROP COLUMN ",  CNAME), 
        "SELECT 1") ;
PREPARE stmt FROM @sqlstmt ;
EXECUTE stmt ;
END//
delimiter ';'

测试：

CREATE TABLE test_table (test_column_1 int, test_column_2 int);
CALL drop_column_if_exists('test_table', 'test_column_1');
DECRIBE test_table;

+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| test_column_2 | int(11) | YES  |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+
1 row in set (0,002 sec)

Answer 9

非常简单的方法。 您只需更改第 2 行和第 3 行中的“my_table”和“my_column”即可。

无需任何存储过程等。

SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_column";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) = 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " DROP COLUMN ", @columnname, ";")
));
PREPARE alterIfExists FROM @preparedStatement;
EXECUTE alterIfExists;
DEALLOCATE PREPARE alterIfExists;

Answer 10

我意识到这个线程现在已经很老了，但我也遇到了同样的问题。
这是我使用 MySQL Workbench 的非常基本的解决方案，但它工作得很好...

1. 获取一个新的 sql 编辑器并执行 SHOW TABLES 来获取表的列表
2. ，选择所有行，然后从上下文菜单
3. 将名称列表粘贴到另一个编辑器选项卡中
4. 写入您的查询，即 ALTER TABLE x DROP a;
5. 进行一些复制和粘贴，因此您最终会对每个表进行单独的查询
6. 切换工作台在发生错误时是否应停止
7. 点击执行并查看输出日志

任何具有该表但现在还没有的表
任何没有的表都会在日志中显示错误，

然后您可以找到/替换“drop a”，将其更改为“ADD COLUMN b INT NULL”等再次运行整个过程......

有点笨拙，但最后你得到了最终结果，你可以控制/监视整个过程，并记住保存 sql 脚本，以防你再次需要它们。