使用 ALTER 删除 MySQL 中存在的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/173814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using ALTER to drop a column if it exists in MySQL
提问by jcodeninja
How can ALTER be used to drop a column in a MySQL table if that column exists?
如果该列存在，如何使用 ALTER 删除 MySQL 表中的列？
I know I can use ALTER TABLE my_table DROP COLUMN my_column, but that will throw an error if my_columndoes not exist. Is there alternative syntax for dropping the column conditionally?
我知道我可以使用ALTER TABLE my_table DROP COLUMN my_column，但如果my_column不存在，则会引发错误。是否有条件删除列的替代语法？
I'm using MySQL version 4.0.18.
我使用的是 MySQL 4.0.18 版。
采纳答案by Matthias Winkelmann
For MySQL, there is none:MySQL Feature Request.
对于 MySQL，没有：MySQL Feature Request。
Allowing this is arguably a really bad idea, anyway: IF EXISTSindicates that you're running destructive operations on a database with (to you) unknown structure. There may be situations where this is acceptable for quick-and-dirty local work, but if you're tempted to run such a statement against production data (in a migration etc.), you're playing with fire.
无论如何，允许这样做可以说是一个非常糟糕的主意：IF EXISTS表明您正在对（对您而言）结构未知的数据库上运行破坏性操作。在某些情况下，这对于快速而繁琐的本地工作是可以接受的，但是如果您想针对生产数据（在迁移等中）运行这样的语句，那么您就是在玩火。
But if you insist, it's not difficult to simply check for existence first in the client, or to catch the error.
但是，如果您坚持，那么简单地首先在客户端中检查是否存在或捕获错误并不困难。
MariaDB also supports the following starting with 10.0.2:
从 10.0.2 开始，MariaDB 还支持以下内容：
DROP [COLUMN] [IF EXISTS] col_name
i. e.
IE
ALTER TABLE my_table DROP IF EXISTS my_column;
But it's arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL.
但是，依赖仅由 MySQL 的几个分支之一支持的非标准功能可以说是一个坏主意。
回答by Chase Seibert
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.
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;
I wrote some more detailed information in a blog post.
我在博客文章中写了一些更详细的信息。
回答by Pradeep Puranik
I know this is an old thread, but there is a simple way to handle this requirement without using stored procedures. This may 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, as it did me (after a lot of trial and error).
希望这对某人有帮助，就像对我一样（经过多次反复试验）。
回答by sp00m
I just built a reusable procedure that can help making DROP COLUMNidempotent:
我刚刚构建了一个可以帮助实现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 ;
Usage:
用法：
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
回答by DrHyde
Chase Seibert's answer works, but I'd add that if you have several schemata you want to alter the SELECT thus:
Chase Seibert 的答案有效，但我要补充一点，如果您有多个架构，您想这样更改 SELECT：
select * from information_schema.columns where table_schema in (select schema()) and table_name=...
回答by Frank Flynn
Perhaps the simplest way to solve this (that will work) is:
也许解决这个问题的最简单方法（可行）是：
CREATE new_table AS SELECT id, col1, col2, ... (only the columns you actually want in the final table) FROM my_table;
RENAME my_table TO old_table, new_table TO my_table;
DROP old_table;
CREATE new_table AS SELECT id, col1, col2, ...（只有你在最终表中真正想要的列） FROM my_table;
将 my_table 重命名为 old_table，将 new_table 重命名为 my_table；
删除旧表；
Or keep old_table for a rollback if needed.
或者在需要时保留 old_table 以进行回滚。
This will work but foreign keys will not be moved. You would have to re-add them to my_table later; also foreign keys in other tables that reference my_table will have to be fixed (pointed to the new my_table).
这将起作用，但不会移动外键。稍后您必须将它们重新添加到 my_table 中；其他表中引用 my_table 的外键也必须修复（指向新的 my_table）。
Good Luck...
祝你好运...
回答by Shah Zai?
You can use this script, use your column, schema and table name
您可以使用此脚本，使用您的列、架构和表名
IF EXISTS (`enter code here`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;
回答by ajp
I realise this thread is quite old now, but I was having the same problem. This was my very basic solution using the MySQL Workbench, but it worked fine...
我意识到这个线程现在已经很老了，但我遇到了同样的问题。这是我使用 MySQL Workbench 的非常基本的解决方案，但效果很好......
- get a new sql editor and execute SHOW TABLES to get a list of your tables
- select all of the rows, and choose copy to clipboard (unquoted) from the context menu
- paste the list of names into another editor tab
- write your query, ie ALTER TABLE
xDROPa; - do some copying and pasting, so you end up with separate query for each table
- Toggle whether the workbench should stop when an error occurs
- Hit execute and look through the output log
- 获取一个新的 sql 编辑器并执行 SHOW TABLES 以获取表列表
- 选择所有行，然后从上下文菜单中选择复制到剪贴板（未引用）
- 将姓名列表粘贴到另一个编辑器选项卡中
- 编写您的查询，即 ALTER TABLE
xDROPa； - 做一些复制和粘贴，所以你最终会对每个表进行单独的查询
- 切换工作台是否应在发生错误时停止
- 点击执行并查看输出日志
any tables which had the table now haven't any tables which didn't will have shown an error in the logs
任何有该表的表现在都没有任何没有的表将在日志中显示错误
then you can find/replace 'drop a' change it to 'ADD COLUMN bINT NULL' etc and run the whole thing again....
然后你可以找到/替换 'drop a' 将其更改为 'ADD COLUMN bINT 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.
有点笨重，但最后你得到了最终结果，你可以控制/监视整个过程，并记住保存你的 sql 脚本，以防你再次需要它们。
