第 9.2 课 · 阅读时间：约 9 分钟

SQL ALTER TABLE 语句在不重建表的情况下修改已有表的结构：添加、修改、重命名和删除列，以及添加或删除约束。在本课中，你将学习每种修改在 MySQL 和 PostgreSQL 中的语法，了解为什么已有数据可能导致修改失败，以及如何按名称查找并删除约束。

SQL ALTER TABLE：修改已有的表

在上一课中，你用 CREATE TABLE 创建了表。需求总会变化：表需要新增一列、更长的文本字段，或者补上忘记的约束。删除表再重新创建会丢失其中的数据，因此要用 ALTER TABLE 就地修改表结构。

SQLtest 上的示例数据库是只读的，所以在那里这些语句会返回权限错误。示例使用 Sakila 的表结构，并已在 MySQL 8.0 和 PostgreSQL 17 上验证过。

ALTER TABLE 语法

ALTER TABLE table_name
    action [, action ...];

操作（action）说明要修改什么。一条 ALTER TABLE 可以包含多个用逗号分隔的操作。最常用的操作如下：

添加列

ALTER TABLE customer
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

结果：每位已有客户的 loyalty_points 都为 0；新插入的行如果 INSERT 没有指定值，也会得到 0。

新列添加在表的末尾。MySQL 可以用 AFTER column_name 或 FIRST 把它放到其他位置；PostgreSQL 总是把列加在末尾。

没有默认值的 NOT NULL 列

如果表中已经有数据，NOT NULL 列需要为这些行提供一个值：

ALTER TABLE customer
ADD COLUMN phone VARCHAR(20) NOT NULL;

PostgreSQL 中的结果：报错，column "phone" contains null values。MySQL 会执行该语句，并用该类型的隐式默认值填充已有行：这里是空字符串，数字类型则是 0。

因此，给有数据的表添加必填列时，务必指定 DEFAULT；或者先把列添加为允许 NULL，用 UPDATE 填充数据，然后再设为 NOT NULL。

修改列

要把 email 列改长：

-- MySQL
ALTER TABLE customer
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- PostgreSQL
ALTER TABLE customer
ALTER COLUMN email TYPE VARCHAR(100);

两种数据库处理列定义其余部分的方式不同：

• MySQL 的 MODIFY 会替换整个列定义。没有重复写出的属性都会丢失：ALTER TABLE customer MODIFY active SMALLINT; 会把一个 NOT NULL DEFAULT 1 的列变成允许 NULL 且没有默认值的列。请始终写出完整定义：MODIFY active SMALLINT NOT NULL DEFAULT 1。
• PostgreSQL 的 ALTER COLUMN 一次只修改一个属性，其他属性保持不变：

ALTER TABLE customer
    ALTER COLUMN active TYPE INT,
    ALTER COLUMN active SET DEFAULT 1,
    ALTER COLUMN email SET NOT NULL;

MySQL 还提供 CHANGE COLUMN old_name new_name definition，可以一步完成重命名和重新定义列。

当数据不符合新定义时

数据库会按照新定义检查已有的行：

• 把 VARCHAR(50) 缩短为 VARCHAR(10) 时，如果有值更长就会失败：MySQL 中是 “Data truncated for column 'email'”，PostgreSQL 中是 “value too long for type character varying(10)”。
• 如果列中有 NULL，添加 NOT NULL 会失败：MySQL 中是 “Invalid use of NULL value”，PostgreSQL 中是 “column contains null values”。

先修正数据，例如 UPDATE customer SET email = '' WHERE email IS NULL;，再重新执行 ALTER TABLE。

重命名和删除

ALTER TABLE customer RENAME COLUMN email TO email_address;

ALTER TABLE customer DROP COLUMN loyalty_points;

ALTER TABLE customer RENAME TO client;

结果：列获得新名称，loyalty_points 列连同其数据一起被删除，表也被重命名。

RENAME COLUMN 适用于 MySQL 8.0、MariaDB 10.5 和 PostgreSQL；SQL Server 使用存储过程 sp_rename。重命名不会更新你的查询、视图和应用代码：所有使用旧名称的地方都会出错。

在 MySQL 中，DROP COLUMN 无法用 ROLLBACK 撤销，因为 DDL 会隐式提交（见第 8.4 课）。请先备份可能还需要的数据。

添加约束

CREATE TABLE 中的约束也可以以后再添加。用 CONSTRAINT name 为每个约束命名，以便日后查找和删除：

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount CHECK (amount >= 0);

ALTER TABLE payment
ADD CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

主键也用同样的方式添加：ALTER TABLE table_name ADD PRIMARY KEY (column_name);。

已有数据可能阻止添加约束

