php 如何在 MySQL 中保留记录更新的历史记录？

How to keep history of record updates in MySQL?

提问by Proxium

I have to create a code in PHP that will allow me to keep the history of record updates in MySQL database so I can find by date an old revision.

我必须在 PHP 中创建一个代码，它允许我在 MySQL 数据库中保留记录更新的历史记录，以便我可以按日期找到旧版本。

Here is the example of what I actualy want to achive: http://en.wikipedia.org/w/index.php?title=Tunisia&action=history

这是我实际想要实现的示例：http: //en.wikipedia.org/w/index.php?title=Tunisia&action=history

The data are mostly Numbers that we record about the company to generate reports and to extract indices.

数据主要是我们记录的有关公司的数字，以生成报告和提取指数。

I plan to use codeigniter for it's simplicity and I'm looking for idea about a framework or an opensource project that use the same approach to keep history of modifications in the database.

我计划使用 codeigniter，因为它很简单，我正在寻找关于使用相同方法来保留数据库中修改历史的框架或开源项目的想法。

回答by wimvds

The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...

最简单的解决方案（取决于您的特定需求）可能是在您的表中添加一个 on update/insert/delete 触发器，这样您就可以在插入/更新/删除数据时执行额外的日志记录。这样，甚至对数据库的手动干预也将被涵盖......

Check http://dev.mysql.com/doc/refman/5.1/en/triggers.htmlfor more information.

查看http://dev.mysql.com/doc/refman/5.1/en/triggers.html了解更多信息。

回答by reko_t

One simple way to keep version history is to create basically an identical table (eg. with _version suffix). Both of the tables would have a version field, which for the main table you increment for every update you do. The version table would have a composite primary key on (id, version).

保留版本历史的一种简单方法是创建一个基本相同的表（例如，带有 _version 后缀）。这两个表都有一个版本字段，对于主表，您每次更新都会增加该字段。版本表将在 上有一个复合主键(id, version)。

Whenever you do an update on the actual table, you also INSERT a new row in the version table with duplicate data. Whenever you want to find the version history, all you need to do is something such as SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.

每当您对实际表进行更新时，您还会在版本表中插入一个包含重复数据的新行。每当您想查找版本历史记录时，您只需要做的就是诸如SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.

If you use something like Doctrine ORM, it has a behavior that does this for you automatically via event listeners. You can check it out here: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors:versionable

如果您使用 Doctrine ORM 之类的东西，它会通过事件侦听器自动为您执行此操作。你可以在这里查看：http: //www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors: versionable

回答by jasiek

There is an open source (MIT license) framework to build CRM and ERP type of database driven web applications. You can download EPESIfrom http://epe.si/available also on SourceFroge: http://sourceforge.net/projects/epesi/

有一个开源（MIT 许可）框架来构建 CRM 和 ERP 类型的数据库驱动的 Web 应用程序。您可以从http://epe.si/下载EPESI，也可以在 SourceFroge 上获得：http: //sourceforge.net/projects/epesi/

EPESI's CRUDengine - the Record Browser- has a very efficient record history as well as other features like advanced permission system (down to a field level) and much more.

EPESI 的 CRUD引擎 -记录浏览器- 具有非常高效的记录历史以及其他功能，例如高级权限系统（低至字段级别）等等。

A single recordset stores data in as many as 10 tables and it is database agnostic (uses PHPAdoDB). A table called recordset_datastores "raw" data and history of changes is stored in 2 additional tables: recordset_edit_historyand recordset_edit_history_data.

单个记录集将数据存储在多达 10 个表中，并且它与数据库无关（使用 PHPAdoDB）。名为recordset_data的表存储“原始”数据，更改历史记录存储在 2 个附加表中：recordset_edit_history和recordset_edit_history_data。

Record's edit_history has the following structure:

Record 的 edit_history 结构如下：

• ID (Primary key for this table, index)
• Edited on (timestamp: 2008-05-14 15:18:15)
• Edited by: (user ID)

• ID（此表的主键，索引）
• 编辑于（时间戳：2008-05-14 15:18:15）
• 编辑：（用户名）

Record's edit_history_data has the following structure:

Record 的 edit_history_data 结构如下：

