mysql如何设计数据日志表
设计MySQL日志表用于记录数据变更，需包含操作类型、用户、时间、IP等信息，使用JSON字段存储新旧数据，通过触发器自动写入，并在user_id和changed_at上建立索引以提升查询效率。
设计MySQL数据日志表，核心目标是记录关键数据的变更历史，便于追踪、审计和恢复。重点在于结构清晰、性能可控、查询方便。
明确日志表用途
先确定你要记录什么：
- 操作类型：INSERT、UPDATE、DELETE
- 变更字段：整行记录 or 仅变化字段
- 谁操作的：用户ID或系统标识
- 何时操作：精确到毫秒的时间戳
- 来源信息：IP地址、客户端信息等（可选）
设计日志表结构
以记录用户表（user）的变更为例，设计日志表 user_log：
CREATE TABLE user_log ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, operation ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL, user_id INT NOT NULL COMMENT '关联的用户ID', old_data JSON DEFAULT NULL COMMENT '变更前的数据', new_data JSON DEFAULT
NULL COMMENT '变更后的数据',
changed_by VARCHAR(50) DEFAULT NULL COMMENT '操作人',
changed_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
ip_address VARCHAR(45) DEFAULT NULL COMMENT '操作IP'
);
说明：
- 使用 BIGINT 作为主键，支持大量日志写入
- operation 用 ENUM 提高可读性和存储效率
- old_data / new_data 使用 JSON 存储整行数据，灵活且无需随业务表频繁改结构
- DATETIME(3) 支持毫秒精度，便于排序和排查问题
- 索引建议：在 user_id 和 changed_at 上建联合索引，加速按对象和时间查询
如何写入日志数据
可通过触发器自动记录，例如为 user 表创建 UPDATE 触发器：
DELIMITER ;; CREATE TRIGGER user_after_update AFTER UPDATE ON user FOR EACH ROW BEGIN INSERT INTO user_log (operation, user_id, old_data, new_data, changed_by, ip_address) VALUES ('UPDATE', NEW.id, JSON_OBJECT('name', OLD.name, 'email', OLD.email), JSON_OBJECT('name', NEW.name, 'email', NEW.email), @operator, @ip); END;; DELIMITER ;注意：
- INSERT 触发器只需记录 new_data
- DELETE 触发器只需记录 old_data
- 变量 @operator 和 @ip 可在连接时由应用层设置
- 触发器影响性能，高频写场景建议用应用层异步写日志
优化与维护建议
- 定期归档老日志，可用分区表按月分区，提升查询效率
- 避免在日志表上做复杂 JOIN，必要时导出分析
- 敏感字段如密码，在日志中应脱敏处理
- 考虑压缩：对 JSON 字段多的表，启用 innodb_page_compression 减少空间占用
