Mysql Workbench 中不同 payment 类型的表结构设计

2026-10-24支付阿杰

结论

不建议在 Payment 表中同时保存多个外键，分别指向 CreditCard、DebitCard、CashOnDelivery 等类型表，也不建议在查询时通过 if/else 判断哪个外键有效。

更合适的设计是：

Bill：保存账单或订单的应付信息。
Payment：保存所有支付方式共有的信息，如金额、支付方式、状态和完成时间。
支付方式专属表：只保存某种支付方式特有的数据，并与 Payment 建立一对一关系。
货到付款是一种支付方式，不是一张 Home 表。未收款、已收款等阶段应由支付状态表示。

业务代码可以根据 payment_method 调用不同的支付流程，但不能只靠这些条件分支保证数据库完整性。

为什么多个可空外键不理想

假设 Payment 同时包含：

credit_card_payment_id
debit_card_payment_id
cash_on_delivery_id
payment_type

这种结构主要有以下问题：

容易产生矛盾数据

payment_type 可能是 CREDIT_CARD，实际填写的却是 debit_card_payment_id，也可能同时填写两个外键。
大量字段长期为 NULL

每条支付记录通常只使用其中一个外键。每增加一种支付方式，Payment 表还要继续增加列。
查询逻辑复杂

查询时需要根据类型执行不同的 JOIN 或 if/else。这样的代码难以维护，而且无法替代外键、唯一约束等数据库约束。
支付状态和支付方式容易混淆

“货到付款”是支付方式，“等待收款”和”已支付”才是状态，两者需要分别建模。

推荐的基础结构

如果系统中的信用卡和借记卡只用于标识支付来源，二者的专属字段也基本相同，可以统一使用 CARD，再通过 card_type 区分 CREDIT 和 DEBIT。

CREATE TABLE bill (
    bill_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    total_amount DECIMAL(19, 4) NOT NULL,
    currency CHAR(3) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (total_amount >= 0)
);

CREATE TABLE payment (
    payment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_id BIGINT UNSIGNED NOT NULL,
    payment_method VARCHAR(32) NOT NULL,
    payment_status VARCHAR(32) NOT NULL,
    amount DECIMAL(19, 4) NOT NULL,
    currency CHAR(3) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME NULL,
    provider_transaction_id VARCHAR(128) NULL,

    CONSTRAINT fk_payment_bill
        FOREIGN KEY (bill_id) REFERENCES bill (bill_id),

    CONSTRAINT chk_payment_method
        CHECK (payment_method IN ('CARD', 'CASH_ON_DELIVERY')),

    CONSTRAINT chk_payment_status
        CHECK (
            payment_status IN (
                'PENDING',
                'AUTHORIZED',
                'PAID',
                'FAILED',
                'CANCELLED',
                'REFUNDED'
            )
        ),

    CONSTRAINT chk_payment_amount
        CHECK (amount > 0),

    UNIQUE KEY uk_payment_provider_transaction (
        provider_transaction_id
    ),

    KEY idx_payment_bill (bill_id),
    KEY idx_payment_status (payment_status)
);

paid_at 应允许为 NULL。支付尚未完成时，不应填写虚假的完成时间。

卡支付的专属信息可以放入一对一扩展表：

CREATE TABLE card_payment (
    payment_id BIGINT UNSIGNED PRIMARY KEY,
    card_type VARCHAR(16) NOT NULL,
    provider VARCHAR(64) NULL,
    card_brand VARCHAR(32) NULL,
    card_last4 CHAR(4) NULL,
    card_token VARCHAR(255) NULL,
    authorization_code VARCHAR(128) NULL,

    CONSTRAINT fk_card_payment
        FOREIGN KEY (payment_id)
        REFERENCES payment (payment_id)
        ON DELETE CASCADE,

    CONSTRAINT chk_card_type
        CHECK (card_type IN ('CREDIT', 'DEBIT'))
);

货到付款的专属信息也可以单独保存：

CREATE TABLE cash_on_delivery_payment (
    payment_id BIGINT UNSIGNED PRIMARY KEY,
    collection_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    collected_at DATETIME NULL,
    collected_by BIGINT UNSIGNED NULL,
    collection_note VARCHAR(500) NULL,

    CONSTRAINT fk_cod_payment
        FOREIGN KEY (payment_id)
        REFERENCES payment (payment_id)
        ON DELETE CASCADE,

    CONSTRAINT chk_cod_collection_status
        CHECK (
            collection_status IN (
                'PENDING',
                'COLLECTED',
                'FAILED',
                'RETURNED'
            )
        )
);

只有货到付款确实存在额外业务字段时，才需要这张扩展表。如果它只需要记录”是否已支付”，直接使用 payment.payment_status 和 paid_at 即可。

