如何使用ALTER TABLE直接保留指定列而非删除不需要的列

标准SQL的ALTER TABLE语法本身不支持直接指定要保留的列来删除其余列，仅支持显式罗列要删除的列，你可以通过以下两类方案实现需求：

通用兼容方案（适配所有主流关系型数据库） #

通过建临时表迁移保留数据、再替换原表的方式实现，无需罗列要删除的列，仅需要指定你要保留的列即可，适配MySQL、PostgreSQL、Oracle、SQL Server等绝大多数数据库：

-- 1. 创建新表，仅保留你需要的列，同时同步原表所有数据
CREATE TABLE new_tablename AS SELECT 需保留的列1, 需保留的列2, 需保留的列3 FROM tablename;

-- 2. 手动补全新表的主键、索引、约束、自增属性等原表配置（该步骤不可省略，否则会丢失原表约束）

-- 3. 替换原表（操作前务必确认数据已完整同步）
DROP TABLE tablename;
ALTER TABLE new_tablename RENAME TO tablename;

注意：该方案操作过程中需要锁表暂停业务写入，避免新老表数据不一致，适合数据量不大、或者可接受短暂停写的业务场景，操作前务必备份全表数据

部分数据库特有原生语法 #

少数数仓类数据库扩展了ALTER TABLE的能力，支持直接通过EXCEPT关键字指定保留列，删除其余所有列，无需罗列待删除列：

-- Snowflake、BigQuery、Databricks SQL支持该语法
ALTER TABLE tablename DROP COLUMN EXCEPT (需保留的列1, 需保留的列2, 需保留的列3);

注意：该语法是非标准SQL扩展，MySQL、PostgreSQL、Oracle、SQL Server等主流事务型数据库暂不支持

内容的提问来源于stack exchange，提问作者Srikanth_Alokam