MySQL存储过程报错Can't reopen table: 'lc1'的解决咨询

问题背景 #

为统计应缴与已缴费用生成报表，编写了存储过程usp_calculate_license_fees，试图通过创建中间临时表避免重复引用临时表的问题，但执行时仍报错「Can't reopen table: 'lc1'」。

错误原因 #

MySQL对临时表的访问存在限制：在同一个查询语句中，无法多次打开同一个临时表。虽然你将递归CTE的结果存入了LicenseChainTemp临时表，但在后续步骤中（如Step 2的自连接、Step 5的嵌套子查询引用），底层查询解析时仍会尝试重复打开该临时表，触发报错。

解决方案 #

将核心临时表LicenseChainTemp复制出一份完全相同的临时表（如LicenseChainTempCopy），在需要重复引用的场景中使用不同的临时表实例，避免同一临时表被多次打开。

修改后的存储过程代码 #

DELIMITER $$

CREATE PROCEDURE usp_calculate_license_fees()
BEGIN
    DECLARE totalRowCount INT;

    SET SQL_SAFE_UPDATES = 0;

    -- Step 1: Create LicenseChainTemp table
    DROP TEMPORARY TABLE IF EXISTS LicenseChainTemp;
    CREATE TEMPORARY TABLE LicenseChainTemp AS
    WITH RECURSIVE LicenseChain AS (
        SELECT 
            id, citizen_id, trade_license_no, previous_license_no, fiscal_year, license_fee, signboard_fee, is_paid, applicant_name
        FROM 
            trade_license_request_uat
        WHERE 
            previous_license_no IS NULL
        UNION ALL
        SELECT 
            t.id, t.citizen_id, t.trade_license_no, t.previous_license_no, t.fiscal_year, t.license_fee, t.signboard_fee, t.is_paid, t.applicant_name
        FROM 
            trade_license_request_uat t
        INNER JOIN 
            LicenseChain lc ON lc.trade_license_no = t.previous_license_no
    )
    SELECT * FROM LicenseChain;

    -- 新增：复制LicenseChainTemp到LicenseChainTempCopy，避免重复打开同一临时表
    DROP TEMPORARY TABLE IF EXISTS LicenseChainTempCopy;
    CREATE TEMPORARY TABLE LicenseChainTempCopy AS SELECT * FROM LicenseChainTemp;

    -- Step 2: Create LatestLicenseTemp table
    DROP TEMPORARY TABLE IF EXISTS LatestLicenseTemp;
    CREATE TEMPORARY TABLE LatestLicenseTemp AS
    SELECT 
        lc1.citizen_id, 
        lc1.trade_license_no AS latest_trade_license_no,
        lc1.applicant_name
    FROM 
        LicenseChainTemp lc1
    LEFT JOIN 
        LicenseChainTempCopy lc2 ON lc1.trade_license_no = lc2.previous_license_no
    WHERE 
        lc2.trade_license_no IS NULL;

    -- Step 3: Create FiscalYearsTemp table
    DROP TEMPORARY TABLE IF EXISTS FiscalYearsTemp;
    CREATE TEMPORARY TABLE FiscalYearsTemp AS
    SELECT DISTINCT fiscal_year
    FROM trade_license_request_uat
    WHERE fiscal_year BETWEEN '2018' AND '2024';

    -- Step 4: Create ApplicantDataTemp table
    DROP TEMPORARY TABLE IF EXISTS ApplicantDataTemp;
    CREATE TEMPORARY TABLE ApplicantDataTemp AS
    SELECT 
        lc.citizen_id, lc.trade_license_no, lc.fiscal_year, lc.license_fee, lc.signboard_fee, lc.is_paid, lc.applicant_name
    FROM 
        LicenseChainTemp lc;

    -- Step 5: Create MissingYearsTemp table
    DROP TEMPORARY TABLE IF EXISTS MissingYearsTemp;
    CREATE TEMPORARY TABLE MissingYearsTemp AS
    SELECT ay.citizen_id, ay.trade_license_no, ay.fiscal_year
    FROM (
        SELECT DISTINCT tlr.citizen_id, tlr.trade_license_no, fy.fiscal_year
        FROM FiscalYearsTemp fy
        CROSS JOIN (SELECT DISTINCT citizen_id, trade_license_no FROM LicenseChainTempCopy) tlr
    ) ay
    LEFT JOIN ApplicantDataTemp ad ON ay.citizen_id = ad.citizen_id AND ay.fiscal_year = ad.fiscal_year AND ay.trade_license_no = ad.trade_license_no
    WHERE ad.fiscal_year IS NULL AND ay.fiscal_year BETWEEN '2018' AND '2024';

    -- Step 6: Create ApplicantFeeTemp table
    DROP TEMPORARY TABLE IF EXISTS ApplicantFeeTemp;
    CREATE TEMPORARY TABLE ApplicantFeeTemp AS
    SELECT md.citizen_id, md.trade_license_no, md.fiscal_year,
           COALESCE(ad.license_fee, (
               SELECT license_fee
               FROM ApplicantDataTemp ad2
               WHERE ad2.citizen_id = md.citizen_id
               AND ad2.trade_license_no = md.trade_license_no
               AND ad2.fiscal_year = (
                   SELECT MAX(ad3.fiscal_year)
                   FROM ApplicantDataTemp ad3
                   WHERE ad3.citizen_id = ad2.citizen_id
                   AND ad3.trade_license_no = ad2.trade_license_no
                   AND ad3.fiscal_year < md.fiscal_year
               )
           )) AS license_fee,
           COALESCE(ad.signboard_fee, (
               SELECT signboard_fee
               FROM ApplicantDataTemp ad2
               WHERE ad2.citizen_id = md.citizen_id
               AND ad2.trade_license_no = md.trade_license_no
               AND ad2.fiscal_year = (
                   SELECT MAX(ad3.fiscal_year)
                   FROM ApplicantDataTemp ad3
                   WHERE ad3.citizen_id = ad2.citizen_id
                   AND ad3.trade_license_no = ad2.trade_license_no
                   AND ad3.fiscal_year < md.fiscal_year
               )
           )) AS signboard_fee
    FROM MissingYearsTemp md
    LEFT JOIN ApplicantDataTemp ad ON md.citizen_id = ad.citizen_id AND md.fiscal_year = ad.fiscal_year AND md.trade_license_no = ad.trade_license_no;

    -- Step 7: Create CombinedFeeTemp table
    DROP TEMPORARY TABLE IF EXISTS CombinedFeeTemp;
    CREATE TEMPORARY TABLE CombinedFeeTemp AS
    SELECT citizen_id, trade_license_no, fiscal_year, license_fee, signboard_fee
    FROM (
        SELECT citizen_id, trade_license_no, fiscal_year, 
               CASE 
                   WHEN is_paid = 2 THEN 0 
                   ELSE license_fee 
               END AS license_fee,
               CASE 
                   WHEN is_paid = 2 THEN 0 
                   ELSE signboard_fee 
               END AS signboard_fee
        FROM ApplicantDataTemp
        UNION ALL
        SELECT citizen_id, trade_license_no, fiscal_year, license_fee, signboard_fee
        FROM ApplicantFeeTemp
    ) AS fees;

    -- Step 8: Create SummarizedDataTemp table
    DROP TEMPORARY TABLE IF EXISTS SummarizedDataTemp;
    CREATE TEMPORARY TABLE SummarizedDataTemp AS
    SELECT 
        af.citizen_id,
        af.trade_license_no,
        SUM(CASE WHEN af.fiscal_year BETWEEN '2018' AND '2023' THEN af.license_fee ELSE 0 END) AS due_for_2018_to_2023,
        SUM(CASE WHEN af.fiscal_year = '2024' THEN af.license_fee ELSE 0 END) AS due_2024_license_fee,
        SUM(CASE WHEN af.fiscal_year = '2024' THEN af.signboard_fee ELSE 0 END) AS due_2024_signboard_fee
    FROM CombinedFeeTemp af
    GROUP BY af.citizen_id, af.trade_license_no;

    -- Step 9: Create ReceivedDataTemp table
    DROP TEMPORARY TABLE IF EXISTS ReceivedDataTemp;
    CREATE TEMPORARY TABLE ReceivedDataTemp AS
    SELECT 
        tlr.citizen_id,
        tlr.trade_license_no,
        SUM(CASE WHEN fiscal_year = '2024' AND is_paid = 2 THEN license_fee ELSE 0 END) AS received_for_2024_license_fee,
        SUM(CASE WHEN fiscal_year = '2024' AND is_paid = 2 THEN signboard_fee ELSE 0 END) AS received_for_2024_signboard_fee
    FROM trade_license_request_uat tlr
    WHERE fiscal_year = '2024'
    GROUP BY tlr.citizen_id, tlr.trade_license_no;

    -- Step 10: Create FinalDataTemp table
    DROP TEMPORARY TABLE IF EXISTS FinalDataTemp;
    CREATE TEMPORARY TABLE FinalDataTemp AS
    SELECT 
        ld.citizen_id,
        ld.latest_trade_license_no AS trade_license_no,
        ld.applicant_name,
        COALESCE(sd.due_for_2018_to_2023, 0) AS due_for_2018_to_2023,
        COALESCE(sd.due_2024_license_fee, 0) AS due_2024_license_fee,
        COALESCE(sd.due_2024_signboard_fee, 0) AS due_2024_signboard_fee,
        COALESCE(rd.received_for_2024_license_fee, 0) AS received_for_2024_license_fee,
        COALESCE(rd.received_for_2024_signboard_fee, 0) AS received_for_2024_signboard_fee
    FROM 
        LatestLicenseTemp ld
    LEFT JOIN 
        SummarizedDataTemp sd ON ld.citizen_id = sd.citizen_id AND ld.latest_trade_license_no = sd.trade_license_no
    LEFT JOIN 
        ReceivedDataTemp rd ON ld.citizen_id = rd.citizen_id AND ld.latest_trade_license_no = rd.trade_license_no;

    -- Step 11: Select the final data
    SELECT 
        fd.citizen_id,
        fd.trade_license_no,
        fd.applicant_name,
        fd.due_for_2018_to_2023,
        fd.due_2024_license_fee,
        fd.due_2024_signboard_fee,
        fd.received_for_2024_license_fee,
        fd.received_for_2024_signboard_fee
    FROM 
        FinalDataTemp fd;

END$$

DELIMITER ;

关键修改点 #

1. 在Step 1之后新增复制临时表的步骤，创建LicenseChainTempCopy，与LicenseChainTemp数据完全一致。
2. Step 2中自连接时，使用LicenseChainTempCopy作为连接表，避免同一临时表被多次打开。
3. Step 5的子查询中，引用LicenseChainTempCopy获取citizen_id和trade_license_no，替换原有的LicenseChainTemp引用。

内容的提问来源于stack exchange，提问作者Md. Shamvil Hossain