PAYATHON 2026

如何解决带更新审批的 SQL 数据库依赖循环

支付小周

结论

这些表之间并没有真正的外键循环。documents 和 payment_methods 存放基础数据，transactions 引用这两张表，transaction_update_requests 再引用原交易和准备更新的新值。依赖关系如下：

documents ───────────────┐
                         ├──> transactions ───> transaction_update_requests
payment_methods ─────────┘              │
                                        │
documents ──────────────────────────────┤
payment_methods ────────────────────────┘

只要 documents、payment_methods 和 transactions 不反向引用更新申请表，这个结构就是有向无环的。最直接的设计是把 updated_transactions 改成一张明确的更新申请表。审批通过后，在同一个数据库事务中修改原交易并更新申请状态。

推荐设计：独立的更新申请表

建议将表名改为 transaction_update_requests。这张表保存待审批的更新申请，而不是一条已经更新的交易。

CREATE TABLE documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE payment_methods (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    document_id BIGINT UNSIGNED NOT NULL,
    version BIGINT UNSIGNED NOT NULL DEFAULT 1,

    PRIMARY KEY (id),

    CONSTRAINT fk_transactions_payment_method
        FOREIGN KEY (payment_method_id)
        REFERENCES payment_methods (id),

    CONSTRAINT fk_transactions_document
        FOREIGN KEY (document_id)
        REFERENCES documents (id)
);

CREATE TABLE transaction_update_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_id BIGINT UNSIGNED NOT NULL,
    proposed_payment_method_id BIGINT UNSIGNED NOT NULL,
    proposed_document_id BIGINT UNSIGNED NOT NULL,
    base_version BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    requested_by BIGINT UNSIGNED NOT NULL,
    reviewed_by BIGINT UNSIGNED NULL,
    requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL,

    PRIMARY KEY (id),

    CONSTRAINT fk_update_request_transaction
        FOREIGN KEY (transaction_id)
        REFERENCES transactions (id),

    CONSTRAINT fk_update_request_payment_method
        FOREIGN KEY (proposed_payment_method_id)
        REFERENCES payment_methods (id),

    CONSTRAINT fk_update_request_document
        FOREIGN KEY (proposed_document_id)
        REFERENCES documents (id),

    CONSTRAINT chk_update_request_status
        CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled'))
);

建表顺序是：

  1. documents
  2. payment_methods
  3. transactions
  4. transaction_update_requests

在这个结构中，transactions 不需要保存 updated_state，也不需要指向申请表。申请是否存在以及是否通过，都由申请表中的 status 表示。

如果需要快速查询某条交易是否有待审批申请，给申请表添加索引即可，不必在 transactions 中增加反向外键：

CREATE INDEX idx_update_request_transaction_status
    ON transaction_update_requests (transaction_id, status);

审批通过时如何应用更新

审批需要在同一个数据库事务中完成：

  1. 锁定更新申请。
  2. 确认申请状态仍是 pending。
  3. 锁定原交易。
  4. 检查申请提交后，交易是否被其他操作修改过。
  5. 更新原交易。
  6. 将申请标记为 approved。
  7. 提交事务。

对应的 SQL 伪代码可以写成：

START TRANSACTION;

SELECT
    transaction_id,
    proposed_payment_method_id,
    proposed_document_id,
    base_version,
    status
FROM transaction_update_requests
WHERE id = :request_id
FOR UPDATE;

SELECT version
FROM transactions
WHERE id = :transaction_id
FOR UPDATE;

UPDATE transactions
SET
    payment_method_id = :proposed_payment_method_id,
    document_id = :proposed_document_id,
    version = version + 1
WHERE id = :transaction_id
  AND version = :base_version;

UPDATE transaction_update_requests
SET
    status = 'approved',
    reviewed_by = :admin_id,
    reviewed_at = CURRENT_TIMESTAMP
WHERE id = :request_id
  AND status = 'pending';

COMMIT;

应用层必须检查两条 UPDATE 语句的影响行数。如果原交易的 version 已经改变，说明这份申请是基于旧数据创建的，不能直接覆盖当前值。此时应执行 ROLLBACK，再由管理员重新审核或让用户重新提交。

上面的示例 SQL 本身不能保证所有异常都会自动触发回滚。实际代码发现影响行数不等于 1 时，必须显式回滚事务。

只允许一个待审批申请

如果同一条交易只能有一个 pending 申请，应尽量在数据库层保证这个约束。

各数据库对条件唯一索引的支持不同。PostgreSQL 可以直接创建部分唯一索引：

CREATE UNIQUE INDEX uq_one_pending_request_per_transaction
    ON transaction_update_requests (transaction_id)
    WHERE status = 'pending';

MySQL 不支持这种形式的部分索引。如果所用版本支持为生成列创建索引，可以使用生成列配合唯一索引：

ALTER TABLE transaction_update_requests
ADD COLUMN pending_transaction_id BIGINT UNSIGNED
GENERATED ALWAYS AS (
    CASE
        WHEN status = 'pending' THEN transaction_id
        ELSE NULL
    END
) STORED;

CREATE UNIQUE INDEX uq_one_pending_request_per_transaction
    ON transaction_update_requests (pending_transaction_id);

唯一索引通常允许存在多个 NULL，因此非 pending 申请不会相互冲突，同一条交易则只能有一个待审批申请。

方案二：使用交易版本表

