I have to store data like articles into a mysql database and if an article is modified, I have to save the old version as well to make it possible to restore it. I have found some similar questions and posts on this topic, but I am not sure, which solution is the best to solve the problem.

我必须将类似文章的数据存储到mysql数据库中，如果文章被修改，我还必须保存旧版本，以便恢复它。我在这个主题上发现了一些类似的问题和帖子，但我不确定，哪种解决方案最能解决问题。

Here is the basic table "articles" for better understanding:

以下是更好理解的基本表格“文章”：

articles (id, name, text)

文章（身份证，姓名，文字）

For me, there are two different approaches for this:

对我来说，有两种不同的方法：

Approach 1

Store the data and every version of an article in the table "articles" and add the columns "version" and "status". In version i store the incremented version number of the article. The active article gets the "status" 1 and the others the "status" 2.

将数据和文章的每个版本存储在“文章”表中，并添加“版本”和“状态”列。在版本i中存储文章的递增版本号。活动文章获得“状态”1，其他文章获得“状态”2。

Pro's:

Pro的：

Only one table is needed
只需要一张桌子
A new version is an insert of the new data and only an update of the "status"-column of the old one
新版本是新数据的插入，只是旧状态的“状态”列的更新

Con's

反对的

Very large tables (maybe slower queries???)
非常大的表（可能是较慢的查询???）

Approach 2

Add the field "version" to "articles" and store only the active data into the table "articles". Old versions of the data is stored / moved to the new table "articles_versioned".

将“version”字段添加到“articles”中，并仅将活动数据存储到表“articles”中。旧版本的数据被存储/移动到新表“articles_versioned”。

Pro's:

Pro的：

Only the actual valid data is in the table "articles"
只有实际有效数据在表“文章”中

Con's

反对的

Dublication of tables
表的出版物

So. Have I forgotten a good aproach? How to deal with related data in other tables (like images, etc.)?

所以。我忘记了一个好的方法吗？如何处理其他表格中的相关数据（如图像等）？

1 个解决方案

#1

My choice would be a variation of approach 2. Bold indicates fields in the primary key.

我的选择是方法2的变体。粗体表示主键中的字段。

You insert every article in a table articles_versioned (id, timestamp, name, text)
您在表tables_versioned（id，timestamp，name，text）中插入每篇文章
Your second table is articles (id, timestamp, [name, text]). Note how timestamp is not primary; name and text may be replicated, or you may use a join with articles_versioned (which will be fast since id and timestamp are the articles_versioned primary key)
你的第二个表是文章（id，timestamp，[name，text]）。注意时间戳不是主要的;可以复制名称和文本，或者您可以使用articles_versioned的连接（由于id和timestamp是articles_versioned主键，因此会很快）
articles_versioned has a trigger on insert that takes the just inserted row and replicates it on articles
articles_versioned在insert上有一个触发器，它接受刚插入的行并在文章上复制它
To restore a specific version of an article you modify the articles table.
要还原特定版本的文章，请修改文章表。

The advantages of this approach are:

这种方法的优点是：

You get for free another information (the date and time of the article) in your table, that you may need anyway
您可以免费获得表格中您可能需要的其他信息（文章的日期和时间）
You do not need to query the database to get the current date. If you use version, you have to.
您无需查询数据库即可获取当前日期。如果你使用版本，你必须。
Your code doesn't have to insert the article in two tables. You simply insert in articles_versioned and read from articles, the db takes care of migrating data as you insert it via the trigger, avoiding any consistency problems.
您的代码不必将文章插入两个表中。您只需在articles_versioned中插入并从文章中读取，db就会在您通过触发器插入数据时进行迁移，从而避免出现任何一致性问题。

Con's

反对的

In an heavily concurrent environment, two versions may be inserted at the very same time, so one of them may fail. This shouldn't be a problem when inserting user-written articles (it is highly unlikely given precision of timestamps these days). If you don't specify the timestamp in your INSERT statement, but instead you set the datetime field to have the current time as a default value, you may avoid this problem entirely.
在高度并发的环境中，可能同时插入两个版本，因此其中一个版本可能会失败。插入用户编写的文章时，这不应该是一个问题（这些天给定时间戳的精度极不可能）。如果未在INSERT语句中指定时间戳，而是将datetime字段设置为将当前时间作为默认值，则可以完全避免此问题。

To answer the rest of your question. Approach 1 will not lead to longer queries as long as you add an index on status. This makes sense only if you tend to have many different versions of each article; as long as you have 2 versions per article on average or less, the index will only slow you down, and approach 2 would not be sensibly faster anyway (altough I'd still recommend my approach because it simplyfies code, since restoring a version does not require switching status for two rows).

回答你的其余问题。只要在状态上添加索引，方法1就不会导致更长的查询。只有当你每篇文章都有不同的版本时才有意义;只要你平均每篇文章有2个版本或更少，索引只会减慢你的速度，而且方法2也不会明显更快（尽管我仍然推荐我的方法，因为它简单地代码，因为恢复版本确实不需要两行的切换状态）。

Related resources, like images, should follow a similar versioning. I assume you are saving them on the filesystem; instead of saving them with their real name, use a table (id, image_name) to give to each image an id, then save the image as -id-.jpg. The image_name field will make you able to know what the original file name was (if you care about that). This way you can version images the same way as you version articles, and in articles you would use something like <img src="-id-.jpg">, that you know will remain available forever.

相关资源（如图像）应遵循类似的版本控制。我假设你将它们保存在文件系统上;而不是使用真实姓名保存它们，使用表（id，image_name）为每个图像提供一个id，然后将图像保存为-id-.jpg。 image_name字段将使您能够知道原始文件名是什么（如果您关心它）。通过这种方式，您可以像版本文章一样对图像进行版本控制，在文章中您将使用类似的文章，您知道它们将永远可用。

mysql数据版本控制系统的最佳实践

Approach 1

Approach 2

1 个解决方案

#1

更多相关文章

随机推荐