MySQL数据修复

开发过程经常会遇到数据库数据修复的问题，常见的就是单条数据修复，这个也是最简单的，直接根据查询条件直接更新数据即可，但还有比较复杂的数据修复，例如修复整张表中有异常的数据，此时仅仅根据单纯的、可见的查询条件是十分有限的，此时就需要稍微复杂的查询条件

有如下场景：

三张表，sale_order、sale_order_extend，pay_class_type，从名称能够看出sale_order_extend作为sale_order的拓展表，使用sale_order_id与sale_order关联，pay_class_type作为支付表，储存各种支付信息，按照类型存储，使用object_id与sale_order中的主键id关联；表结构如下：

CREATE TABLE `sale_order`(
id BIGINT(20) NOT NULL,
order_no VARCHAR(32) NOT NULL DEFAULT '' ,
order_type TINYINT(4) COMMENT '订单类型',
order_state TINYINT(4) COMMENT '订单状态',
update_time DATE 
);

CREATE TABLE `sale_order_extend`(
id BIGINT(20) NOT NULL,
sale_order_id BIGINT(20) NOT NULL,
change_box_barcode TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否更换箱唛 1-更换 0-不更换',
change_product_barcode TINYINT(1) NOT NULL DEFAULT 0 COMMENT '更换箱唛数量',
product_barcode_quantity INT(11) NOT NULL DEFAULT 0 COMMENT '是否更换条码 1-更换 0-不更换',
box_barcode_quantity  INT(11) NOT NULL DEFAULT 0 COMMENT '更换条码数量'
)

CREATE TABLE `pay_info`(
id BIGINT(20) NOT NULL,
object_id BIGINT(20) NOT NULL DEFAULT 0 COMMENT '关联表主键id',
pay_class_id TINYINT(4) DEFAULT 0 COMMENT '费用类型 58-箱唛费用，59-条码费用',

)

由于程序错误，订单类型为11、状态为5的订单，时间在2021-09-28到2021-10-14之间的数据，在保存箱唛数量与条码数量时，两个字段的值被互换了，但是否更换箱唛与条码这两个字段没有互换；选择是的时候才会生成相应的费用；正常情况下，箱唛数量大于条码数量

有以下几种方式修复数据

使用存储过程：

delimiter $$;
drop procedure if exists repair_fee; 
create procedure repair_fee()
BEGIN

   DECLARE flag int DEFAULT 0;
   DECLARE order_id int DEFAULT 0;
   DECLARE num1 INT DEFAULT 0;
   DECLARE num2 INT DEFAULT 0;
   DECLARE pay_detail_1 INT DEFAULT 0;
   DECLARE pay_detail_2 INT DEFAULT    0;
 
   SET @order_type=11;
   SET @order_state=5;
   SET @pay_class_1=59;
   SET @pay_class_2=58;
   
   DECLARE order_ids CURSOR FOR (SELECT a.id,e.product_barcode_quantity,e.box_barcode_quantity FROM sale_order a LEFT JOIN sale_order_extend e ON e.sale_order_id = a.id WHERE a.update_time > '2021-9-28' AND a.update_time < '2021-10-14' AND order_state IN(@order_state) AND order_type IN (@order_type) AND (e.change_box_barcode = 1 OR e.change_product_barcode = 1) AND e.product_barcode_quantity < e.box_barcode_quantity); 
   DECLARE CONTINUE handler for not found set flag=1;
   
   
   OPEN order_ids;  
         fetch order_ids into order_id,num1,num2;
            while flag <> 1 do
               UPDATE sale_order_extend SET    box_barcode_quantity=num2,product_barcode_quantity=num1 WHERE sale_order_id=order_id;               
               SELECT id as pay_detail_1 FROM    pay_info WHERE    object_id=order_id AND pay_class_id=@pay_class_1;
               SELECT id as pay_detail_2 FROM    pay_info WHERE    object_id=order_id AND pay_class_id=@pay_class_2;
               IF(pay_detail_1) then
                   UPDATE pay_info SET pay_class_id=@pay_class_2 WHERE    id=pay_detail_1;
               END IF;
                  IF(pay_detail_2) then
                   UPDATE pay_info SET pay_class_id=@pay_class_1 WHERE    id=pay_detail_2;
               END IF;
                  
         fetch order_ids into order_id,num1,num2;
         END while;
   close order_ids;