新约束会作用于表中已有的行。只要有一行违反约束，整条语句就会失败：

添加约束之前，先用 SELECT 找出有问题的行。例如，引用了不存在客户的付款记录：

SELECT p.payment_id, p.customer_id
FROM payment AS p
LEFT JOIN customer AS c ON c.customer_id = p.customer_id
WHERE c.customer_id IS NULL;

结果：会阻止添加外键的“孤儿”行。修正或删除它们，然后再添加约束。

查找和删除约束

要删除约束，需要知道它的名称。如果当初没有命名，数据库会自动生成一个，例如 MySQL 中的 payment_ibfk_1 或 PostgreSQL 中的 payment_customer_id_fkey。可以从 information_schema.table_constraints 视图中查看：

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'payment'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Sakila 上的结果：fk_payment_customer、fk_payment_rental 和 fk_payment_staff（FOREIGN KEY）以及 PRIMARY（PRIMARY KEY）。在 PostgreSQL 中，请用 table_schema = 'public' 代替 DATABASE() 进行过滤。

然后删除它：

-- PostgreSQL, SQL Server and MySQL 8.0.19+
ALTER TABLE payment DROP CONSTRAINT fk_payment_customer;

-- MySQL: type-specific forms
ALTER TABLE payment DROP FOREIGN KEY fk_payment_customer;
ALTER TABLE payment DROP CHECK chk_payment_amount;
ALTER TABLE customer DROP INDEX uq_customer_email;

在 MySQL 中，UNIQUE 约束就是一个索引，所以要用 DROP INDEX 删除。

大表上的 ALTER TABLE

在有数百万行的表上，有些修改会重写整张表，并在执行期间阻塞写入。在较新的版本中，添加允许 NULL 的列或带常量默认值的列速度很快：MySQL 8.0 可以瞬间完成（ALGORITHM=INSTANT），PostgreSQL 11 及更高版本也是如此。修改列类型、给大表添加外键，或在旧版本中删除列，都可能耗时很长。

在生产数据库上，请先在副本上测试表结构变更，并在负载较低时执行。

本课要点：

• ALTER TABLE 就地修改已有的表：列、约束以及表名。
• 给有数据的表添加必填列时使用 DEFAULT，或者先填充数据再设置 NOT NULL。
• 在 MySQL 中，MODIFY 会替换整个列定义；要重复写出 NOT NULL 和 DEFAULT，否则它们会丢失。
• 已有行必须符合新的类型、NOT NULL 或约束，否则语句会失败。
• 为约束命名；没有命名的约束可以在 information_schema.table_constraints 中查找，并用 DROP CONSTRAINT 删除。
• 在 MySQL 中，ALTER TABLE 会隐式提交，无法回滚。

常见问题

如何在 SQL 中给已有的表添加列？

使用 ALTER TABLE table_name ADD COLUMN column_name data_type;，例如 ALTER TABLE customer ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;。已有的行会得到默认值；如果该列没有默认值，则为 NULL。

如何在 SQL 中修改列的数据类型？

在 MySQL 中使用 ALTER TABLE customer MODIFY COLUMN email VARCHAR(100) NOT NULL;，并写出完整的列定义。在 PostgreSQL 中使用 ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(100);。如果已有的值不符合新类型，修改会失败。

如何给已有的表添加外键？

ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);。payment 中已有的每个 customer_id 都必须在 customer 中存在，否则语句会失败。

不知道约束名称时如何删除约束？

先在 information_schema.table_constraints 中查找该表的约束，然后执行 ALTER TABLE table_name DROP CONSTRAINT constraint_name;。在 MySQL 中，外键也可以用 DROP FOREIGN KEY 删除，唯一约束可以用 DROP INDEX 删除。

面试问题

MySQL 的 MODIFY COLUMN 和 PostgreSQL 的 ALTER COLUMN 有什么区别？

MODIFY COLUMN 会替换完整的列定义，因此没有重复写出的属性（如 NOT NULL 或 DEFAULT）会被删除。PostgreSQL 的 ALTER COLUMN 只修改一个属性，例如类型或默认值，其余属性保持不变。

为什么给已有的表添加 NOT NULL 或 UNIQUE 可能会失败？

约束会对表中已有的行进行检查。如果有 NULL 值，NOT NULL 会失败；有重复值时 UNIQUE 会失败；如果有值在被引用的表中不存在，外键会失败。请先清理数据。

如何给生产环境中的大表添加必填列？

用常量 DEFAULT 添加，这在 MySQL 8.0 和 PostgreSQL 11+ 中只是一次快速的元数据修改；或者先添加为允许 NULL 的列，用 UPDATE 分批填充，再设置 NOT NULL。先在副本上测试，并在低负载时执行。