MySQL如何开启表级触发器进行安全审计_自定义audit

mysql表级审计最可控方式是用after insert/update/delete触发器写入audit_log表，需手动为每张业务表创建触发器；audit_log应含table_name、record_id、action_type、old_data/new_data等字段，并建立合理索引。

mysql如何开启表级触发器进行安全审计_自定义audit_table记录日志

直接上结论：用 AFTER INSERT、AFTER UPDATE、AFTER DELETE 触发器写入自定义 audit_log 表，是 MySQL 表级审计最可控、最易落地的方式；它不依赖插件、不改配置、不重启服务，但必须手动为每张业务表创建对应触发器。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

`AFTER` 触发器比 `BEFORE` 更适合审计

AFTER 触发器在 DML 操作已成功提交（或回滚）后执行，能确保记录的是最终生效的数据状态
BEFORE 触发器中 NEW 可被修改，OLD 不能被修改，但若业务逻辑里有异常或事务失败，BEFORE 写入的审计日志可能“记录了没发生的操作”
审计的核心诉求是“事实发生了什么”，不是“意图做什么”，所以优先选 AFTER
注意：AFTER 触发器无法阻止主操作（比如不能像 BEFORE 那样用 SIGNAL 拒绝插入），但这对审计反而是优点——避免审计逻辑干扰业务一致性

`audit_log` 表设计要兼顾通用性与可查性

必须支持多表复用，不能为每个业务表建一张审计表
推荐字段：
- table_name VARCHAR(64)：记录被审计的表名，如 'users'
- record_id VARCHAR(255)：主键值，统一转字符串（兼容 BIGINT、UUID、COMPOSITE PK）
- action_type ENUM('INSERT','UPDATE','DELETE')：明确操作类型
- old_data JSON 和 new_data JSON：只存变更涉及字段，非全量；UPDATE 时两者都填，INSERT 只填 new_data，DELETE 只填 old_data
- changed_by VARCHAR(255) DEFAULT (CURRENT_USER())：用 CURRENT_USER() 而非 USER()，前者是授权用户，后者可能是代理连接用户，更可靠
- change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP：用 CURRENT_TIMESTAMP，不是 NOW()，避免时区歧义
索引建议：
- CREATE INDEX idx_audit_table_time ON audit_log (table_name, change_timestamp);
- CREATE INDEX idx_audit_record ON audit_log (table_name, record_id);

为业务表写触发器时，字段映射最容易出错

错误现象：INSERT 后 audit_log 中 new_data 字段为空，或 JSON 格式非法
原因常是：
- 直接拼接字符串构造 JSON，比如 CONCAT('{"name":"', NEW.name, '"}') —— 一旦 NEW.name 含单引号或反斜杠就崩
- 漏写 DELIMITER $$，导致分号被 MySQL 当作语句结束，触发器体被截断
- 对 NULL 字段未做处理，JSON_OBJECT('col', NULL) 会把整个 key 丢掉，看不出该字段是否真为 NULL

正确写法示例（以 users 表为例）：

DELIMITER $$
CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, record_id, action_type, new_data, changed_by)
VALUES ('users', CAST(NEW.id AS CHAR), 'INSERT',
        JSON_OBJECT('id', NEW.id, 'username', NEW.username, 'email', NEW.email),
        CURRENT_USER());
END$$
DELIMITER ;