如何在SQL Server中使用单个ALTER TABLE语句删除多个列？

Question

我想编写一个单一的SQL命令，在一个ALTER TABLE语句中删除单个表中的多个列。

来自MSDN的ALTER TABLE文档...

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed.

它说语句中可以列出多个列，但语法没有显示可选逗号或任何暗示语法的内容。

如果可能的话，我应该如何编写SQL以在一个语句中删除多个列？

- Jesse Webb

Answer 1

对于SQL Server：

ALTER TABLE TableName
    DROP COLUMN Column1, Column2;

语法如下：

DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] 

针对MySQL：

ALTER TABLE TableName
    DROP COLUMN Column1,
    DROP COLUMN Column2;

或者像这样¹：

ALTER TABLE TableName
    DROP Column1,
    DROP Column2;

¹除了RENAME COLUMN（用于区分列重命名操作和表重命名操作），单词COLUMN是可选的，可以省略。更多信息请点击这里。

- Alex Aza

---

@Gweebz - 我猜你遇到的错误与列的使用有关。列可以用于约束、索引或另一个计算列中。 - Alex Aza

准确地说，我拥有的第二列被用于索引。我删除了该索引后，我的DROP语句就完美地运行了。 - Jesse Webb

@jeicam - 这可能是因为你要删除的其中一列可能是主键。 - Benny

如果需要的话，您还可以添加IF EXISTS语句：DROP COLUMN IF EXISTS Column1。 - Fusion

如果需要的话，您还可以添加IF EXISTS语句，以便删除列时先检查列是否存在，语句如下：DROP COLUMN IF EXISTS Column1。 - undefined

Answer 2

总结

Oracle：

ALTER TABLE table_name DROP (column_name1, column_name2);

MS SQL Server：

ALTER TABLE table_name DROP COLUMN column_name1, column_name2

MySQL：

ALTER TABLE table_name DROP column_name1, DROP column_name2;

PostgreSQL

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2;

---

注意

DROP COLUMN 并不会在某些数据库管理系统中 物理上 移除数据，例如 MS SQL。对于固定长度类型（如 int、numeric、float、datetime、uniqueidentifier 等），即使在删除列之后添加记录，空间仍然会被占用。为了清除浪费的空间，请使用 ALTER TABLE ... REBUILD。

- Denis Rozhnev

---

在PostgreSQL中，甚至可以像 MySql 一样省略 COLUMN 关键字。 - Artem Novikov

您也可以使用与 MS SQL 和 Postgre SQL 相同的格式来操作 MySQL：ALTER TABLE table_name DROP COLUMN column_name1, column_name2; - jciloa

Answer 3

create table test (a int, b int , c int, d int);
alter table test drop column b, d;

请注意，DROP COLUMN并不会物理上删除数据，在使用固定长度类型（例如int、numeric、float、datetime、uniqueidentifier等）时，即使在删除列之后添加记录也会占用空间。要消除浪费的空间，请执行ALTER TABLE ... REBUILD。

- Remus Rusanu

---

+1 对于表大小和需要重建的注意事项。您能提供文档链接作为参考吗？ - Jesse Webb

在ALTER TABLE中就在那里：http://msdn.microsoft.com/en-us/library/ms190273.aspx - Remus Rusanu

我和我的数据库管理员讨论了重建索引的事情，他说如果表格有聚集索引（我的表格确实有），我可以运行ALTER INDEX PK_IndexName ON TableName REBUILD命令来回收空间。在这里的“重建索引”章节中，有一些相关描述：http://msdn.microsoft.com/en-us/library/ms189858.aspx。 - Jesse Webb

有人能解释一下为什么要这样实现吗？如果我删除了某一列，我希望建立在该列上的索引、数据和所有内容都会被删除。留下浪费空间而没有任何警告似乎是不好的做法，或者我漏掉了什么？ - DanteTheSmith

Answer 4

可能有点晚了，但为了访问此问题的新用户分享一下。 要删除多个列的实际语法是：

alter table tablename drop column col1, drop column col2 , drop column col3 ....

因此，在Mysql 5.0.45中，对于每个列，您需要指定“删除列”。

- MANISH ZOPE

---

这是错误的语法。请使用： ALTER TABLE Test DROP COLUMN c1, c2; - Graeme

@Graeme 语法是正确的，我已经测试过了，它能够正常工作。如果我使用：ALTER TABLE Test DROP COLUMN c1, c2; MySQL会抛出错误...附近有 c2。 - Tom Mwenda

@TomBikiNurse这个问题带有MSDN的参考资料，因此可以推断出该问题涉及到MS SQL Server而不是MySQL。 - Surya Pratap

@SuryaPratap 这个问题在最后一段提到了“我的SQL”。 - Eternal21

@Eternal21 你提到的参考资料是我应该如何编写SQL以在一个语句中删除多个列（如果可能）？ 句子的上下文表明，“my”指代作者，“SQL”指代语言。 - Surya Pratap

这种语法更好，特别是如果您想利用较新的幂等语法来删除列：ALTER TABLE dbo.[TableName] DROP COLUMN IF EXISTS [Column1], COLUMN IF EXISTS [Column2]请参阅Jeff Rush的帖子：https://thedatayouneed.com/drop-exists-columns/ - Nick Allan

Answer 5

微软指定的ALTER语句中删除列部分的语法如下：

 DROP 
 {
     [ CONSTRAINT ] 
     { 
          constraint_name 
          [ WITH 
           ( <drop_clustered_constraint_option> [ ,...n ] ) 
          ] 
      } [ ,...n ]
      | COLUMN 
      {
          column_name 
      } [ ,...n ]
 } [ ,...n ]

请注意，在列名之后和整个drop子句的结尾都出现了[,...n]。这意味着有两种方法可以删除多个列。你可以这样做：

ALTER TABLE TableName
    DROP COLUMN Column1, Column2, Column3

或者这个

ALTER TABLE TableName
    DROP 
        COLUMN Column1,
        COLUMN Column2,
        COLUMN Column3

如果您想将列的删除与约束删除结合起来，则可以使用第二种语法：

ALTER TBALE TableName
    DROP
        CONSTRAINT DF_TableName_Column1,
        COLUMN Column1;

当删除列时，SQL Server不会回收已删除列占用的空间。对于存储在行内的数据类型（例如int），它甚至可能占用修改语句后添加的新行的空间。要解决这个问题，您需要在表上创建聚集索引或重新构建已有的聚集索引。修改表后，可以使用REBUILD命令重建索引。但要注意，在非常大的表上执行此操作可能会很慢。例如：

ALTER TABLE Test
    REBUILD;

- Martin Brown

Answer 6

对于MySQL（版本5.6），您无法使用单个drop语句删除多个列，而是需要使用多个drop语句：

mysql> alter table test2 drop column (c1,c2,c3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(c1,c2,c3)' at line 1
mysql> alter table test2 drop column c1,c2,c3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c2,c3' at line 1
mysql> alter table test2 drop column c1, drop column c2, drop c3;
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

顺便提一下，drop <col_name>是drop column <col_name>的简写，正如您从上面的drop c3所看到的那样。

- CSY

Answer 7

如果只需要删除单个列，可以使用下面的语法：

ALTER TABLE 表名 DROP COLUMN 列名;

如果需要删除多个列，使用 DROP COLUMN 语法无效，可以使用下面的语法：

ALTER TABLE 表名 DROP (列名1, 列名2, 列名3......);

- kk250040

Answer 8

通用：

ALTER TABLE table_name 
DROP COLUMN column1,column2,column3;

E.g:

ALTER TABLE Student 
DROP COLUMN Name, Number, City;

- Chiragkumar Thakar

Answer 9

"For postgis is" 的意思不明确，需要更多的上下文来理解。

翻译:

alter table table01 drop columns col1, drop col2

修改表 table01，删除列 col1 和 col2。

- Joselin Ceron

---

尝试使用SQL，但它不起作用。在日志中得到以下信息：12:58:04 ALTER TABLE ORDERS DROP COLUMNS user_name, user_contact_number, user_address 错误代码：1064。您的SQL语法有误，请检查与您的MySQL服务器版本相对应的手册以获取正确的语法使用方法，在第2行附近出现了“user_name，user_contact_number，user_address”的错误。0.0060秒。 - Akash Verma

Answer 10

alter table tablename drop (column1, column2, column3......);

- Neelima

---

这是错误的语法。请使用：ALTER TABLE Test DROP COLUMN c1，c2; - Graeme