MySQL 多分表日誌 UNION 查詢合併儲存過程實作

MySQL 分表日誌跨表 UNION 合併查詢儲存過程

業務常將日誌按天分表 (如 tbl_req_YYYYMMDD)，查詢跨月資料需合併多張表結果。利用儲存過程動態產生 UNION SQL，避免手工拼接。

儲存過程完整碼

DROP PROCEDURE IF EXISTS unionSp;
DELIMITER //
CREATE PROCEDURE unionSp(
    IN sTime VARCHAR(32),   -- 起始日期 格式 YYYYMMDD
    IN eTime VARCHAR(32),   -- 結束日期 格式 YYYYMMDD
    IN tchema VARCHAR(32)   -- 目標資料庫
)
BEGIN
    DECLARE sqlVar VARCHAR(1024000) DEFAULT '';
    DECLARE rest INT DEFAULT 100;
    DECLARE tableName VARCHAR(1024);
    DECLARE cnt INT;

    WHILE rest > 0 DO
        SET sTime = DATE_FORMAT(ADDDATE(sTime, 1), '%Y%m%d');
        SET tableName = CONCAT('tbl_req_', sTime);

        SELECT COUNT(1) INTO cnt
        FROM information_schema.tables
        WHERE table_name = tableName AND TABLE_SCHEMA = tchema;

        IF cnt != 0 THEN
            IF rest = 1 THEN
                SET sqlVar = CONCAT(sqlVar, ' SELECT DISTINCT channel_id, app_id FROM ', tableName);
            ELSE
                SET sqlVar = CONCAT(sqlVar, ' SELECT DISTINCT channel_id, app_id FROM ', tableName, ' UNION');
            END IF;
        END IF;

        SET rest = DATEDIFF(eTime, sTime);
    END WHILE;

    SET @v_s = sqlVar;
    PREPARE stmt FROM @v_s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 呼叫範例：查詢 2014-07-30 至 2014-09-30 的 biz_date 庫日誌
CALL unionSp('20140730', '20140930', 'biz_date');

關鍵邏輯解析

• 動態建表名：用 ADDDATE 遞增日期，拼接 tbl_req_YYYYMMDD。
• 存在性檢查：查詢 information_schema.tables 確認表存在，避免報錯。
• UNION 去重：預設 UNION 會去除重複行；若需保留重複請用 UNION ALL (效能更好)。
• 預處理執行：動態 SQL 需用 PREPARE/EXECUTE/DEALLOCATE PREPARE 執行。

UNION 使用注意事項

1. 所有 SELECT 欄位數必須一致，欄位型別建議一致。
2. 結果欄位名以第一個 SELECT 為準。
3. UNION 會排序去重，大資料量建議改 UNION ALL 後應用層去重。
4. 跨庫查詢需確保用戶有各庫 SELECT 權限。

效能優化建議

• 若查詢頻繁，考慮建立彙總表或使用分區表替代手工分表。
• 為 channel_id、app_id 加上適當索引。
• 避免在儲存過程中處理超大結果集，必要時分批查詢。