MySQL动态表名查询实现：关联多表统计未读消息数

嘿，这个动态表名的问题确实挺常见的——MySQL的静态SQL没法直接把变量当表名用，得换个思路，用预处理语句或者存储过程来实现你的需求。我给你两种可行的方案，结合你的表关联逻辑来梳理：

先明确前提 #

假设你的动态表名规则是「固定前缀 + 从Sites表提取的某个字段值」（比如前缀是chat_data_，搭配Sites表的site_code字段，生成表名chat_data_xxx），同时Sites、Chat_ID、Chats的关联关系是：Sites.id = Chat_ID.site_id，Chat_ID.chat_id = Chats.id。

方法1：用预处理语句（适合一次性执行） #

这种方式是先把每个站点对应的查询语句拼接成完整的SQL，再通过预处理执行：

SET @sql = NULL;

-- 拼接每个站点的查询语句，用UNION ALL合并结果
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SELECT s.id AS site_id, ',
        '(SELECT COUNT(*) FROM `chat_data_', s.site_code, '` AS ch WHERE ch.chat_id = c.id AND ch.status = 0 AND ch.type = ''CLIENT'' ) AS unread_count ',
        'FROM Sites s JOIN Chat_ID ci ON s.id = ci.site_id JOIN Chats c ON ci.chat_id = c.id WHERE s.site_code = ''', s.site_code, ''''
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM Sites s;

-- 执行预处理语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键点说明： #

• 用`包裹动态表名，避免字段值里的特殊字符（比如下划线、数字开头）导致语法错误。
• 如果站点数量很多，可能需要调整group_concat_max_len参数，避免拼接的SQL被截断。

方法2：写存储过程（适合重复执行） #

如果需要经常跑这个统计，写个存储过程会更方便，用游标遍历每个站点，逐个查询动态表并汇总结果：

DELIMITER //

CREATE PROCEDURE GetSiteUnreadMessages()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE siteId INT;
    DECLARE siteCode VARCHAR(50);
    -- 定义游标遍历所有站点
    DECLARE cur CURSOR FOR SELECT id, site_code FROM Sites;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存汇总结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_unread (
        site_id INT,
        unread_count INT
    );
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO siteId, siteCode;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 构建当前站点的动态查询SQL
        SET @sql = CONCAT(
            'INSERT INTO temp_unread (site_id, unread_count) ',
            'SELECT ', siteId, ', COUNT(*) FROM `chat_data_', siteCode, '` ch ',
            'JOIN Chat_ID ci ON ch.chat_id = ci.chat_id ',
            'JOIN Chats c ON ci.chat_id = c.id ',
            'WHERE ci.site_id = ', siteId, ' AND ch.status = 0 AND ch.type = ''CLIENT'''
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    -- 返回最终汇总结果
    SELECT * FROM temp_unread;
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_unread;
    
    CLOSE cur;
END //

DELIMITER ;

调用存储过程的命令：

CALL GetSiteUnreadMessages();

额外建议： #

• 可以加个表存在性判断，避免因为某个动态表不存在导致整个查询失败，比如在循环里先检查EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = CONCAT('chat_data_', siteCode))。
• 如果所有动态表的结构完全一致，建议改成分区表，这样就不用折腾动态表名了，查询会简洁很多。

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