sql-alter-command

SQL ALTER TABLE 命令

ALTER TABLE 简介

ALTER TABLE 语句是一种数据定义语言（DDL）命令，用于修改现有表的结构。您可以使用它来添加、删除或修改列，以及添加或删除诸如主键、外键和唯一约束等约束。

警告： 修改大型生产表的结构可能是一个缓慢且资源密集型的操作。它可能会锁定表，阻止读写，导致应用程序停机。务必首先在开发环境中测试模式更改，并为潜在的停机时间做好计划。

添加和删除列

ADD COLUMN（添加列）

这会将新列添加到表中。其语法在各数据库中相当标准。

-- 基本语法
ALTER TABLE table_name ADD COLUMN new_column_name data_type [constraints];

-- 示例：向 Customers 表添加 contact_email 列。
ALTER TABLE Customers ADD COLUMN contact_email VARCHAR(255);

-- 示例：添加一个带默认值的列，以避免现有行中出现 NULL 值。
ALTER TABLE Customers ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

DROP COLUMN（删除列）

这将永久删除一个列及其所有数据。此操作不可逆。

-- 基本语法
ALTER TABLE table_name DROP COLUMN column_name;

-- 示例：移除现在未使用的 'sex' 列。
ALTER TABLE Customers DROP COLUMN sex;

修改列（数据类型、重命名）

修改列的语法在不同数据库系统之间差异很大。

更改数据类型

这会更改现有列的数据类型。请注意，如果新类型比旧类型更具限制性（例如，将 VARCHAR(100) 更改为 VARCHAR(50)），这可能导致数据截断。

-- PostgreSQL 和 SQL Server
ALTER TABLE Customers ALTER COLUMN address TYPE TEXT; -- PostgreSQL
ALTER TABLE Customers ALTER COLUMN address NVARCHAR(MAX); -- SQL Server

-- MySQL 和 Oracle
ALTER TABLE Customers MODIFY COLUMN address TEXT;

重命名列

这会更改列的名称。

-- 标准 SQL / PostgreSQL
ALTER TABLE Customers RENAME COLUMN name TO full_name;

-- MySQL
ALTER TABLE Customers RENAME COLUMN name TO full_name; -- 现代 MySQL
-- 或者更旧、更通用的 CHANGE COLUMN 语法：
ALTER TABLE Customers CHANGE COLUMN name full_name VARCHAR(100) NOT NULL;

-- SQL Server（使用存储过程）
EXEC sp_rename 'Customers.name', 'full_name', 'COLUMN';

使用约束（添加、删除）

您可以在表创建后添加或删除 PRIMARY KEY（主键）、UNIQUE（唯一）和 FOREIGN KEY（外键）等约束。最佳实践是明确命名您的约束。

ADD CONSTRAINT（添加约束）

-- 示例：向 email 列添加 UNIQUE 约束。
ALTER TABLE Customers
ADD CONSTRAINT uq_customers_email UNIQUE(contact_email);

-- 示例：添加 FOREIGN KEY 约束。
ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id) REFERENCES Customers(id);

DROP CONSTRAINT（删除约束）

删除约束需要知道其名称。这就是命名约束如此重要的原因。

-- 标准 SQL / PostgreSQL / SQL Server
ALTER TABLE Customers DROP CONSTRAINT uq_customers_email;

-- MySQL
-- 对于 UNIQUE 约束：
ALTER TABLE Customers DROP INDEX uq_customers_email;
-- 对于 FOREIGN KEY 约束：
ALTER TABLE Orders DROP FOREIGN KEY fk_orders_customer_id;

安全性、性能和最佳实践

修改表，尤其是大型表，是高风险操作。请遵循以下准则：

• 使用事务： 在支持事务性 DDL 的数据库（如 PostgreSQL）中，将 ALTER 语句封装在 BEGIN...COMMIT 块中。如果出现问题，您可以 ROLLBACK（回滚）更改。
• 先备份： 在对生产数据库进行重大模式更改之前，务必确保您有最新的、可恢复的备份。
• 安排停机时间： 计划在用户流量较低的维护时段运行这些更改，以最大限度地减少影响。
• 使用在线模式更改工具： 对于 MySQL 等系统上的超大型表，标准的 ALTER TABLE 操作可能会锁定表数小时。gh-ost 或 pt-online-schema-change 等工具可以以最小的锁定执行迁移，从而使应用程序保持在线状态。
• 测试，测试，再测试： 在镜像生产环境的暂存服务器上运行完全相同的迁移脚本，以便在生产环境中运行之前识别任何潜在问题或性能问题。