END$$;
# 执行存储过程
call repair_fee();
// 删除储存过程
drop repair_fee;

使用存储过程修复数据，可以完成较为复杂的数据修复，数据修复完成之后，也不会留下多余的表

使用临时表（update inner join使用）：
不需要计算中间的查询条件时可以使用

set @order_type=11;

DROP TABLE IF EXISTS sale_extend_tmp211014;
create table sale_extend_tmp211014 
SELECT a.id, e.id as extend_id, 
e.box_barcode_quantity, e.product_barcode_quantity
FROM `sale_order` a 
INNER JOIN sale_order_extend e ON e.sale_order_id = a.id 
WHERE a.update_time > '2021-09-28' AND a.update_time < '2021-10-14' 
AND sale_order_state = 5 AND order_type IN (@order_type) 
AND e.change_box_barcode = 1 OR e.change_product_barcode = 1 
AND e.product_barcode_quantity < e.box_barcode_quantity; 

update sale_extend_tmp211014 t 
inner join sale_order_extend e on e.id=t.extend_id 
set e.box_barcode_quantity=t.product_barcode_quantity, 
e.product_barcode_quantity=t.box_barcode_quantity;

set @b_class_id=58;
set @p_class_id=59;
update sale_extend_tmp211014 t 
inner join paid_info p on p.object_id=t.id and p.pay_class_id in (@b_class_id, @p_class_id) 
set p.pay_class_id=if(p.pay_class_id=@b_class_id, @p_class_id, @b_class_id);

使用临时表，也能完成数据修复，但所能修复的数据不会很复杂，而且也会产生临时表

不使用临时表
不需要计算中间的查询条件时可以使用，如下需求

a：需求

A表和B表的表结构相同，A表是历史表，B表是增量数据表；想要根据关联条件更新A表中的数据。

b：表结构

CREATE TABLE `A` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bid` bigint(20) NOT NULL ,
  `sid` bigint(20) NOT NULL ,
  `grid` bigint(20) NOT NULL ,
  `age` bigint(20) NOT NULL ,
  `pv` bigint(20) NOT NULL ,
  `uv` bigint(20) NOT NULL ,
  `pay_count` bigint(20) NOT NULL,
  `charge_amount` bigint(20) NOT NULL ,
  `last_pay_count` bigint(20) NOT NULL ,
  `last_charge_amount` bigint(20) NOT NULL ,
  `ftime` bigint(20) NOT NULL ,
  `dtime` bigint(20) NOT NULL DEFAULT '0' COMMENT '详细时间(yyyyMMddHH)'
  PRIMARY KEY (`id`,`ftime`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

c：注意

只更新A表的部分字段：pv、uv和pay_amount；
确定唯一一条记录的关联字段是：bid、sid、dtime、qrid

d：实现

#使用inner join实现    
UPDATE A as aa
INNER JOIN B as bb ON bb.bid = aa.bid AND bb.sid=aa.sid AND bb.grid=aa.grid
SET aa.pv=bb.pv,aa.uv=bb.uv,aa.pay_amount=bb.pay_amount
WHERE aa.dtime=? AND aa.bid=? ;
        
#使用exists实现    
UPDATE A aa 
SET  aa.pv = (SELECT bb.pv 
        FROM   B bb 
        WHERE  bb.bid = aa.bid AND bb.sid = aa.sid AND bb.grid = aa.grid), 
     aa.uv = (SELECT bb.uv 
        FROM   B bb 
        WHERE  bb.bid = aa.bid  AND bb.sid = aa.sid AND bb.grid = aa.grid), 
     aa.pay_amount = (SELECT bb.pay_amount 
            FROM   B bb 
            WHERE  bb.bid = aa.bid AND bb.sid = aa.sid AND bb.grid = aa.grid) 
     WHERE  EXISTS (SELECT 1
                        FROM   B bb 
            WHERE  bb.bid = aa.bid AND bb.sid = aa.sid  AND bb.grid = aa.grid) 
     AND aa.dtime = ? AND aa.bid = ?;