如果系统除了审批，还要完整保存每次修改前后的历史记录，可以将交易设计为不可变版本。

CREATE TABLE transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    current_version_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (id)
);

CREATE TABLE transaction_versions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_id BIGINT UNSIGNED NOT NULL,
    version_number BIGINT UNSIGNED NOT NULL,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    document_id BIGINT UNSIGNED NOT NULL,
    approval_status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_transaction_version (
        transaction_id,
        version_number
    ),

    CONSTRAINT fk_version_transaction
        FOREIGN KEY (transaction_id)
        REFERENCES transactions (id),

    CONSTRAINT fk_version_payment_method
        FOREIGN KEY (payment_method_id)
        REFERENCES payment_methods (id),

    CONSTRAINT fk_version_document
        FOREIGN KEY (document_id)
        REFERENCES documents (id),

    CONSTRAINT chk_version_approval_status
        CHECK (
            approval_status IN (
                'pending',
                'approved',
                'rejected'
            )
        )
);

用户提交更新时，系统创建一个 pending 版本。管理员批准后，将该版本改为 approved，再让 transactions.current_version_id 指向它。

这个设计会在建表时产生相互引用：版本引用交易，交易也引用当前版本。可以先创建两张表，再通过 ALTER TABLE 添加 current_version_id 的外键：

ALTER TABLE transactions
ADD CONSTRAINT fk_transaction_current_version
FOREIGN KEY (current_version_id)
REFERENCES transaction_versions (id);

但普通外键无法保证 current_version_id 指向的版本属于同一个 transaction_id。通常还要通过复合键、触发器或应用层检查来实现这项约束，所以它比独立申请表更复杂。

这种方案适合审计要求较高，或者需要回滚历史版本、展示完整变更轨迹的系统。如果业务只是提交申请、审批并覆盖原记录，通常没有必要引入版本模型。

方案三：只保存发生变化的字段

如果交易包含很多字段，而每份申请只修改少数字段，可以允许申请表中的拟议值为 NULL，并将 NULL 解释为“不修改该字段”：

CREATE TABLE transaction_update_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_id BIGINT UNSIGNED NOT NULL,
    proposed_payment_method_id BIGINT UNSIGNED NULL,
    proposed_document_id BIGINT UNSIGNED NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',

    PRIMARY KEY (id),

    FOREIGN KEY (transaction_id)
        REFERENCES transactions (id),

    FOREIGN KEY (proposed_payment_method_id)
        REFERENCES payment_methods (id),

    FOREIGN KEY (proposed_document_id)
        REFERENCES documents (id)
);

审批时按照业务规则合并数据：

UPDATE transactions
SET
    payment_method_id =
        COALESCE(:proposed_payment_method_id, payment_method_id),
    document_id =
        COALESCE(:proposed_document_id, document_id)
WHERE id = :transaction_id;

这种设计要求业务字段不能通过申请被修改为 NULL。如果“没有提交修改”和“申请将字段改为 NULL”都可能出现，就要增加单独的布尔标记，例如 change_document，不能只用 NULL 区分。

不建议把拟议内容全部存成 JSON

还有一种做法是建立通用审批表，将字段变化存为 JSON：

CREATE TABLE change_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    proposed_changes JSON NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    PRIMARY KEY (id)
);

这种结构便于扩展，但数据库无法通过普通外键验证 JSON 中的 document_id 和 payment_method_id 是否存在，字段类型、查询效率和迁移也更难控制。

只有当系统需要为大量不同实体提供统一审批引擎，而且应用层具备完善的校验和审计机制时，这种方案才比较合适。对于单一的 transactions 更新审批，强类型列通常更可靠。

外键和删除策略

更新申请引用原交易、文档和支付方式，因此必须明确删除规则。通常不建议对这些关系使用 ON DELETE CASCADE，否则删除基础数据时可能连带删除审批记录，导致审计信息丢失。

可以保留默认的 RESTRICT，也可以对基础数据使用软删除：

ALTER TABLE documents
ADD COLUMN deleted_at TIMESTAMP NULL;

ALTER TABLE payment_methods
ADD COLUMN deleted_at TIMESTAMP NULL;

即使某种支付方式不再允许用于新交易，也可以保留原记录，让历史交易和审批申请中的外键继续有效。

如果业务允许删除待审批申请引用的拟议对象，也可以使用 ON DELETE SET NULL。此时对应列必须允许 NULL，审批界面还需要将申请标记为无效，不能继续批准。

原 SQL 还需要修正的细节

原定义的主键使用 BIGINT UNSIGNED，外键列却使用了 BIGINT。在 MySQL 中，外键两端的数据类型和有符号属性应保持一致，因此相关列都应使用 BIGINT UNSIGNED。

命名方面也有几处需要调整：

  • docment_id 应统一为 document_id。
  • payment_methodes 应统一为 payment_methods。
  • id_transaction 更适合改为 transaction_id。
  • updated_state 的含义不明确。如果它表示审批状态，应放在申请表中；如果表示并发版本，应改名为 version。
  • 状态列不宜使用含义不清的 BIGINT，可以改用受约束的字符串、枚举或关联状态表。

最简单且便于维护的方案，是只保留一条正式的 transactions 记录，把所有未审批内容放入 transaction_update_requests，再通过事务、行锁和版本号完成审批。这样既能保留现有业务流程，也不需要删除外键或复制基础数据来处理并不存在的循环依赖。

备注：内容仅供参考。