先看一个实际的案例:

    某应用有一个应用收益功能，其中最重要的收益详细它对应着后台数据库的账单表.业务部门的业务员可以随时添加对应的财务信息，但是其中某位业务员因为误操作删除了一批重要收益的信息。对于统计造成了很大破坏。

    现在需要解决这个问题，并且要避免以后出现类似的问题具体如何解决？

    第一种方式：当事情已经发生，而后端架构师并没有对表进行特定的设计的时候只能找数据库数据恢复团队解决此问题，或者是使用数据库的快照恢复过来。然后对于业务员设置权限，回收所有删除权限。或者是对访问数据库的用户回收delete权限.

    第二种方式：保留业务员的删除权限，而数据库内不是实际删除。引入"标记字段"。

    第二种方式也是去掉数据Delete的主要意义和实现方式。

    数据库以PostgreSQL9.5为示例:

    先创建两个示例收益表 profit_a:

create table profit_a(    id serial primary key,    title character varying(255),    content character varying(500));

profit_b:

create table profit_b(    id serial primary key,    title character varying(255),    content character varying(500)    is_remove boolean default('false'));

    在profit_b中我引入了is_remove字段，用来标记是否删除此条数据，默认是false，如果是删除数据就修改为false。

    分别插入两条示例数据:

insert into profit_a(title,content) values('3月份收益','三月份收益有所 有增加'),('四月份收益','四月份收益下跌了20%');insert into profit_b(title,content) values('3月份收益','三月份收益有所 有增加'),('四月份收益','四月份收益下跌了20%');

    数据对比:

eachma=# select * from profit_a; id |   title    |       content        ----+------------+----------------------  1 | 3月份收益  | 三月份收益有所有增加  2 | 四月份收益 | 四月份收益下跌了20%(2 rows)Time: 0.464 mseachma=# select * from profit_b; id |   title    |       content        | is_remove ----+------------+----------------------+-----------  1 | 3月份收益  | 三月份收益有所有增加 | f  2 | 四月份收益 | 四月份收益下跌了20%  | f(2 rows)Time: 0.486 ms

    对于profit_a表如果是业务员需要删除数据时只能删除这条数据，而profit_b表对于删除数据只需要修改is_remove的值为true即可。在查询的时候只需要在SQL语句后面加上一个where子句或者是and  is_remove=false；

    下面两个SQL语句的结果相同：

select * from profit_b where is_remove=false;#orselect * from profit_b where is_remove is false;

eachma=# select * from profit_b where is_remove is false; id |   title    |       content        | is_remove ----+------------+----------------------+-----------  1 | 3月份收益  | 三月份收益有所有增加 | f  2 | 四月份收益 | 四月份收益下跌了20%  | f(2 rows)

    这样对于业务员删除的数据虽然已经“删除”，但是却还保存在数据库中,对于误操作删除的数据恢复时只需要把is_remove的值设置为false即可恢复。这样既保证了业务的正常也保证数据的可恢复。

    对于上面的示例对于没有唯一约束的表设计不会有问题，但是对于设置了唯一约束的表使用这种方法就会出现键值冲突的错误，是不允许的成功执行提交的事务。但是在PostgreSQL,MySQL,Oracle等数据库中提供了部分索引(partial index)的功能。

    以用户表为例:

create table users(    id serial,    name character varying(20),    email character varying(50),    password character varying(32),    is_remove boolean default false    primary key(id));

    给email添加partial unique索引:

create unique index on users(email) where is_remove=false;

    数据插入演示:

eachma=# insert into users(name,email,password) values('test','eachma@eachma.com',md5('123'));INSERT 0 1eachma=# insert into users(name,email,password) values('test','eachma@eachma.com',md5('123'));ERROR:  duplicate key value violates unique constraint "users_email_idx"DETAIL:  Key (email)=(eachma@eachma.com) already exists.eachma=# select * from users; id | name |       email       |             password             | is_remove ----+------+-------------------+----------------------------------+-----------  1 | test | eachma@eachma.com | 202cb962ac59075b964b07152d234b70 | f(1 row)

    现在把id为1的is_remove设置为true，再测试效果.:

eachma=# update users set is_remove=true;UPDATE 1eachma=# insert into users(name,email,password) values('test','eachma@eachma.com',md5('123'));INSERT 0 1eachma=# select * from users; id | name |       email       |             password             | is_remove ----+------+-------------------+----------------------------------+-----------  1 | test | eachma@eachma.com | 202cb962ac59075b964b07152d234b70 | t  3 | test | eachma@eachma.com | 202cb962ac59075b964b07152d234b70 | f

    同样的再查询时只需要在SQL语句的最后面添加is_remove is true;即可。

    模拟登录SQL：

SELECT id,name FROM users WHERE email='eachma@eachma.com' AND password=md5('123') AND is_remove=false;eachma=# SELECT id,name FROM users WHERE email='eachma@eachma.com' AND password=md5('123') AND is_remove=false; id | name ----+------  3 | test(1 row)

    而且这种方式的索引对于表的数据过大时，部分索引所占的存储空间也比较小。

    我很建议大家不要在商业应用上面真实的删除数据，使用标记字段。这样也便于数据恢复。去掉CRUD中的"D"。

参考资料:

postgresql partial index:

partial-indexes: