如何比较表中的数据(操作前后)?

Is there any free tool or a way to get to know what has changed in database's table?

是否有免费的工具或方法来了解数据库表中发生了什么变化?

4 个解决方案

#1

You could take a copy before the update

你可以在更新前复印一份

CREATE TABLE t2 AS SELECT * FROM t1

Run your update

运行您的更新

Then to show the differences

然后展示差异

use this to show updates:

使用这个来显示更新:

SELECT * FROM t1
MINUS
SELECT * FROM t2

use this to show the deletes:

用这个来显示删除:

SELECT * FROM t2
WHERE NOT EXISTS(SELECT 1 FROM t1 WHERE t1.primary_key = t2.primary_key)

and finally this to check the total number of records are identical

最后，检查记录的总数是否相同

SELECT count(*) FROM t1

SELECT count(*) FROM t2

Note: If there are other sessions updating t1 it could be tricky spotting your updates.

注意:如果有其他会话更新t1，可能很难发现您的更新。

#2

I have used Toad for MySQL very successfully in times past (for both the Schema and Data). I see it is also compatible with Oracle.

在过去的时间里，我非常成功地使用了Toad作为MySQL(用于模式和数据)。我认为它也与Oracle兼容。

#3

Try liquibase, it provides the version control mechanism for database.

试试liquibase，它提供了数据库的版本控制机制。

#4

Triggers really should be avoided but ...

触发器确实应该避免，但是……

If you are in a non-production environment you can set up a trigger to perform logging to a new table. You need 5 fields something like this:

如果您处于非生产环境中，您可以设置一个触发器来执行对新表的日志记录。你需要5个领域像这样:

LogTime DateTime;
Table   Varchar2(50); -- Table Name
Action  Char;         -- Insert, Update or Delete
OldRec  Blob;         -- Concatenate all your field Values
NewRec  Blob;         -- Ditto

The Beauty of this is that you can select all the OldRecs and NewRecs for a given timespan into text files. A comparison tool will assist by highlighting your changes for you.

这样做的好处是，您可以为给定的timespan选择所有的OldRecs和NewRecs。比较工具可以帮助您突出您的更改。

Any help ?

任何帮助吗?

#1