在mysql中使用触发器记录表的增删改所发生的数据前后变化存储到log表里
时间: 2024-03-27 07:38:01 浏览: 20
在MySQL中,你可以使用触发器来记录表的增删改操作。以下是一个示例触发器,用于在`my_table`表的增删改操作发生时,将变化前后的数据存储到`my_log_table`表中:
```mysql
DELIMITER $$
CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
INSERT INTO my_log_table (table_name, operation, old_data, new_data)
VALUES ('my_table', 'INSERT', NULL, JSON_OBJECT('id', NEW.id, 'col1', NEW.col1, 'col2', NEW.col2, 'col3', NEW.col3));
END$$
CREATE TRIGGER my_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
BEGIN
INSERT INTO my_log_table (table_name, operation, old_data, new_data)
VALUES ('my_table', 'UPDATE', JSON_OBJECT('id', OLD.id, 'col1', OLD.col1, 'col2', OLD.col2, 'col3', OLD.col3),
JSON_OBJECT('id', NEW.id, 'col1', NEW.col1, 'col2', NEW.col2, 'col3', NEW.col3));
END$$
CREATE TRIGGER my_trigger
AFTER DELETE ON my_table
FOR EACH ROW
BEGIN
INSERT INTO my_log_table (table_name, operation, old_data, new_data)
VALUES ('my_table', 'DELETE', JSON_OBJECT('id', OLD.id, 'col1', OLD.col1, 'col2', OLD.col2, 'col3', OLD.col3), NULL);
END$$
DELIMITER ;
```
在上面的示例中,我们创建了三个触发器,分别用于记录`my_table`表的增删改操作。在每个触发器中,我们都将变化前后的数据存储到`my_log_table`表中的`old_data`和`new_data`列中。如果是插入操作,`old_data`为`NULL`,`new_data`为新增的行数据;如果是更新操作,`old_data`为更新前的数据,`new_data`为更新后的数据;如果是删除操作,`old_data`为删除前的数据,`new_data`为`NULL`。
需要注意的是,在触发器中,我们使用了`JSON_OBJECT()`函数将数据转换为JSON格式,方便存储和查询。如果你想要查询`my_log_table`表中的数据,可以使用`JSON_EXTRACT()`函数来提取JSON格式的数据。例如:
```mysql
-- 查询my_log_table表中的数据
SELECT table_name, operation, JSON_EXTRACT(old_data, '$.id') AS old_id, JSON_EXTRACT(new_data, '$.id') AS new_id
FROM my_log_table;
```
在上面的示例中,我们使用`JSON_EXTRACT()`函数来提取`old_data`和`new_data`列中的`id`字段。你可以根据自己的需求使用`JSON_EXTRACT()`函数来提取其他字段。