修复存量数据技巧之【存储过程】

前言

开发迭代的过程，你是否经常遇到需要考虑：增量、存量数据如何处理的场景？比如一个新功能，对增量的数据不用过多考虑，不过对存量的数据，需要处理后才能满足升级需求。

背景

这里简单记录下在修复存量数据过程中可能会用到的一个技巧【存储过程】。存储过程一般很少用，笔者写过和接触过的存储过程寥寥无几。有的领导在编码规则中会专门强调尽量不用或者少用存储过程。不过，在修复数据，数据量不大又需要遍历的时候，走走存储过程还是蛮方便的。

场景

数据库前期业务未考虑幂等，未对关键的几个字段比如：company_id、employee_id, type三个字段来做唯一键约束，导致同一个company_id、emloyee_id、type的数据出现多条记录，从业务角度来看，应该是只有一条。那么我们的开发思路一般是这样的：

一、代码层面
在对象持久化之前，先按照幂等条件：company_id、employee_id、type查一下，类似这样的一个方法：

	BusinessEntity	queryAll(company_id,employee_id,type);

若BusinessEntity !=null ，则给业务提示；若为null，则入库。

二、数据库层面
一做的是代码层面的控制住，不过也不排除直接通过SQL来更改数据库或者其他方法不小心开了“入口”，也会导致违背幂条件的脏数据进来。所以从数据库层面也要做约束，方法就是加唯一索引。

alter table business_entity add unique index idx_cid_eid_type(company_id,employee_id,type);

如果这是一张新表，是你新开发的功能，那就很好办，直接加索引，因为后期数据都是在索引之后进来的，肯定是满足唯一约束条件的。然而，从上面场景角度来看，是后期要给已存在的表加此业务幂等条件。那么因为库里已经有了不少“脏数据“，无法添加该索引，因而需要手动处理掉脏数据，然后在没有新数据进来的情况下（如何确保，也可以写一篇文章），添加该索引。

三、脏数据处理

在处理脏数据的时候，我们需要先查一下哪些脏数据需要处理，比如类似这样：

步骤1

select id,company_id,employee_id,`type`, count(1) total 
from business_entity 
group by 
employee_id,company_id 
having 
total >=2

那么在这里有多种技巧可以使用，不同的技巧决定了修复数据的复杂度。

思路1：查出要保留的id,然后把不等于该id的删除（本篇博客要介绍的）

思路2：查出要删除的ids，然后删除。要删除的ids,通过concat来拼接。
	这个的好处是不用在关心company_id和type了。

今天就暂不care思路2，按照思路1的方式继续：
那么需要需要在查出来的脏数据的基础上，构造这样的处理语句：

步骤2

delete from business_entity where 
company_id = '' and  #待传参数
employee_id = '' and  #待传参数
and type=1 #这里先写固定，便于理解
and id !=  #{} #这里待传参数

到这里思路就明确了，就是把步骤1查出来的东西动态的赋值到步骤2的语句参数中，执行就可以了。因为这里的参数是动态的，所以要支持遍历。

因为是脏数据嘛，一般量不打。如果数据量少，1K以内，直接手动构造类似步骤2这样的语句，通过笔者分享的VIM块编辑技巧，可以很方便的构造几千、几万条这种语句，并且把对应的参数复制进去，然后批量执行就可以了。当然每次都这么做，步骤比较零碎，可以考虑存储过程。

存储过程通用模板

所有类似的脏数据遍历处理，都可以用下面这个模板：

drop temporary table duplicate_cert;
DROP PROCEDURE IF EXISTS duplicateCertProcess;
 
create temporary table duplicate_cert
select min(c.id) id,c.employee_id,c.company_id,c.create_time,count(1) as total 
from db.cert c WHERE c.types = 10 and c.employee_id is not null
group by c.employee_id,c.company_id 
having total >1;
#+++++++++++++++++++++++++存储过程(Start) ++++++++++++++++++++++
# 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS duplicateCertProcess;
# 创建存储过程
DELIMITER //
CREATE PROCEDURE duplicateCertProcess()
BEGIN
DECLARE s int(10) DEFAULT 0;
DECLARE var_id int(10);
DECLARE var_employee_id varchar(255);
DECLARE var_company_id varchar(255);

#定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select id,employee_id,company_id from duplicate_cert;
#声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
  
#打开游标
open report;
  
# 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致

fetch report into var_id,var_employee_id,var_company_id;
#当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
  
# 执行业务逻辑
delete from db.cert 
where company_id = var_company_id and employee_id = var_employee_id and id !=var_id;

#当s等于1时表明遍历以完成，退出循环
fetch report into var_id,var_employee_id,var_company_id;
end while;
#关闭游标
close report;
END;
//
DELIMITER ;
#+++++++++++++++++++存储过程(End) ++++++++++++++++++++++
-- 执行存储过程
call duplicateCertProcess();

这里需要注意一下min(id)，这个目的是为了拿到想保留的那条数据记录。因为group by之后是无需的，所以拿到想要保留的那条数据对应的id,一般按照时间排序后获取想要的那个id.这块也值得单独写一篇博客介绍。

这个存储过程就是起到遍历步骤1，拿到company_id、employee_id、type三个参数，然后依次赋值给动态的变量，在传给步骤2的语句，最后重置参数，继续遍历，直到结束。

总结

方式	优点	缺点
存储过程修复脏数据	简单，相比手动凑delete语句，这个更加通用，更加抽象，复用性更高。改一下语句和参数就可以了。	如果数据量很大1K以上，存储过程执行是蛮慢的，一次几百条
手动构造SQL语句	执行快速	基本没有复用性，繁琐

好了，今天就介绍到这里。再会。