I know of two ways to delete data from a database table
我知道有两种方法可以从数据库表中删除数据
- DELETE it forever
- Use a flag like isActive/isDeleted
永远删除它
使用像isActive / isDeleted这样的标志
Now the problem with isActive is that I have to track everywhere in my SQL queries that whether the record is active or not. Using DELETE however gets rid of the data forever.
现在isActive的问题是我必须在我的SQL查询中跟踪记录是否处于活动状态。但是,使用DELETE可以永久删除数据。
What would be the best way to backup this data?
备份这些数据的最佳方法是什么?
Assuming I have multiple tables in a database, should I have a common function which just backs everything up and stores it in another table (in XML probably?) or is there any other way.
假设我在数据库中有多个表,我应该有一个只支持所有内容的公共函数并将其存储在另一个表中(可能是XML格式吗?)还是有其他方法。
I am using MySQL but am curious about techniques used in other DBs as well.
我正在使用MySQL,但我对其他数据库中使用的技术感到好奇。
7 个解决方案
#1
13
Replace the table with a view that hides the inactive items.
将表替换为隐藏非活动项的视图。
Or write a trigger on DELETE that backs up the row to an archive table.
或者在DELETE上写一个触发器,将行备份到存档表。
#2
2
You could use a trigger that fires on deleting records to back them up into some kind of graveyard table.
你可以使用触发器来触发删除记录,将它们备份到某种墓地表中。
#3
2
You could use an isDeleted column and defien a view which selects all columns except isDeleted with the condition isDeleted=false. Then have all your stps work only with the view.
您可以使用isDeleted列并使用条件isDeleted = false来定义除了isDeleted之外的所有列的视图。然后让你所有的stps只适用于视图。
#4
1
You could maintain a history table, where you back the record up and time stamp
您可以维护一个历史记录表,在该表中备份记录和时间戳
#5
1
One of the biggest reasons for not deleting data is that it may be required for a relation - for example the the user may decide to delete an old customer from the database, but you still need the customer record because it is referenced by old invoices (which may have a much longer lifespan).
不删除数据的最大原因之一是关系可能是必需的 - 例如,用户可能决定从数据库中删除旧客户,但您仍然需要客户记录,因为它是由旧发票引用的(这可能有更长的寿命)。
Based on this the best solution is often the "IsDeleted" type of column, combined with a view (Quassnoi has mentioned partitioning, which can help with performance issues that might pop up due to a lot of invisible data).
基于此,最佳解决方案通常是“IsDeleted”类型的列,并结合视图(Quassnoi提到了分区,这可以帮助解决由于大量不可见数据而可能出现的性能问题)。
#6
0
You can partition your tables on the DELETED column and define the views which would include the condition:
您可以在DELETED列上对表进行分区,并定义包含条件的视图:
… AND deleted = 0
This will make the queries over the active data just as simple and efficient.
这将使对活动数据的查询同样简单有效。
#7
0
Well, if you were using SqlServer you can use triggers, which will allow you to move the record to a deleted table.
好吧,如果您使用SqlServer,则可以使用触发器,这将允许您将记录移动到已删除的表。
#1
13
Replace the table with a view that hides the inactive items.
将表替换为隐藏非活动项的视图。
Or write a trigger on DELETE that backs up the row to an archive table.
或者在DELETE上写一个触发器,将行备份到存档表。
#2
2
You could use a trigger that fires on deleting records to back them up into some kind of graveyard table.
你可以使用触发器来触发删除记录,将它们备份到某种墓地表中。
#3
2
You could use an isDeleted column and defien a view which selects all columns except isDeleted with the condition isDeleted=false. Then have all your stps work only with the view.
您可以使用isDeleted列并使用条件isDeleted = false来定义除了isDeleted之外的所有列的视图。然后让你所有的stps只适用于视图。
#4
1
You could maintain a history table, where you back the record up and time stamp
您可以维护一个历史记录表,在该表中备份记录和时间戳
#5
1
One of the biggest reasons for not deleting data is that it may be required for a relation - for example the the user may decide to delete an old customer from the database, but you still need the customer record because it is referenced by old invoices (which may have a much longer lifespan).
不删除数据的最大原因之一是关系可能是必需的 - 例如,用户可能决定从数据库中删除旧客户,但您仍然需要客户记录,因为它是由旧发票引用的(这可能有更长的寿命)。
Based on this the best solution is often the "IsDeleted" type of column, combined with a view (Quassnoi has mentioned partitioning, which can help with performance issues that might pop up due to a lot of invisible data).
基于此,最佳解决方案通常是“IsDeleted”类型的列,并结合视图(Quassnoi提到了分区,这可以帮助解决由于大量不可见数据而可能出现的性能问题)。
#6
0
You can partition your tables on the DELETED column and define the views which would include the condition:
您可以在DELETED列上对表进行分区,并定义包含条件的视图:
… AND deleted = 0
This will make the queries over the active data just as simple and efficient.
这将使对活动数据的查询同样简单有效。
#7
0
Well, if you were using SqlServer you can use triggers, which will allow you to move the record to a deleted table.
好吧,如果您使用SqlServer,则可以使用触发器,这将允许您将记录移动到已删除的表。