MySQL数据版本控制

发布于 2024-10-16 12:06:20 字数 984 浏览 2 评论 0原文

有没有办法将MySQL设置为每次更改一行时，然后使用原始数据创建到另一个表/数据库的行？（带时间戳）

如果是这样我该怎么做？

例如，

UPDATE `live_db`.`people`
SET    `live_db`.`people`.`name` = 'bob'
WHERE  `id` = 1;

导致这种情况在更新之前发生：

INSERT INTO `changes_db`.`people`
SELECT *
FROM   `live_db`.`people`
WHERE  `live_db`.`people`.`id` = 1;

如果您再次执行此操作，则会导致类似这样的结果：

`live_db`.`people`
+----+-------+---------------------+
| id | name  | created             |
+----+-------+---------------------+
| 1  | jones | 10:32:20 12/06/2010 |
+----+-------+---------------------+

`changes_db`.`people`
+----+-------+---------------------+
| id | name  | updated             |
+----+-------+---------------------+
| 1  | billy | 12:11:25 13/06/2010 |
| 1  | bob   | 03:01:54 14/06/2010 |
+----+-------+---------------------+

实时数据库需要在行上有一个创建的时间戳，并且更改数据库需要有一个时间戳实时数据库行已更新。更改数据库也将没有主键和外键约束。

我正在使用 InnoDB 和 MySQL 5.1.49，但如果需要可以升级。

原文

Is there any way to setup MySQL to every time a row is changed, then a row to another table/database is created with what the data was originally? (with time stamping)

If so how would I go about doing it?

E.g.

UPDATE `live_db`.`people`
SET    `live_db`.`people`.`name` = 'bob'
WHERE  `id` = 1;

Causes this to happen before the update:

INSERT INTO `changes_db`.`people`
SELECT *
FROM   `live_db`.`people`
WHERE  `live_db`.`people`.`id` = 1;

And if you did it again it would result in something like this:

`live_db`.`people`
+----+-------+---------------------+
| id | name  | created             |
+----+-------+---------------------+
| 1  | jones | 10:32:20 12/06/2010 |
+----+-------+---------------------+

`changes_db`.`people`
+----+-------+---------------------+
| id | name  | updated             |
+----+-------+---------------------+
| 1  | billy | 12:11:25 13/06/2010 |
| 1  | bob   | 03:01:54 14/06/2010 |
+----+-------+---------------------+

The live DB needs to have a created time stamp on the rows, and the changes DB needs to have a time stamp of when the live DB row was updated.
The changes DB will also have no primary keys and foreign key constraints.

I'm using InnoDB and MySQL 5.1.49 but can upgrade if required.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一世旳自豪 2024-10-23 12:06:20

使用触发器

MySQL 对触发器的支持从 MySQL 版本 5.0 开始。 2.

回复收藏 0 原文

听，心雨的声音 2024-10-23 12:06:20

您可以创建一个触发器：

DELIMITER \\
CREATE TRIGGER logtrigger BEFORE UPDATE ON live_db.people
FOR EACH ROW BEGIN
    INSERT INTO changes_db.people(id,name,updated) VALUES(OLD.id,OLD.name,now());
END;
\\

You can create a trigger:

DELIMITER \\
CREATE TRIGGER logtrigger BEFORE UPDATE ON live_db.people
FOR EACH ROW BEGIN
    INSERT INTO changes_db.people(id,name,updated) VALUES(OLD.id,OLD.name,now());
END;
\\

回复收藏 0 原文

漫漫岁月 2024-10-23 12:06:20

这就是我最终做到的

DELIMITER |

# Create the log table
CREATE TABLE IF NOT EXISTS `DB_LOG`.`TABLE`
LIKE `DB`.`TABLE`|

# Remove any auto increment
ALTER TABLE `DB_LOG`.`TABLE` CHANGE `DB_LOG`.`TABLE`.`PK` `DB_LOG`.`TABLE`.`PK` INT UNSIGNED NOT NULL|
# Drop the primary keys
ALTER TABLE `DB_LOG`.`TABLE` DROP PRIMARY KEY|

#Create the trigger
DROP TRIGGER IF EXISTS `DB`.`update_TABLE`|
CREATE TRIGGER `DB`.`update_TABLE` BEFORE UPDATE ON `DB`.`TABLE` FOR EACH ROW
BEGIN
    INSERT INTO `DB_LOG`.`TABLE`
    SELECT `DB`.`TABLE`.*
    FROM `DB`.`TABLE`
    WHERE `DB`.`TABLE`.`PK` = NEW.`PK`;
END|

DELIMITER ;

This is how I ended up doing it

DELIMITER |

# Create the log table
CREATE TABLE IF NOT EXISTS `DB_LOG`.`TABLE`
LIKE `DB`.`TABLE`|

# Remove any auto increment
ALTER TABLE `DB_LOG`.`TABLE` CHANGE `DB_LOG`.`TABLE`.`PK` `DB_LOG`.`TABLE`.`PK` INT UNSIGNED NOT NULL|
# Drop the primary keys
ALTER TABLE `DB_LOG`.`TABLE` DROP PRIMARY KEY|

#Create the trigger
DROP TRIGGER IF EXISTS `DB`.`update_TABLE`|
CREATE TRIGGER `DB`.`update_TABLE` BEFORE UPDATE ON `DB`.`TABLE` FOR EACH ROW
BEGIN
    INSERT INTO `DB_LOG`.`TABLE`
    SELECT `DB`.`TABLE`.*
    FROM `DB`.`TABLE`
    WHERE `DB`.`TABLE`.`PK` = NEW.`PK`;
END|

DELIMITER ;

回复收藏 0 原文