有人询问我能否跟踪MySQL数据库记录的更改。因此,当一个字段被更改时,旧值和新值以及更改日期都应该可用。是否存在某种功能或常见技术来实现这一点呢?
如果有,我会考虑像这样做。创建一个名为changes
的表。它将包含与主表相同的字段,但是前缀为“old”和“new”,但仅针对实际更改的那些字段,并带有一个TIMESTAMP
。它将用一个ID
进行索引。这样,可以运行SELECT
报告以显示每个记录的历史记录。这是一个好方法吗?谢谢!
CREATE TABLE MyDB.data_history LIKE MyDB.data;
ALTER TABLE MyDB.data_history MODIFY COLUMN primary_key_column int(11) NOT NULL,
DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST,
ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
ADD PRIMARY KEY (primary_key_column, revision);
DROP TRIGGER IF EXISTS MyDB.data__ai;
DROP TRIGGER IF EXISTS MyDB.data__au;
DROP TRIGGER IF EXISTS MyDB.data__bd;
CREATE TRIGGER MyDB.data__ai AFTER INSERT ON MyDB.data FOR EACH ROW
INSERT INTO MyDB.data_history SELECT 'insert', NULL, NOW(), d.*
FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;
CREATE TRIGGER MyDB.data__au AFTER UPDATE ON MyDB.data FOR EACH ROW
INSERT INTO MyDB.data_history SELECT 'update', NULL, NOW(), d.*
FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;
CREATE TRIGGER MyDB.data__bd BEFORE DELETE ON MyDB.data FOR EACH ROW
INSERT INTO MyDB.data_history SELECT 'delete', NULL, NOW(), d.*
FROM MyDB.data AS d WHERE d.primary_key_column = OLD.primary_key_column;
ID revision action data columns..
1 1 'insert' .... initial entry for row where ID = 1
1 2 'update' .... changes made to row where ID = 1
2 1 'insert' .... initial entry, ID = 2
3 1 'insert' .... initial entry, ID = 3
1 3 'update' .... more changes made to row where ID = 1
3 2 'update' .... changes made to row where ID = 3
2 2 'delete' .... deletion of row where ID = 2
CREATE VIEW data_history_changes AS
SELECT t2.dt_datetime, t2.action, t1.primary_key_column as 'row id',
IF(t1.a_column = t2.a_column, t1.a_column, CONCAT(t1.a_column, " to ", t2.a_column)) as a_column
FROM MyDB.data_history as t1 INNER join MyDB.data_history as t2 on t1.primary_key_column = t2.primary_key_column
WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision+1
ORDER BY t1.primary_key_column ASC, t2.revision ASC
这很微妙。
如果业务需求是“我想审计数据的更改——谁在何时进行了什么操作?”您通常可以使用审计表(如Keethanjan提供的触发器示例)。我不太喜欢触发器,但它有一个巨大的好处,就是相对容易实现——您现有的代码不需要知道触发器和审计内容。
如果业务需求是“展示给我特定日期过去的数据状态”,这意味着时间变化方面已经进入了您的解决方案。虽然您可以通过查看审计表来勉强重建数据库的状态,但是这很困难且容易出错,对于任何复杂的数据库逻辑,它都变得笨重。例如,如果业务要求知道“找到应该发送给第一个月未付发票的客户的信件地址”,您可能需要搜索半打审计表。
相反,您可以将时间变化的概念融入模式设计中(这是Keethanjan建议的第二个选项)。这是对应用程序的更改,至少涉及业务逻辑和持久性级别,因此它并不简单。
例如,如果您有这样一个表:
CUSTOMER
---------
CUSTOMER_ID PK
CUSTOMER_NAME
CUSTOMER_ADDRESS
如果你想随着时间的推移进行跟踪,那么你应该按以下方式修改它:
CUSTOMER
------------
CUSTOMER_ID PK
CUSTOMER_VALID_FROM PK
CUSTOMER_VALID_UNTIL PK
CUSTOMER_STATUS
CUSTOMER_USER
CUSTOMER_NAME
CUSTOMER_ADDRESS
每次你想更改一个客户记录时,不要直接更新该记录,而是将当前记录的VALID_UNTIL设置为NOW(),然后插入一个新记录,该新记录具有VALID_FROM(现在)和空VALID_UNTIL。如果需要保留,则将“CUSTOMER_USER”状态设置为当前用户的登录ID。如果需要删除客户,则使用CUSTOMER_STATUS标志来指示此操作-您永远不应从此表中删除记录。DROP TRIGGER IF EXISTS history_trigger $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
FOR EACH ROW
BEGIN
IF OLD.first_name != NEW.first_name
THEN
INSERT INTO history_clients
(
client_id ,
col ,
value ,
user_id ,
edit_time
)
VALUES
(
NEW.client_id,
'first_name',
NEW.first_name,
NEW.editor_id,
NEW.last_mod
);
END IF;
IF OLD.last_name != NEW.last_name
THEN
INSERT INTO history_clients
(
client_id ,
col ,
value ,
user_id ,
edit_time
)
VALUES
(
NEW.client_id,
'last_name',
NEW.last_name,
NEW.editor_id,
NEW.last_mod
);
END IF;
END;
$$
另一种解决方案是保留一个修订字段,并在保存时更新该字段。您可以决定最大值是最新的修订版本,还是将0视为最近的行。这取决于您。
MariaDB 支持自10.3版本起的系统版本控制,这是标准 SQL 功能,可以实现你想要的功能:它存储表记录的历史并通过SELECT
查询提供访问。MariaDB是MySQL的一个开放式发展分支。您可以通过此链接了解有关其系统版本控制的更多信息:
以下是我们的解决方案:
一个名为“用户”的表格如下:
Users
-------------------------------------------------
id | name | address | phone | email | created_on | updated_on
业务需求发生了变化,我们需要检查用户曾经拥有的所有地址和电话号码。 新的架构如下所示
Users (the data that won't change over time)
-------------
id | name
UserData (the data that can change over time and needs to be tracked)
-------------------------------------------------
id | id_user | revision | city | address | phone | email | created_on
1 | 1 | 0 | NY | lake st | 9809 | @long | 2015-10-24 10:24:20
2 | 1 | 2 | Tokyo| lake st | 9809 | @long | 2015-10-24 10:24:20
3 | 1 | 3 | Sdny | lake st | 9809 | @long | 2015-10-24 10:24:20
4 | 2 | 0 | Ankr | lake st | 9809 | @long | 2015-10-24 10:24:20
5 | 2 | 1 | Lond | lake st | 9809 | @long | 2015-10-24 10:24:20
要找到任何用户的当前地址,我们搜索具有降序修订和1个限制的UserData
要在一定时间段内获取用户的地址,可以使用created_on bewteen(date1,date2)
id_user=1
的 revision=1
发生了什么?一开始我以为你的计数是 0,2,3,...
,但后来我发现对于 id_user=2
,修订计数是 0,1, ...
。 - Pathrosid
和 id_user
列。只需使用 id
(用户ID)和 revision
的组ID。 - Gajus仅供参考。我会创建一个解决方案,记录所做更改的详细信息,非常类似于临时解决方案。
我的ChangesTable很简单:
日期时间 | 谁变更了 | 表名 | 操作 | ID |字段名 | 旧值
1)当主表中整行发生更改时,将有大量条目进入此表,但这很不可能发生,因此不是一个大问题(人们通常只更改一件事情) 2)旧值(如果需要新值)必须是某种史诗级别的“任何类型”,因为它可以是任何数据,可能有一种方法使用RAW类型或仅使用JSON字符串进行转换。
最小化数据使用,存储您需要的所有内容,并可同时用于所有表格。我现在正在研究这个问题,但这可能会成为我走的路。
对于创建和删除,只需行ID,不需要字段。在删除时,可以在主表上设置一个标志(活动?)。
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME;
可以通过以下方式查询过去的历史记录:
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2016-10-09 08:07:06';
MySQL目前没有这方面的对应物。
请查看文档以获取更多信息。如果您使用的是较旧版本的MariaDB,则可以在文档中找到替代语法,该语法自MariaDB 10.3.4起已可用。
直接的方法是在表上创建触发器。设置一些条件或映射方法。当更新或删除发生时,它会自动插入到“更改”表中。
但最大的问题是如果我们有很多列和很多表怎么办。我们必须输入每个表的每个列的名称。显然,这是浪费时间。
为了更加优雅地处理这个问题,我们可以创建一些过程或函数来检索列名。
我们也可以使用第三方工具来简单地完成这项工作。在这里,我编写了一个Java程序Mysql Tracker