MySQL 存儲過程實戰：批量為每日分表新增欄位

MySQL 存儲過程實戰：批量為每日分表新增欄位

高流量系統常按天分表（如 tbl_ads_req_20140801..._20140831），業務新增欄位時逐表手工 ALTER TABLE 不現實。本文提供一個參數化存儲過程，自動遍歷日期範圍批量加欄位。

表命名規律

tbl_ads_req_YYYYMMDD，前綴固定，後綴為日期。

存儲過程核心碼

DELIMITER //
CREATE PROCEDURE sp_add_col_daily()
BEGIN
  DECLARE sDate VARCHAR(8) DEFAULT '20140801';
  DECLARE eDate VARCHAR(8) DEFAULT '20140831';
  DECLARE tblName VARCHAR(128);
  DECLARE sqlStmt VARCHAR(256);
  DECLARE diffDay INT DEFAULT 1;

  WHILE diffDay > 0 DO
    SET sDate = DATE_FORMAT(DATE_ADD(sDate, INTERVAL 1 DAY), '%Y%m%d');
    SET tblName = CONCAT('tbl_ads_req_', sDate);
    SET sqlStmt = CONCAT('ALTER TABLE `', tblName, '` ADD `app_package` VARCHAR(64)');
    
    SET @v_sql = sqlStmt;
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SET diffDay = DATEDIFF(eDate, sDate);
  END WHILE;
END//
DELIMITER ;

關鍵函數說明

• DATE_ADD(date, INTERVAL 1 DAY)：日期遞增。
• DATE_FORMAT(..., '%Y%m%d')：格式化為無分隔符日期串。
• DATEDIFF(end, start)：剩餘天數，作為迴圈終止條件。
• PREPARE / EXECUTE / DEALLOCATE PREPARE：動態 SQL 執行三部曲，防注入、支援變數插值。

執行與驗證

CALL sp_add_col_daily();
-- 抽樣檢查
SHOW COLUMNS FROM tbl_ads_req_20140815 LIKE 'app_package';

擴展建議

1. 加入 IF NOT EXISTS 判斷，避免重複執行報錯（MySQL 8.0+ 支援）。
2. 將表前綴、欄位定義、日期範圍參數化，做成通用工具過程。
3. 大量表操作建議在低峰期執行，並監控 ALTER TABLE 是否觸發表鎖（5.6+ 線上 DDL 多數場景無鎖）。