ERP系统里发生的事都要能查到谁改了什么、什么时候改的、改之前的值是什么这不只是合规要求也是排错的基本功。一、操作日志1. 日志表设计CREATE TABLE sys_operation_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 操作人 user_id BIGINT NOT NULL, user_name VARCHAR(50), -- 操作时间 operation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 操作类型 operation_type VARCHAR(20) NOT NULL, -- CREATE/UPDATE/DELETE/APPROVE/REJECT -- 操作对象 module_code VARCHAR(30) NOT NULL, -- SALES/PURCHASE/INVENTORY/FINANCE business_type VARCHAR(30) NOT NULL, -- ORDER/RECEIPT/INVOICE... business_id BIGINT NOT NULL, business_no VARCHAR(30), -- 单据编号 -- 变更内容 field_name VARCHAR(50), old_value VARCHAR(500), new_value VARCHAR(500), -- 环境 ip_address VARCHAR(50), user_agent VARCHAR(200), -- 备注 memo VARCHAR(500), INDEX idx_user (user_id), INDEX idx_time (operation_time), INDEX idx_business (module_code, business_type, business_id), INDEX idx_type (operation_type) );2. 自动记录变更通过数据库触发器记录变更CREATE TRIGGER trg_sa_order_update AFTER UPDATE ON sa_order FOR EACH ROW BEGIN -- 客户变更 IF NEW.customer_id OLD.customer_id THEN INSERT INTO sys_operation_log (user_id, operation_type, module_code, business_type, business_id, business_no, field_name, old_value, new_value) VALUES (current_user_id, UPDATE, SALES, ORDER, NEW.id, NEW.order_no, customer_id, OLD.customer_id, NEW.customer_id); END IF; -- 金额变更 IF NEW.total_amount OLD.total_amount THEN INSERT INTO sys_operation_log (user_id, operation_type, module_code, business_type, business_id, business_no, field_name, old_value, new_value) VALUES (current_user_id, UPDATE, SALES, ORDER, NEW.id, NEW.order_no, total_amount, OLD.total_amount, NEW.total_amount); END IF; END;触发器方式对应用层透明但性能有影响。高频操作不建议用触发器。3. 应用层拦截更好的方式是在应用层统一拦截public class AuditInterceptor : ICommandInterceptor { public void BeforeExecute(CommandContext context) { if (context.OperationType OperationType.Update) { // 查询修改前的数据 var oldData _db.Querydynamic( $SELECT * FROM {context.TableName} WHERE id Id, new { context.BusinessId } ).FirstOrDefault(); context.OldData oldData; } } public void AfterExecute(CommandContext context) { if (context.OperationType OperationType.Update) { // 查询修改后的数据 var newData _db.Querydynamic( $SELECT * FROM {context.TableName} WHERE id Id, new { context.BusinessId } ).FirstOrDefault(); // 对比变更字段 var changes CompareObjects(context.OldData, newData); foreach (var change in changes) { _auditLog.Save(new OperationLog { UserId context.UserId, OperationType UPDATE, ModuleCode context.ModuleCode, BusinessType context.BusinessType, BusinessId context.BusinessId, BusinessNo context.BusinessNo, FieldName change.FieldName, OldValue change.OldValue?.ToString(), NewValue change.NewValue?.ToString() }); } } else if (context.OperationType OperationType.Create) { _auditLog.Save(new OperationLog { UserId context.UserId, OperationType CREATE, ModuleCode context.ModuleCode, BusinessType context.BusinessType, BusinessId context.BusinessId, BusinessNo context.BusinessNo, Memo 新建记录 }); } else if (context.OperationType OperationType.Delete) { _auditLog.Save(new OperationLog { UserId context.UserId, OperationType DELETE, ModuleCode context.ModuleCode, BusinessType context.BusinessType, BusinessId context.BusinessId, Memo 删除记录 }); } } }二、数据版本对于关键字段保留历史版本支持回溯和对比。1. 版本表CREATE TABLE sa_order_version ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, version INT NOT NULL, -- 完整的订单快照 snapshot JSON NOT NULL, -- 变更摘要 change_summary VARCHAR(500), -- 操作人 user_id BIGINT NOT NULL, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_version (order_id, version) );每次保存时把完整数据快照存入版本表。这样任何时候都能恢复到某个历史版本。2. 版本对比public class VersionCompareResult { public string FieldName { get; set; } public string OldValue { get; set; } public string NewValue { get; set; } } public ListVersionCompareResult CompareVersions(int orderId, int version1, int version2) { var v1 _db.QuerySingleOrderSnapshot( SELECT snapshot FROM sa_order_version WHERE order_id OrderId AND version V1, new { OrderId orderId, V1 version1 } ); var v2 _db.QuerySingleOrderSnapshot( SELECT snapshot FROM sa_order_version WHERE order_id OrderId AND version V2, new { OrderId orderId, V2 version2 } ); return CompareObjects(JsonDeserialize(v1), JsonDeserialize(v2)); }三、审批日志审批流程的每一步都要记录。CREATE TABLE sys_approval_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 审批对象 module_code VARCHAR(30) NOT NULL, business_id BIGINT NOT NULL, business_no VARCHAR(30), -- 审批节点 node_name VARCHAR(50) NOT NULL, node_seq INT, -- 审批人 approver_id BIGINT NOT NULL, approver_name VARCHAR(50), -- 审批动作 action VARCHAR(20) NOT NULL, -- APPROVE/REJECT/DELEGATE/ADD_SIGN -- 审批意见 opinion VARCHAR(500), -- 时间 receive_time DATETIME, -- 收到审批任务的时间 action_time DATETIME NOT NULL, -- 执行审批动作的时间 duration_minutes INT GENERATED ALWAYS AS ( TIMESTAMPDIFF(MINUTE, receive_time, action_time) ) STORED, INDEX idx_business (module_code, business_id), INDEX idx_approver (approver_id) );审批效率分析看每个审批节点的平均处理时间找出瓶颈。SELECT node_name, COUNT(*) AS approval_count, ROUND(AVG(duration_minutes), 1) AS avg_duration, MAX(duration_minutes) AS max_duration FROM sys_approval_log WHERE action_time BETWEEN ? AND ? GROUP BY node_name ORDER BY avg_duration DESC;四、登录日志CREATE TABLE sys_login_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT, user_name VARCHAR(50), login_time DATETIME NOT NULL, ip_address VARCHAR(50), login_result VARCHAR(10), -- SUCCESS/FAILED fail_reason VARCHAR(100), user_agent VARCHAR(200), INDEX idx_user (user_id), INDEX idx_time (login_time) );安全审计用途-- 检测异常登录非工作时间、陌生IP SELECT * FROM sys_login_log WHERE login_result SUCCESS AND (HOUR(login_time) NOT BETWEEN 8 AND 18 OR ip_address NOT IN (SELECT DISTINCT ip_address FROM sys_login_log WHERE user_id ? AND login_result SUCCESS GROUP BY ip_address HAVING COUNT(*) 5)) ORDER BY login_time DESC; -- 检测暴力破解短时间内多次失败 SELECT user_name, COUNT(*) AS fail_count, MIN(login_time) AS first_fail, MAX(login_time) AS last_fail FROM sys_login_log WHERE login_result FAILED AND login_time DATE_SUB(NOW(), INTERVAL 1 HOUR) GROUP BY user_name HAVING fail_count 5;五、日志管理1. 存储策略操作日志量很大需要分区存储。-- 按月分区 ALTER TABLE sys_operation_log PARTITION BY RANGE (YEAR(operation_time) * 100 MONTH(operation_time)) ( PARTITION p202601 VALUES LESS THAN (202602), PARTITION p202602 VALUES LESS THAN (202603), PARTITION p202603 VALUES LESS THAN (202604), -- ... PARTITION p_future VALUES LESS THAN MAXVALUE );2. 归档策略超过一年的日志归档到历史表或文件。-- 归档 INSERT INTO sys_operation_log_archive SELECT * FROM sys_operation_log WHERE operation_time DATE_SUB(CURDATE(), INTERVAL 1 YEAR); -- 删除已归档数据 DELETE FROM sys_operation_log WHERE operation_time DATE_SUB(CURDATE(), INTERVAL 1 YEAR);3. 查询优化日志查询频繁但不需要实时。可以建汇总表CREATE TABLE sys_operation_log_daily ( log_date DATE NOT NULL, module_code VARCHAR(30), operation_type VARCHAR(20), user_id BIGINT, operation_count INT, PRIMARY KEY (log_date, module_code, operation_type, user_id) ); -- 每日汇总 INSERT INTO sys_operation_log_daily SELECT DATE(operation_time), module_code, operation_type, user_id, COUNT(*) FROM sys_operation_log WHERE DATE(operation_time) CURDATE() - INTERVAL 1 DAY GROUP BY DATE(operation_time), module_code, operation_type, user_id;六、审计报告1. 异常操作检测-- 单日删除操作超过阈值 SELECT user_name, COUNT(*) AS delete_count FROM sys_operation_log WHERE operation_type DELETE AND operation_time CURDATE() GROUP BY user_name HAVING delete_count 10; -- 敏感字段变更 SELECT * FROM sys_operation_log WHERE field_name IN (credit_limit, price, discount_rate, tax_rate) AND operation_time DATE_SUB(NOW(), INTERVAL 24 HOUR) ORDER BY operation_time DESC;2. 操作热力图-- 按小时统计操作量 SELECT HOUR(operation_time) AS hour_slot, COUNT(*) AS op_count FROM sys_operation_log WHERE operation_time CURDATE() GROUP BY HOUR(operation_time) ORDER BY hour_slot;帮助了解系统使用高峰做性能优化。成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型