如何实现带嵌套IF逻辑的MySQL触发器自动分析test_logs日志

核心错误点 #

• 直接引用表字段未指定行：触发器内直接使用test_logs.BlockId这类写法，MySQL无法定位要匹配的test_logs表具体行，需要先遍历查询已有历史记录再做匹配
• 时间字段判断逻辑错误：timestamp类型空值为NULL而非0，使用New.SentAt <> 0判断会导致匹配逻辑失效，应改为New.SentAt IS NOT NULL
• 变量名笔误：ELSE分支中出现不存在的别名tNew.ReceivedAt，属于输入错误
• WHERE条件字段歧义：UPDATE语句的筛选条件sorted_logs.Test_LogID = LogID未指定LogID所属表，会导致语法报错
• 字段类型不兼容：TIMESTAMPDIFF(MICROSECOND)返回值最大对应约35分钟的微秒数，超过INT类型上限，建议将sorted_logs的RequestDelay、BlockDelay字段修改为BIGINT类型避免溢出
• 时间差计算顺序错误：原代码时间参数顺序颠倒，会得到负的延迟数值，需调整为「开始时间在前，结束时间在后」

修正后完整代码 #

1. 表结构（已调整延迟字段类型） #

DROP TABLE IF EXISTS `sorted_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sorted_logs` (
  `LogID` int(11) NOT NULL AUTO_INCREMENT,
  `Test_LogID` int(11) NOT NULL,
  `BlockId` varchar(128) NOT NULL,
  `Sender` varchar(128) NOT NULL,
  `Receiver` varchar(128) NOT NULL,
  `RequestDelay` bigint(20) DEFAULT NULL,
  `BlockDelay` bigint(20) DEFAULT NULL,
  `BlockDelivered` tinyint(1) DEFAULT NULL,
  `Duplicate` tinyint(1) NOT NULL,
  PRIMARY KEY (`LogID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `test_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_logs` (
  `LogID` int(11) NOT NULL AUTO_INCREMENT,
  `BlockId` varchar(128) NOT NULL,
  `LocalPeer` varchar(128) NOT NULL,
  `RemotePeer` varchar(128) NOT NULL,
  `SentAt` timestamp NULL DEFAULT NULL,
  `ReceivedAt` timestamp NULL DEFAULT NULL,
  `BlockRequestedAt` timestamp NULL DEFAULT NULL,
  `Duplicate` tinyint(1) NOT NULL,
  PRIMARY KEY (`LogID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

2. 触发器（新增游标遍历历史记录，修正所有逻辑错误） #

DELIMITER //
DROP TRIGGER IF EXISTS `analize_logs`;
CREATE TRIGGER `analize_logs` AFTER INSERT ON test_logs 
FOR EACH ROW 
BEGIN
    -- 存储遍历到的历史日志字段
    DECLARE v_log_id INT;
    DECLARE v_block_id VARCHAR(128);
    DECLARE v_local_peer VARCHAR(128);
    DECLARE v_remote_peer VARCHAR(128);
    DECLARE v_sent_at TIMESTAMP;
    DECLARE v_block_requested_at TIMESTAMP;
    DECLARE done INT DEFAULT 0;
    -- 游标遍历所有历史日志（排除新插入的当前行）
    DECLARE log_cursor CURSOR FOR 
        SELECT LogID, BlockId, LocalPeer, RemotePeer, SentAt, BlockRequestedAt 
        FROM test_logs 
        WHERE LogID != NEW.LogID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN log_cursor;
    read_loop: LOOP
        FETCH log_cursor INTO v_log_id, v_block_id, v_local_peer, v_remote_peer, v_sent_at, v_block_requested_at;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- 匹配逻辑1：计算请求延迟
        IF NEW.BlockId = v_block_id 
            AND NEW.SentAt IS NOT NULL 
            AND v_block_requested_at IS NOT NULL 
            AND NEW.RemotePeer = v_local_peer 
            AND NEW.LocalPeer = v_remote_peer 
        THEN 
            SET @requestdelay = TIMESTAMPDIFF(MICROSECOND, v_block_requested_at, NEW.SentAt);
            INSERT INTO sorted_logs (Test_LogID, BlockId, Sender, Receiver, RequestDelay, BlockDelivered, Duplicate) 
            VALUES (NEW.LogID, NEW.BlockId, NEW.LocalPeer, NEW.RemotePeer, @requestdelay, 0, NEW.Duplicate);
        END IF;

        -- 匹配逻辑2：计算块传输延迟
        IF NEW.BlockId = v_block_id 
            AND NEW.LocalPeer = v_remote_peer 
            AND NEW.RemotePeer = v_local_peer 
            AND NEW.ReceivedAt IS NOT NULL 
            AND v_sent_at IS NOT NULL 
        THEN 
            IF (SELECT COUNT(BlockId) FROM sorted_logs WHERE BlockId = NEW.BlockId AND Test_LogID = v_log_id) != 0 THEN 
                SET @blockdelay = TIMESTAMPDIFF(MICROSECOND, v_sent_at, NEW.ReceivedAt);
                UPDATE sorted_logs 
                SET BlockDelay = @blockdelay, 
                    Sender = NEW.LocalPeer, 
                    Receiver = NEW.RemotePeer, 
                    BlockDelivered = 1, 
                    Duplicate = NEW.Duplicate 
                WHERE BlockId = NEW.BlockId AND Test_LogID = v_log_id;
            ELSE
                SET @blockdelay = TIMESTAMPDIFF(MICROSECOND, v_sent_at, NEW.ReceivedAt);
                INSERT INTO sorted_logs (Test_LogID, BlockId, Sender, Receiver, BlockDelay, BlockDelivered, Duplicate) 
                VALUES (NEW.LogID, NEW.BlockId, NEW.LocalPeer, NEW.RemotePeer, @blockdelay, 1, NEW.Duplicate);
            END IF;
        END IF;
    END LOOP;
    CLOSE log_cursor;
END; //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Matias Correia