MySQL 审计日志小项目：记录订单状态变更、查询和清理

订单状态从“待支付”变成“已支付”，再变成“已发货”，后台通常只保留当前状态。等到客服追问“谁在什么时候改了状态”，或者运营发现订单状态异常时，如果没有审计日志，就只能翻业务日志，定位成本很高。

本文做一个小项目：用 MySQL 记录订单状态变更审计日志。项目包含订单表、审计表、触发器、查询 SQL 和清理策略。目标不是把所有审计能力一次做完，而是先搭一个可运行、可查询、可维护的基础版本。

项目目标：记录每一次订单状态变化

这个小项目要解决三个问题：

• 订单状态变更后，能看到旧状态、新状态、变更时间。
• 按订单号查询时，能按时间线看到完整状态流转。
• 审计日志长期增长后，可以按时间分批清理。

数据生命周期可以理解为：业务更新订单状态，MySQL 触发审计记录，审计表追加一行日志，查询时按订单聚合时间线，最后按保留周期清理历史数据。

审计日志要尽量保持追加写，不要覆盖。因为它记录的是“发生过什么”，不是当前状态。

环境准备：创建订单表和审计表

先准备一张简化订单表。真实项目里字段会更多，这里只保留演示需要的字段。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(64) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  updated_by VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

审计表单独保存状态变化。这里把旧状态、新状态、操作者和变更时间都放进去。

CREATE TABLE order_status_audit (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  order_no VARCHAR(64) NOT NULL,
  old_status VARCHAR(32) NOT NULL,
  new_status VARCHAR(32) NOT NULL,
  changed_by VARCHAR(64) NOT NULL,
  changed_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_order_time (order_id, changed_at),
  KEY idx_changed_at (changed_at)
);

idx_order_time 用于按订单查时间线，idx_changed_at 用于后续按时间清理。审计表的查询和清理路径一开始就要考虑，否则数据量上来后会很难补。

核心代码：用触发器写入状态变更日志

状态变更可以由业务代码主动写审计表，也可以用触发器兜底。这个小项目使用触发器，重点演示“只要订单状态发生变化，就追加审计记录”。

DELIMITER //

CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status  NEW.status THEN
    INSERT INTO order_status_audit (
      order_id,
      order_no,
      old_status,
      new_status,
      changed_by,
      changed_at,
      created_at
    ) VALUES (
      NEW.id,
      NEW.order_no,
      OLD.status,
      NEW.status,
      NEW.updated_by,
      NEW.updated_at,
      NOW()
    );
  END IF;
END//

DELIMITER ;

这里有一个关键判断：只有 OLD.status NEW.status 时才写审计日志。如果只是修改金额、备注或更新时间，不应该生成状态变更审计。

本地运行：插入订单并模拟状态流转

先插入一条待支付订单：

INSERT INTO orders (
  order_no, user_id, status, amount, updated_by, created_at, updated_at
) VALUES (
  'SO202606290001', 10001, 'WAIT_PAY', 199.00, 'system', NOW(), NOW()
);

再模拟两次状态变化：

UPDATE orders
SET status = 'PAID',
    updated_by = 'pay-callback',
    updated_at = NOW()
WHERE order_no = 'SO202606290001';

UPDATE orders
SET status = 'SHIPPED',
    updated_by = 'warehouse-admin',
    updated_at = NOW()
WHERE order_no = 'SO202606290001';

查询审计时间线：

SELECT
  order_no,
  old_status,
  new_status,
  changed_by,
  changed_at
FROM order_status_audit
WHERE order_no = 'SO202606290001'
ORDER BY changed_at ASC;

预期可以看到两行记录：WAIT_PAY -> PAID，以及 PAID -> SHIPPED。

部署集成：业务代码和触发器怎么配合

触发器适合做兜底，但业务代码仍然要负责提供清晰的操作者和变更时间。例如支付回调更新状态时，把 updated_by 写成 pay-callback；后台人工发货时，把 updated_by 写成人员账号或系统账号。

集成时建议遵守三条规则：

• 订单状态只能通过统一入口更新，避免绕过 updated_by。
• 触发器只记录事实，不写复杂业务判断。
• 审计表只追加，不在业务流程里修改历史审计行。

如果系统对触发器比较敏感，也可以改成业务代码主动写审计表。但无论哪种方式，都要保持同一条原则：状态变更和审计记录要在同一个事务里完成。

验收清单：查询、索引和过期清理

上线前先按下面清单验收。

按订单查询是否稳定

用 EXPLAIN 看是否命中 idx_order_time：

EXPLAIN
SELECT old_status, new_status, changed_by, changed_at
FROM order_status_audit
WHERE order_id = 1
ORDER BY changed_at ASC;

重复更新是否会产生无效日志

如果状态没有变化，只更新 updated_at，审计表不应该新增记录。

历史数据是否能分批清理

审计日志通常要保留一段时间，例如 180 天。清理时不要一次删除太多行，建议分批处理。

DELETE FROM order_status_audit
WHERE changed_at 

清理任务跑完后再继续下一批，避免长事务和锁等待。对审计要求更高的业务，可以先归档到冷库，再删除在线表数据。

总结

这个 MySQL 审计日志小项目的核心是把“当前订单状态”和“历史状态变化”分开存储。订单表保存当前值，审计表追加记录每一次变化；触发器或业务代码负责在同一事务里写入审计；查询时按订单时间线展示；清理时按时间分批处理。这样既能满足排查和追溯，又不会把审计逻辑散落到各个页面里。