货到付款的状态更新

创建货到付款记录时，支付还没有完成：

START TRANSACTION;

INSERT INTO payment (
    bill_id,
    payment_method,
    payment_status,
    amount,
    currency
)
VALUES (
    1001,
    'CASH_ON_DELIVERY',
    'PENDING',
    299.00,
    'CNY'
);

SET @payment_id = LAST_INSERT_ID();

INSERT INTO cash_on_delivery_payment (
    payment_id,
    collection_status
)
VALUES (
    @payment_id,
    'PENDING'
);

COMMIT;

快递交付并收到现金后，应在同一个事务中更新相关状态：

START TRANSACTION;

UPDATE payment
SET payment_status = 'PAID',
    paid_at = CURRENT_TIMESTAMP
WHERE payment_id = 5001
  AND payment_method = 'CASH_ON_DELIVERY'
  AND payment_status = 'PENDING';

UPDATE cash_on_delivery_payment
SET collection_status = 'COLLECTED',
    collected_at = CURRENT_TIMESTAMP,
    collected_by = 42
WHERE payment_id = 5001
  AND collection_status = 'PENDING';

COMMIT;

实际业务中还要检查两条 UPDATE 的受影响行数。如果状态已被其他请求修改，应回滚事务或按照幂等规则处理，避免重复收款。

查询不同支付方式

可以先查询公共支付信息，再按需关联专属表：

SELECT
    p.payment_id,
    p.bill_id,
    p.payment_method,
    p.payment_status,
    p.amount,
    p.currency,
    p.paid_at,
    cp.card_type,
    cp.card_brand,
    cp.card_last4,
    cod.collection_status,
    cod.collected_at
FROM payment AS p
LEFT JOIN card_payment AS cp
    ON cp.payment_id = p.payment_id
LEFT JOIN cash_on_delivery_payment AS cod
    ON cod.payment_id = p.payment_id
WHERE p.payment_id = ?;

应用层根据 payment_method 分派业务流程很正常，例如：

CARD                 -> 调用支付服务并处理回调
CASH_ON_DELIVERY     -> 创建待收款记录，交付后更新状态

条件分支本身没有问题，问题是不能只靠条件分支保证数据一致性。

如何保证类型与扩展表一致

上述结构中的外键只能保证扩展记录对应真实的 Payment，却不能自动保证：

CARD 支付一定存在 card_payment；
CASH_ON_DELIVERY 支付一定存在对应的 COD 记录；
CARD 支付不会被误插入 COD 扩展表。

通常有三种处理方式：

在同一个事务中写入主表和扩展表

这是多数项目最直接的做法。
使用存储过程统一创建支付记录

将类型检查和多表写入封装在统一的数据库入口中。
使用更强的复合外键建模

可以把类型列带入扩展表，再通过复合唯一键和复合外键限制类型，但结构会更复杂。是否采用这种方案，要看系统对数据库级完整性的要求。

不要只通过应用中的 if(payment_type) 维持这些约束。脚本、后台任务或其他服务都可能绕过这条代码路径。

需要特别注意的事项

不要保存完整卡号和安全码

敏感卡数据通常应由合规的支付服务商保存，业务数据库只保留：

支付服务商生成的 token；
卡品牌；
卡号后四位；
支付服务商交易号；
授权结果。

不要保存 CVV/CVC。如果业务必须处理银行卡数据，需要根据实际业务范围评估 PCI DSS 等安全与合规要求，不能只考虑表结构。

账单和支付通常不是一对一

一张账单可能经历：

首次支付失败后重新支付；
分次支付；
部分退款；
更换支付方式；
多次授权或扣款尝试。

因此，通常应设计为 bill 1:N payment，不要默认 Payment 中的 bill_id 唯一。只有在业务明确规定每张账单只能有一笔支付时，才需要增加唯一约束。

金额不要使用浮点类型

金额应使用 DECIMAL，同时明确保存币种。FLOAT 或 DOUBLE 可能产生不适合财务计算的精度误差。

时间字段表达真实事件

建议至少区分：

created_at：支付记录的创建时间；
paid_at：实际完成支付的时间；
collected_at：货到付款的现金收取时间。

尚未发生的事件应使用 NULL，不要用默认时间或特殊日期代替。

状态变更需要幂等和并发保护

支付回调、人工补单和配送确认可能重复执行。更新时应带上当前状态条件，并通过事务处理关联记录。规模较大的系统还应保存支付事件或状态变更日志，方便审计和排查。

总体来说，Payment 负责保存稳定的公共字段，扩展表负责保存各支付方式的专属字段，货到付款则建模为”支付方式 + 状态变化”。这种结构比多个可空外键和查询分支更清楚，也更容易支持新的支付渠道。