MySQL存储过程执行报错1146：别名'info'未识别问题求助

报错核心原因 #

• info不是实际表：你写的SELECT table_name AS info里的info只是查询结果的临时列别名，不是数据库中存在的实体表，后续FROM info的写法完全错误，直接触发1146表不存在报错。
• 循环逻辑无效：用COUNT(*)统计总表数后做while循环，循环内的info根本没关联到任何具体表名，相当于空转，无法遍历目标表。
• 动态表名需用动态SQL：要操作的表名是从information_schema.tables读取的动态值，静态SQL无法识别变量形式的表名，必须用动态SQL执行。

修正后的存储过程代码 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `unite`()
BEGIN
    -- 声明变量存储表名
    DECLARE table_name_var VARCHAR(255);
    -- 游标遍历结束标志
    DECLARE done INT DEFAULT 0;
    -- 声明游标，只筛选当前库中transactions_20开头的表
    DECLARE table_cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_name LIKE 'transactions_20%'
          AND table_schema = DATABASE();
    
    -- 设置游标遍历结束时的处理逻辑
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 打开游标
    OPEN table_cursor;

    -- 循环遍历所有目标表
    read_loop: LOOP
        -- 读取当前表名到变量
        FETCH table_cursor INTO table_name_var;
        -- 遍历结束则退出循环
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- 拼接动态SQL，将表名变量代入查询
        SET @sql = CONCAT('
            INSERT INTO days_totals
            (date, num_of_ppl, punches, admission_total, pass_total, misc_total, food_total, drink_total, grand_total)
            SELECT 
                SUBSTRING(''', table_name_var, ''', 14, 23) AS date,
                COUNT(CASE WHEN name IN (''adult admission'', ''punch a pass'', ''child admission'') THEN amount END) AS num_of_ppl,
                COUNT(CASE WHEN name = ''punch a pass'' THEN amount END) AS punches,
                SUM(CASE WHEN name IN (''adult admission'', ''child admission'') THEN total END) AS admission_total,
                SUM(CASE WHEN name = ''ten visit pass'' THEN total END) AS pass_total,
                SUM(CASE WHEN type = ''misc'' AND name NOT IN (''adult admission'', ''child admission'', ''ten visit pass'') THEN total END) AS misc_total,
                SUM(CASE WHEN type = ''food'' THEN total END) AS food_total,
                SUM(CASE WHEN type = ''drink'' THEN total END) AS drink_total,
                SUM(CASE WHEN type IN (''misc'', ''food'', ''drink'') THEN total END) AS grand_total
            FROM ', table_name_var, ';
        ');
        
        -- 执行动态SQL
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    -- 关闭游标
    CLOSE table_cursor;
END

关键修改说明 #

• 用游标遍历表名：直接从information_schema.tables筛选符合条件的表，避免无效遍历非目标表。
• 动态SQL处理：通过CONCAT拼接包含动态表名的SQL语句，再用PREPARE/EXECUTE执行，解决静态SQL无法识别动态表名的问题。
• 简化统计逻辑：把原代码中多个子查询改成单表查询+CASE WHEN，减少重复表扫描，提升执行效率。
• 添加数据库过滤：游标查询中加入table_schema = DATABASE()，仅遍历当前数据库的表，防止误操作其他库的表。

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