如何捕获校验错误信息并将错误记录拼接存入error_log表
步骤1:创建error_log表
建议额外新增报错时间字段方便后续排查,MySQL建表语句参考:
CREATE TABLE error_log ( sequence_no INT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键', error_message VARCHAR(255) NOT NULL COMMENT '触发的规则错误描述', error_data TEXT NOT NULL COMMENT '报错记录全字段拼接内容', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '报错时间' );
步骤2:核心逻辑实现
整体通用逻辑:遍历每条待校验记录→逐条匹配校验规则→触发规则后生成错误信息、拼接全字段内容、更新原记录状态、写入错误日志表。以下给出两种常见实现方式:
方式1:业务代码层实现(以Python操作MySQL为例)
import pymysql import json # 数据库连接配置 db = pymysql.connect(host='数据库地址', user='账号', password='密码', database='库名') cursor = db.cursor() # 取出业务表所有待校验记录,假设业务表名为item_table cursor.execute("SELECT * FROM item_table") records = cursor.fetchall() # 获取业务表字段名,用于后续拼接错误数据 col_names = [desc[0] for desc in cursor.description] for record in records: record_dict = dict(zip(col_names, record)) error_msg = None item_no = str(record_dict.get('item_no', '')) item_desc = str(record_dict.get('item_description', '')) # 校验规则匹配 if not item_no: error_msg = 'Item No is null so the status is updated to V' elif len(item_no) != 5: error_msg = f'Item No length is {len(item_no)}, required 5 characters so the status is updated to V' elif not item_desc: error_msg = 'Item Description is null so the status is updated to V' elif len(item_desc) >= 240: error_msg = f'Item Description length is {len(item_desc)}, must be less than 240 characters so the status is updated to V' # 触发错误后的处理逻辑 if error_msg: # 全字段拼接,这里用JSON格式存储可读性更高,也可以自定义分隔符拼接 error_data = json.dumps(record_dict, ensure_ascii=False) # 开启事务保证原子性,避免状态更新成功但日志写入失败 try: # 更新原记录状态为V,假设业务表主键为id cursor.execute("UPDATE item_table SET status = 'V' WHERE id = %s", (record_dict['id'],)) # 写入错误日志 cursor.execute("INSERT INTO error_log (error_message, error_data) VALUES (%s, %s)", (error_msg, error_data)) db.commit() print(error_msg) except Exception as e: db.rollback() print(f"数据处理失败:{str(e)}") # 关闭数据库连接 cursor.close() db.close()
方式2:数据库层存储过程实现
如果不需要业务层介入,可以直接用存储过程实现校验+入库逻辑:
DELIMITER // CREATE PROCEDURE check_item_valid() BEGIN DECLARE done INT DEFAULT 0; DECLARE v_id INT; DECLARE v_item_no VARCHAR(5); DECLARE v_item_desc VARCHAR(240); DECLARE v_error_msg VARCHAR(255); DECLARE v_error_data TEXT; -- 游标遍历未校验的记录 DECLARE cur CURSOR FOR SELECT id, item_no, item_description FROM item_table WHERE status != 'V'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; loop_check: LOOP FETCH cur INTO v_id, v_item_no, v_item_desc; IF done THEN LEAVE loop_check; END IF; SET v_error_msg = NULL; -- 规则校验 IF v_item_no IS NULL OR v_item_no = '' THEN SET v_error_msg = 'Item No is null so the status is updated to V'; ELSEIF LENGTH(v_item_no) != 5 THEN SET v_error_msg = CONCAT('Item No length is ', LENGTH(v_item_no), ', required 5 characters so the status is updated to V'); ELSEIF v_item_desc IS NULL OR v_item_desc = '' THEN SET v_error_msg = 'Item Description is null so the status is updated to V'; ELSEIF LENGTH(v_item_desc) >= 240 THEN SET v_error_msg = CONCAT('Item Description length is ', LENGTH(v_item_desc), ', must be less than 240 characters so the status is updated to V'); END IF; IF v_error_msg IS NOT NULL THEN -- 拼接全字段内容,可根据业务表实际字段补充 SET v_error_data = CONCAT('id:',v_id,'|item_no:',v_item_no,'|item_description:',v_item_desc); UPDATE item_table SET status = 'V' WHERE id = v_id; INSERT INTO error_log(error_message, error_data) VALUES(v_error_msg, v_error_data); END IF; END LOOP; CLOSE cur; END // DELIMITER ;
调用存储过程直接执行即可:CALL check_item_valid();
内容的提问来源于stack exchange,提问作者Naveen Ragam
相关产品推荐
相关产品推荐

