这个使用EAV表的“软删除”解决方案是否存在问题？ [英] Are there problems with this 'Soft Delete' solution using EAV tables?
问题描述
我已经阅读了一些关于在表中设置 deleted_at 字段的丑陋的一些信息，以表示一行已被删除。
即是
http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/
是否有任何潜在的问题从您要删除的表中取出一排，并将其转动到某些EAV表中？
例如。
<让我说我有两个表
删除和 deleted_row 分别描述如下。 mysql>描述删除;
+ ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- +
|字段|类型|空| Key |默认|额外|
+ ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- +
| id | int（11）| NO | PRI | NULL | auto_increment |
| tablename | varchar（255）| YES | | NULL | |
| deleted_at |时间戳| YES | | NULL | |
+ ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- +
mysql>描述deleted_rows;
+ -------- + -------------- + ------ + ----- + -------- - + ---------------- +
| Field |类型|空| Key |默认|额外|
+ -------- + -------------- + ------ + ----- + -------- - + ---------------- +
| id | int（11）| NO | PRI | NULL | auto_increment |
|实体| int（11）| YES | MUL | NULL | |
|名称| varchar（255）| YES | | NULL | |
|值| blob | YES | | NULL | |
+ -------- + -------------- + ------ + ----- + -------- - + ---------------- +
现在您希望从表中删除一行，然后将其从表中删除，然后将其插入到这些表中。
已删除
+ ---- + ----------- + --------------------- +
| id | tablename | deleted_at |
+ ---- + ----------- + --------------------- +
| 1 |产品| 2011-03-23 00:00:00 |
+ ---- + ----------- + --------------------- +
deleted_row
+ ---- + -------- + ------------- + -------------- ----------------- +
| id |实体|名称|值|
+ ---- + -------- + ------------- + ----------------- -------------- +
| 1 | 1 |标题|一个伟大的产品|
| 2 | 1 |价格| 55.00 |
| 3 | 1 |说明|你猜到了...这很棒|
+ ---- + -------- + ------------- + ----------------- -------------- +
我看到的几件事蝙蝠。
- 您需要使用应用程序逻辑
来做枢轴（Ruby，PHP，Python，
等） - 表可能会变得非常大
，因为我使用blob来处理
行值的未知大小
您是否看到这种类型的软删除有其他明显的问题？
为什么不用存档表镜像你的表？
创建表mytable（
col_1 int
，col_2 varchar（100）
，col_3 date
，主键（col_1）
）
创建表mytable_deleted（
delete_id int not null auto_increment
，delete_dtm datetime not null
- 所有原始列
，col_1 int
，col_2 varchar（100）
，col_3日期
，索引（col_1）
，主键（delete_id）
）
然后在删除之前，简单地在表上添加on-delete-triggers，将当前行插入镜像表中？您可以使用数据字典生成表并触发代码。
请注意，我可能不想在归档表中的原始主键（col_1）上有一个唯一的索引，因为如果使用自然键，您可能会最终删除同一行两次。除非你打算连接应用程序中的归档表（为了撤消目的），你可以完全删除索引。此外，我添加了删除（deleted_dtm）的时间和可用于删除删除（hehe）行的代理键。
您还可以考虑在deleted_dtm上对归档表进行范围分区。这样可以轻松地从表中清除数据。
I've read some information about the ugly side of just setting a deleted_at field in your tables to signify a row has been deleted.
Namely
http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/
Are there any potential problems with taking a row from a table you want to delete and pivoting it into some EAV tables?
For instance.
Lets Say I have two tables deleted and deleted_row respectively described as follows.
mysql> describe deleted;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tablename | varchar(255) | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
mysql> describe deleted_rows;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| entity | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| value | blob | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
Now when you wanted to delete a row from any table you would delete it from the table then insert it into these tables as such.
deleted
+----+-----------+---------------------+
| id | tablename | deleted_at |
+----+-----------+---------------------+
| 1 | products | 2011-03-23 00:00:00 |
+----+-----------+---------------------+
deleted_row
+----+--------+-------------+-------------------------------+
| id | entity | name | value |
+----+--------+-------------+-------------------------------+
| 1 | 1 | Title | A Great Product |
| 2 | 1 | Price | 55.00 |
| 3 | 1 | Description | You guessed it... it's great. |
+----+--------+-------------+-------------------------------+
A few things I see off the bat.
- You'll need to use application logic to do the pivot (Ruby, PHP, Python, etc)
- The table could grow pretty big
because I'm using
blobto handle the unknown size of the row value
Do you see any other glaring problems with this type of soft delete?
Why not mirror your tables with archive tables?
create table mytable(
col_1 int
,col_2 varchar(100)
,col_3 date
,primary key(col_1)
)
create table mytable_deleted(
delete_id int not null auto_increment
,delete_dtm datetime not null
-- All of the original columns
,col_1 int
,col_2 varchar(100)
,col_3 date
,index(col_1)
,primary key(delete_id)
)
And then simply add on-delete-triggers on your tables that inserts the current row in the mirrored table before the deletion? That would provide you with dead-simple and very performant solution.
You could actually generate the tables and trigger code using the data dictionary.
Note that I might not want to have a unique index on the original primary key (col_1) in the archive table, because you may actually end up deleting the same row twice over time if you are using natural keys. Unless you plan to hook up the archive tables in your application (for undo purposes) you can drop the index entirely. Also, I added the time of delete (deleted_dtm) and a surrogate key that can be used to delete the deleted (hehe) rows.
You may also consider range partitioning the archive table on deleted_dtm. This makes it pretty much effortless to purge data from the tables.
这篇关于这个使用EAV表的“软删除”解决方案是否存在问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
