禁止直接 update 业务关键字段

三条核心规则（核心字段只允许状态驱动修改、所有关键变更写审计表、禁止直接 update 业务关键字段）在 PHP 中实现的完整生产级示例。

我们以一个典型的订单系统为例，核心字段包括 status（订单状态），这是典型的业务关键字段。

技术栈假设

• PHP 8.1+
• MySQL
• 使用 PDO 进行数据库操作（推荐）
• 简单的领域模型 + 服务层设计（不依赖框架，也便于迁移到 Laravel/Symfony）

1. 数据库表结构

-- 订单主表
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1=待支付,2=已支付,3=已发货,4=已完成,5=已取消',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 订单审计表
CREATE TABLE order_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    old_value VARCHAR(255) NULL,
    new_value VARCHAR(255) NULL,
    operator_id BIGINT UNSIGNED NOT NULL COMMENT '操作人ID',
    operation VARCHAR(50) NOT NULL COMMENT '如 change_status',
    reason VARCHAR(255) NULL COMMENT '变更原因',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_id (order_id)
);

2. 订单状态枚举（状态机定义）

<?php
// src/Enum/OrderStatus.php
namespace App\Enum;

enum OrderStatus: int
{
    case PendingPayment = 1;  // 待支付
    case Paid           = 2;  // 已支付
    case Shipped        = 3;  // 已发货
    case Completed      = 4;  // 已完成
    case Cancelled      = 5;  // 已取消

    // 定义允许的状态流转规则（状态机）
    public function canTransitionTo(self $newStatus): bool
    {
        return match ($this) {
            self::PendingPayment => in_array($newStatus, [self::Paid, self::Cancelled]),
            self::Paid           => in_array($newStatus, [self::Shipped, self::Cancelled]),
            self::Shipped        => $newStatus === self::Completed,
            self::Completed      => false,
            self::Cancelled      => false,
            default              => false,
        };
    }

    public function getName(): string
    {
        return match ($this) {
            self::PendingPayment => '待支付',
            self::Paid           => '已支付',
            self::Shipped        => '已发货',
            self::Completed      => '已完成',
            self::Cancelled      => '已取消',
        };
    }
}

3. 订单实体（核心字段只允许通过方法修改）

<?php
// src/Entity/Order.php
namespace App\Entity;

use App\Enum\OrderStatus;

class Order
{
    private int $id;
    private string $orderNo;
    private int $userId;
    private float $amount;
    private OrderStatus $status;

    public function __construct(int $id, string $orderNo, int $userId, float $amount, OrderStatus $status)
    {
        $this->id = $id;
        $this->orderNo = $orderNo;
        $this->userId = $userId;
        $this->amount = $amount;
        $this->status = $status;
    }

    public function getId(): int { return $this->id; }
    public function getOrderNo(): string { return $this->orderNo; }
    public function getStatus(): OrderStatus { return $this->status; }

    // 禁止外部直接修改状态，只能通过 changeStatus 方法（状态驱动）
    public function changeStatus(OrderStatus $newStatus, string $reason = ''): void
    {
        if (!$this->status->canTransitionTo($newStatus)) {
            throw new \DomainException(
                sprintf('订单[%s] 当前状态[%s] 不能变更为[%s]', 
                    $this->orderNo, 
                    $this->status->getName(), 
                    $newStatus->getName()
                )
            );
        }

        $this->status = $newStatus;
        // 注意：这里不直接写数据库，由服务层统一处理审计和持久化
    }
}

4. 审计日志服务

<?php
// src/Service/OrderAuditService.php
namespace App\Service;

use PDO;

class OrderAuditService
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function log(
        int $orderId,
        string $fieldName,
        ?string $oldValue,
        ?string $newValue,
        int $operatorId,
        string $operation,
        ?string $reason = null
    ): void {
        $sql = "INSERT INTO order_audit_logs 
                (order_id, field_name, old_value, new_value, operator_id, operation, reason) 
                VALUES (:order_id, :field, :old, :new, :operator, :op, :reason)";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':order_id'  => $orderId,
            ':field'     => $fieldName,
            ':old'       => $oldValue,
            ':new'       => $newValue,
            ':operator'  => $operatorId,
            ':op'        => $operation,
            ':reason'    => $reason,
        ]);
    }
}

5. 订单服务层（核心业务逻辑 + 审计 + 禁止直接UPDATE）

<?php
// src/Service/OrderService.php
namespace App\Service;

use App\Entity\Order;
use App\Enum\OrderStatus;
use PDO;
use PDOException;

class OrderService
{
    private PDO $pdo;
    private OrderAuditService $auditService;

    public function __construct(PDO $pdo, OrderAuditService $auditService)
    {
        $this->pdo = $pdo;
        $this->auditService = $auditService;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    // 加载订单（从数据库）
    public function getOrderById(int $orderId): Order
    {
        $stmt = $this->pdo->prepare("SELECT * FROM orders WHERE id = :id");
        $stmt->execute([':id' => $orderId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$row) {
            throw new \Exception("订单不存在");
        }

        return new Order(
            $row['id'],
            $row['order_no'],
            $row['user_id'],
            (float)$row['amount'],
            OrderStatus::from($row['status'])
        );
    }

    // 关键变更：修改订单状态（状态驱动 + 审计）
    public function changeOrderStatus(
        int $orderId,
        OrderStatus $newStatus,
        int $operatorId,
        string $reason = ''
    ): void {
        $this->pdo->beginTransaction();
        try {
            $order = $this->getOrderById($orderId);

            $oldStatus = $order->getStatus();

            // 状态驱动修改（唯一允许修改状态的入口）
            $order->changeStatus($newStatus, $reason);

            // 更新主表（仅此一处允许 UPDATE status）
            $sql = "UPDATE orders SET status = :status WHERE id = :id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                ':status' => $newStatus->value,
                ':id'     => $orderId
            ]);

            // 写入审计日志（关键变更必审）
            $this->auditService->log(
                orderId: $orderId,
                fieldName: 'status',
                oldValue: $oldStatus->value . '(' . $oldStatus->getName() . ')',
                newValue: $newStatus->value . '(' . $newStatus->getName() . ')',
                operatorId: $operatorId,
                operation: 'change_status',
                reason: $reason
            );

            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    // 其他业务方法...（支付、发货等都类似封装）
}

6. 使用示例（控制器或脚本）

<?php
require_once 'autoload.php'; // 假设有简单的自动加载

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$auditService = new App\Service\OrderAuditService($pdo);
$orderService = new App\Service\OrderService($pdo, $auditService);

try {
    // 示例：操作员ID为1001的用户将订单ID=1的状态改为“已支付”
    $orderService->changeOrderStatus(
        orderId: 1,
        newStatus: \App\Enum\OrderStatus::Paid,
        operatorId: 1001,
        reason: '用户完成微信支付'
    );

    echo "订单状态修改成功并已记录审计日志\n";
} catch (\Exception $e) {
    echo "操作失败: " . $e->getMessage() . "\n";
}

总结：如何满足三条规则

这种设计在生产环境中非常稳健、可审计、可扩展，后续可轻松迁移到 Laravel（使用 Eloquent 事件 + Policy）或 Symfony（使用 Doctrine 事件）中保持相同约束。

如需加入更多字段（如金额退款、地址变更）的审计，只需扩展类似模式即可。

Don’t reinvent the wheel, library code is there to help.

欢迎关注公-众-号【TaonyDaily】、留言、评论，一起学习。