• edit_id = ID from edit history
• field = the name of the field that was changed
• old_value = value of the field that changed.

• edit_id = 编辑历史记录中的 ID
• field = 更改的字段的名称
• old_value = 更改的字段的值。

It is a very fast and efficient engine and it stores changes not on the record but on a single field level.

它是一个非常快速和高效的引擎，它不是在记录上而是在单个字段级别上存储更改。

回答by Nicolò Martini

You have to use an extra layer between your application and the db. You can make a very simple on your own (instead of direct calling mysql_queryyou call a function made by you that wraps it and that tracks updates), or use some existing ORM. In pseudo-code

您必须在应用程序和数据库之间使用额外的层。您可以自己制作一个非常简单的（而不是直接调用mysql_query您调用由您制作的包装它并跟踪更新的函数），或者使用一些现有的 ORM。在伪代码中

my_mysql_query($query){
    if($query is an update){
        //Log stuff before query
    }
    $r = mysql_query($query);

    if ($r && $query is an update){
        //Log stuff after query
    }
    return $r;
}

And then in your application you call my_mysql_queryinstead of mysql_query. You can check if the table is the one you want to track and you can copy the row in a copy of the original table.

然后在您的应用程序中调用my_mysql_query而不是mysql_query. 您可以检查该表是否是您要跟踪的表，并且可以在原始表的副本中复制该行。

If you use Doctrine ORM, you can use its Event Listenersto get what you want.

如果你使用Doctrine ORM，你可以使用它的事件监听器来获得你想要的。

回答by Niels Bom

If I understand correctly, you're saving just a "number" in a specific table, and you want the revision history of that number?

如果我理解正确，您只是在特定表格中保存一个“数字”，并且想要该数字的修订历史？

I'd say: write your datamodel so that it contains a history of that number. So you not only record the latest value of that number but also keep any previous values.

我会说：编写您的数据模型，使其包含该数字的历史记录。因此，您不仅要记录该数字的最新值，还要保留任何以前的值。

A simple way of doing this is by having a table with the following fields:

一个简单的方法是创建一个包含以下字段的表：

• id
• companyId
• number
• timestamp

• ID
• 公司编号
• 数字
• 时间戳

If you want to know the current number, just do

如果您想知道当前号码，只需执行

SELECT * FROM table WHERE companyId = X ORDER BY timestamp DESC LIMIT 1

If you want to see all revisions just do:

如果您想查看所有修订，请执行以下操作：

SELECT * FROM table WHERE companyId = X

回答by thecoshman

That is not logging the actual SQL updates. It is logging the updates of the data. It will in effect be storing every revision of the page, and just providing the latest one by default. The SQL code that was used for it was not stored.

这并没有记录实际的 SQL 更新。它正在记录数据的更新。它实际上会存储页面的每个修订版，并且默认情况下只提供最新的修订版。没有存储用于它的 SQL 代码。

Thus you will have to take a similar approach. Every time the data changes, you will need to work out how the data has changed and store this 'patch' data. You will probably be best storing the latest version, rather then having to work through all the patches to get to it. This means you will be able to see something like

因此，您将不得不采取类似的方法。每次数据更改时，您都需要弄清楚数据是如何更改的并存储此“补丁”数据。您可能最好存储最新版本，而不必通过所有补丁来获取它。这意味着您将能够看到类似

! created file
* added data to cell D4 'product descript' D5 'set of pens' E5 '￡5.99'
* added data to cell D6 'toaster' E5 '￡10'
& changed data in cell D4 'Product Description'

Each of these changes would need to be stored with a time stamp or when they where done. You will also need to work out your own scheeme for storing the changes to data.

这些更改中的每一个都需要与时间戳或完成时间一起存储。您还需要制定自己的方案来存储对数据的更改。

Another simpler solution, would be to use a wiki engine, that provides all of the functionality you want, if you are willing to work in a web page. You may need to spend some time to make it work better for your needs, letting people edit it from a more finished view, rather then a raw wiki.

另一个更简单的解决方案是使用 wiki 引擎，如果您愿意在网页中工作，它可以提供您想要的所有功能。您可能需要花一些时间让它更好地满足您的需求，让人们从更完整的视图而不是原始 wiki 编辑它。