mysql触发器可以记录变更吗_mysql触发器记录数据变更信息的实现

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);
<p>-- 创建日志表
CREATE TABLE users_audit (
log_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
operation VARCHAR(10),         -- 操作类型：INSERT, UPDATE, DELETE
old_value TEXT,                -- 修改前的值（JSON格式）
new_value TEXT,                -- 修改后的值（JSON格式）
changed_by VARCHAR(50),        -- 操作人（可从SESSION获取或默认CURRENT_USER）
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 变更时间
);</p>

DELIMITER $$
CREATE TRIGGER after_users_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit (user_id, operation, new_value, changed_by)
    VALUES (NEW.id, 'INSERT', JSON_OBJECT('name', NEW.name, 'email', NEW.email), CURRENT_USER());
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER after_users_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit (user_id, operation, old_value, new_value, changed_by)
    VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('name', OLD.name, 'email', OLD.email),
        JSON_OBJECT('name', NEW.name, 'email', NEW.email),
        CURRENT_USER()
    );
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER after_users_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit (user_id, operation, old_value, changed_by)
    VALUES (
        OLD.id,
        'DELETE',
        JSON_OBJECT('name', OLD.name, 'email', OLD.email),
        CURRENT_USER()
    );
END$$
DELIMITER ;