记录mysql更新 [英] log mysql updates

问题描述

我有一个迁移脚本，该脚本从一个数据库读取并写入另一个数据库.

I have a migration script that reads from one DB and write to a second DB.

我通常会更新现有记录.我如何记录更新，如:

I usually update the existing records. How can I log the updates like:

productID : 125
title : Product1 => test update
price : 125 => 140

这意味着productID 125的标题为"Products1"，更新后变为"test"，价格为"125"，变为"140"

This means that the productID 125 had title "Products1" and became "test" after update and had price "125" which became "140"

一种想法是读取记录中保留的值，然后进行更新，再次读取值并进行比较并记录哪些必填字段.

One thought is to read the record keep the values and then update, read again the values and the compare and log what necessary fields.

还有其他方法吗?

推荐答案

您可以使用触发器并将更改存储在另一个表中.

You could use a trigger and store the changes in another table.

从我的头开始(以下情况假设productId永远不会更新)；

From the top of my head (the following assumes that productId never will be updated);

create table main (
    `id` int not null auto_increment,
    `title` varchar(30) not null,
    `price` float not null, 
    primary key(`id`)
);

create table logger (
    `id` int not null auto_increment,
    `productId` int not null,
    `from_title` varchar(30) not null,
    `to_title` varchar(30) not null,
    `from_price` float not null,
    `to_price` float not null,
    primary key(`id`)
);

delimiter //
create trigger my_logger before update on main
begin
    insert into
        logger
    set
        `productId`=OLD.`id`,
        `from_title`=OLD.`title`,
        `to_title`=NEW.`title`,
        `from_price`=OLD.`price`,
        `to_price`=NEW.`title`;
end;//
delimiter ;

这篇关于记录mysql更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！