You need to enable JavaScript to run this app. 如何捕获校验错误信息并将错误记录拼接存入error_log表
优惠活动
大模型
产品
解决方案
定价
更多

如何捕获校验错误信息并将错误记录拼接存入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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.11 19:03:00