一次生产上的事故总结

创建一个存储过程:

/*
Navicat MySQL Data Transfer

Source Server         : 
Source Server Version : 
Source Host           : 
Source Database       : 

Target Server Type    : MYSQL
Target Server Version : 50709
File Encoding         : 65001

Date: 2018-07-02 14:29:21
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Procedure structure for deleteUserBill
-- ----------------------------
DROP PROCEDURE IF EXISTS `deleteUserBill`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `deleteUserBill`(
	userId varchar(63),
	billSeqNum int(11)
	)
begin 
	DECLARE currMoney double default 0;
	DECLARE currType varchar(63) DEFAULT "";
	DECLARE currId varchar(63) default '';

	SELECT `id`,`money` ,`type` into currId,currMoney,currType  
	from `user_bill` ub 
	where ub.user_id = userId and ub.seq_num = billSeqNum ; 

	if(currMoney is null or currMoney='') then 
		set currMoney=0;
	end if ;

	if(currType is null or currType='') then 
		set currType="";
	end if ;

	if(currType='ti_balance') then 
		update `user_bill` set seq_num = seq_num-1, balance = round(balance - currMoney,2)  
		where user_id = userId and seq_num > billSeqNum;
	end if ;

	if(currType='to_balance') then 
		update `user_bill` set seq_num = seq_num-1, balance = round(balance + currMoney,2)  
		where user_id = userId and seq_num > billSeqNum;
	end if;
	delete from `user_bill` where id = currId;

end
;;
DELIMITER ;

调用存储过程:

call deleteUserBill("18201575935",53);

起因:一次批量文件处理的误操作,导致生产数据库产生了4000多条的错账记录;

处理办法:

1. 删除掉插入的错误数据流水记录;

2. 将用户流水的账户余额和seq_num调回原有正确的数据;

3. 使用存储过程来调账,大大简化了工作量,并且易于维护.