MariaDB存储过程跨表匹配时陷入重复循环问题求助

需要实现两张表的行匹配功能：匹配成功后删除匹配行，继续后续匹配。但MariaDB存储过程匹配到某条记录后，会一直重复匹配该行，直到触发循环的行数限制（此前曾无限循环）。

附存储过程代码：

DELIMITER //
CREATE PROCEDURE MatchTransactions()
BEGIN
    -- Declare variables at the beginning
    DECLARE rows_deleted INT DEFAULT 1;
    DECLARE min_rows INT;

    -- Create temporary tables
    CREATE TEMPORARY TABLE TempA AS SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num FROM SA_A;
    CREATE TEMPORARY TABLE TempB AS SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num FROM SA_B;

    -- Get the minimum number of rows between the two tables
    SELECT LEAST((SELECT COUNT(*) FROM TempA), (SELECT COUNT(*) FROM TempB)) INTO min_rows;

    -- Create a table to store the matches
    CREATE TABLE Matches (A_row INT, B_row INT, A_ID INT, B_ID INT);


    -- Loop until no more matches are found or the number of iterations exceeds the minimum number of rows
    WHILE rows_deleted > 0 AND (SELECT COUNT(*) FROM Matches) < min_rows DO
        -- Reset rows_deleted
        SET rows_deleted = 0;

        -- Start a new transaction
        START TRANSACTION;

        -- Find a match
        SELECT A.row_num, B.row_num, A.A_ID, B.B_ID INTO @match_A, @match_B, @A_id, @B_id
        FROM TempA AS A
        INNER JOIN TempB AS B 
        ON A.ENTRY_DATE = B.TRANSACTION_DATE 
        AND A.TRANSACTION_AMOUNT = B.TRANSACTION_AMOUNT 
        AND A.CREDIT_DEBIT = B.CREDIT_DEBIT 
        AND A.ACCOUNT = B.PRIMARY_ACCOUNT 
        AND ABS(TIMEDIFF(A.TIME, B.TIME)) <= '00:00:15'
        LIMIT 1;

        -- If a match was found, delete it from both tables and add it to the Matches table
        IF @A_id IS NOT NULL AND @B_id IS NOT NULL THEN
            DELETE FROM TempA WHERE A_ID = @A_id;
            DELETE FROM TempB WHERE B_ID = @B_id;

            INSERT INTO Matches(A_row, B_row, A_ID, B_ID) VALUES (@match_A, @match_B, @A_id, @B_id);

            -- Get the number of rows deleted in this iteration
            SET rows_deleted = ROW_COUNT();
        END IF;

        -- Commit the transaction
        COMMIT;
    END WHILE;

    -- Select the matches
    SELECT * FROM Matches;

    -- Drop the temporary tables and the Matches table
    DROP TEMPORARY TABLE IF EXISTS TempA;
    DROP TEMPORARY TABLE IF EXISTS TempB;
    DROP TABLE IF EXISTS Matches;
END//
DELIMITER ;

注：已添加min_rows约束强制终止循环，Matches表理论最大行数应为两表的最小行数。

• 会话变量未重置：@A_id、@B_id等会话变量在循环结束后未被清空，若某次循环未找到新匹配，变量会保留上一次的匹配值，导致后续循环误判仍有匹配，重复执行删除逻辑。
• 删除条件不精准：若A_ID/B_ID不是唯一键，DELETE FROM TempA WHERE A_ID = @A_id会删除所有同ID的行，而非仅当前匹配的行；若同ID存在多条符合条件的记录，会反复触发匹配删除，形成无限循环。
• 循环终止条件依赖漏洞：rows_deleted依赖ROW_COUNT()，但如果删除操作未命中行（比如目标行已被删除），rows_deleted会被设为0，但若变量残留旧值，仍会进入无效的匹配判断流程。

1. 循环前重置会话变量：每次循环开始时清空@match_A、@match_B、@A_id、@B_id，避免残留旧值干扰判断。
2. 使用唯一行标识删除：利用临时表的row_num（由ROW_NUMBER()生成，唯一）作为删除条件，确保仅删除当前匹配的行，避免误删同ID的其他记录。
3. 优化循环终止逻辑：直接通过是否找到匹配（@A_id IS NOT NULL）来设置rows_deleted，判断更准确。

修改后的存储过程代码：

DELIMITER //
CREATE PROCEDURE MatchTransactions()
BEGIN
    DECLARE rows_deleted INT DEFAULT 1;
    DECLARE min_rows INT;

    CREATE TEMPORARY TABLE TempA AS SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num FROM SA_A;
    CREATE TEMPORARY TABLE TempB AS SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num FROM SA_B;

    SELECT LEAST((SELECT COUNT(*) FROM TempA), (SELECT COUNT(*) FROM TempB)) INTO min_rows;

    CREATE TABLE Matches (A_row INT, B_row INT, A_ID INT, B_ID INT);

    WHILE rows_deleted > 0 AND (SELECT COUNT(*) FROM Matches) < min_rows DO
        SET rows_deleted = 0;
        -- 重置会话变量，避免残留上次匹配值
        SET @match_A = NULL;
        SET @match_B = NULL;
        SET @A_id = NULL;
        SET @B_id = NULL;

        START TRANSACTION;

        SELECT A.row_num, B.row_num, A.A_ID, B.B_ID INTO @match_A, @match_B, @A_id, @B_id
        FROM TempA AS A
        INNER JOIN TempB AS B 
        ON A.ENTRY_DATE = B.TRANSACTION_DATE 
        AND A.TRANSACTION_AMOUNT = B.TRANSACTION_AMOUNT 
        AND A.CREDIT_DEBIT = B.CREDIT_DEBIT 
        AND A.ACCOUNT = B.PRIMARY_ACCOUNT 
        AND ABS(TIMEDIFF(A.TIME, B.TIME)) <= '00:00:15'
        LIMIT 1;

        IF @A_id IS NOT NULL AND @B_id IS NOT NULL THEN
            -- 使用row_num删除，确保只删除当前匹配的行
            DELETE FROM TempA WHERE row_num = @match_A;
            DELETE FROM TempB WHERE row_num = @match_B;

            INSERT INTO Matches(A_row, B_row, A_ID, B_ID) VALUES (@match_A, @match_B, @A_id, @B_id);

            -- 本次删除两行（TempA、TempB各一行），设置rows_deleted为2
            SET rows_deleted = 2;
        END IF;

        COMMIT;
    END WHILE;

    SELECT * FROM Matches;

    DROP TEMPORARY TABLE IF EXISTS TempA;
    DROP TEMPORARY TABLE IF EXISTS TempB;
    DROP TABLE IF EXISTS Matches;
END//
DELIMITER ;

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