企业官网建设流程全解析

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.Query<dynamic>( $"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.Query<dynamic>( $"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 List<VersionCompareResult> CompareVersions(int orderId, int version1, int version2) { var v1 = _db.QuerySingle<OrderSnapshot>( "SELECT snapshot FROM sa_order_version WHERE order_id = @OrderId AND version = @V1", new { OrderId = orderId, V1 = version1 } ); var v2 = _db.QuerySingle<OrderSnapshot>( "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;

帮助了解系统使用高峰，做性能优化。

成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型