Postgres 与 MySQL 执行 DDL 事务的对比

Bytebase 2025-06-04

117

原文地址：https://www.bytebase.com/blog/postgres-vs-mysql-ddl-transaction

数据库 schema 变更作为关键操作，需要慎重规划执行；因此，执行变更是否安全可靠，是选择数据库管理系统的关键考虑因素。接下来，我们将比较 PostgreSQL 17 和 MySQL 8 对于 DDL（Data Definition Language）事务的处理，且重点关注二者的原子性和回滚能力。

什么是 DDL 事务‍‍

深入比较之前，让我们先明确 DDL 事务的含义。DDL 语句可以组合在一起，作为一个单元提交，或者在出现问题时完全回滚。

有两个重要的概念需要区分：

1. 事务性 DDL：能够在多语句事务块中包含 DDL 语句，并可以选择一起提交或回滚所有语句。

2. 原子性 DDL：保证单个 DDL 语句是原子的（全有或全无），但不一定支持将其包含在多语句事务中。

PostgreSQL 17：真正的事务性 DDL

在 PostgreSQL 17 中，DDL 操作是完全事务性的，这意味着：

DDL 语句可以与 DML 语句一起包含在事务块中
多个 DDL 操作可以作为单个单元提交或回滚
保存点可以在包含 DDL 语句的事务中使用
如果事务失败，所有 DDL 更改都会回滚，数据库保持在原始状态

此规则只有少数例外：对数据库和表空间本身的操作（如 CREATE DATABASE 或 DROP TABLESPACE）无法回滚。除这些之外，所有其他目录操作都是可逆的。

MySQL 8：原子性 DDL

在 MySQL 8 之前，MySQL 中的 DDL 操作根本不是原子的。如果 DDL 语句在执行过程中失败（例如添加了多个列或索引的 ALTER TABLE 操作），数据库可能会处于不一致的、部分修改的状态。

MySQL 8 引入了一个称为原子性 DDL 的功能，这相比以前的版本是一个重大改进，但与 PostgreSQL 的方法在根本上有所不同。

在 MySQL 8 中，DDL 在语句级别是原子的，这意味着：

单个 DDL 语句要么完全完成，要么完全回滚
DDL 语句在执行前隐式提交任何活动事务
DDL 语句不能成为可以回滚的多语句事务的一部分
崩溃恢复确保语句级别的原子性

MySQL 的原子性 DDL 通过 InnoDB 中的一个特殊内部 DDL_LOG 表实现，该表跟踪 DDL 执行期间文件和结构的创建。此日志在提交/回滚时用于正确清理，确保崩溃后不会残留孤立文件或索引树。

需要注意的是，原子性 DDL 仅在 MySQL 8 InnoDB 存储引擎中受支持。对于使用其他存储引擎的表，仍可能发生部分更新。

示例

为了更好地理解这些差异，让我们运行一些 DDL，来演示 DDL 事务在两个系统中的行为。

PostgreSQL 17

我们的第一个示例将所有 DDL 操作包装在一个事务中。当我们执行 ROLLBACK 命令时，所有表和索引都会被彻底删除。

-- 开始一个事务块
BEGIN;
-- 创建一个简单的表
CREATETABLEusers (
    id SERIALPRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 添加一个索引
CREATEINDEXidx_usernameON users(username);
-- 糟糕！我们犯了一个错误，想要回滚所有更改
ROLLBACK;
-- 验证表未被创建
SELECT table_name FROMinformation_schema.tables
WHERE table_schema ='public'AND table_name ='users';
-- 应该返回无行，因为事务被回滚了

第二个示例演示了 PostgreSQL 如何用 SAVEPOINT 进行部分回滚，为开发者提供 schema 变更的细粒度控制。

BEGIN;
-- 创建一个表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 创建一个保存点
SAVEPOINT after_users_table;
-- 修改表以添加列
ALTER TABLE users ADD COLUMN email VARCHAR(100);
-- 糟糕！我们只想回滚列的添加
ROLLBACK TO after_users_table;
-- 改为添加不同的列
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE;
-- 提交事务
COMMIT;

MySQL 8

在这个 MySQL 示例中，尽管将 DDL 语句包装在事务块中，ROLLBACK 命令也并不会撤销表的创建。这是因为每个 DDL 语句在执行前都会隐式提交事务，使得无法将多个 DDL 语句作为一个单元回滚。

-- 尝试使用事务块（注意：这对 DDL 不会按预期工作）
START TRANSACTION;
-- 创建一个简单的表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 添加一个索引
CREATE INDEX idx_username ON users(username);
-- 尝试回滚所有更改（对 DDL 语句不起作用）
ROLLBACK;
-- 验证尽管有 ROLLBACK，表仍被创建
SHOW TABLES;
-- 将显示 'users' 表

不过，MySQL 8 确实提供语句级别的原子性：

-- 这将要么创建所有用户，要么都不创建
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1',
           'user2'@'localhost' IDENTIFIED BY 'password2';
-- 这将要么删除所有表，要么都不删除
DROP TABLE IF EXISTS table1, table2, table3;

结论

比对一下 PostgreSQL 17 和 MySQL 8 在处理 DDL 事务时的主要差异：

功能	PostgreSQL 17	MySQL 8
DDL 事务支持	完全事务性 DDL	原子性 DDL（仅语句级别）
多语句 DDL 事务	是	否
事务中的 DDL 回滚	是	否（隐式提交）
单个 DDL 语句的原子性	是	是（仅 InnoDB）
崩溃恢复	完整事务恢复	语句级别恢复
支持 DDL 的保存点	支持	不支持
例外情况	数据库和表空间操作	数据库目录删除不是原子操作的一部分
实现机制	预写日志（WAL）	InnoDB 中的 DDL_LOG

PostgreSQL 的事务性 DDL 保障了复杂 shcema 迁移更高级别的安全性。通过将多个相关更改包装在事务中，管理员可以确保数据库保持一致状态（即使迁移过程中出现问题）。相比之下，MySQL 8 在语句级别引入原子性 DDL，意味着每个单独的 DDL 语句都作为全有或全无的操作执行。虽然这相比早期版本的 MySQL 是一个显著改进，但它仍然不支持多语句 DDL 事务。