MySQL 分表場景下批量添加字段：存儲過程游標動態執行方案

MySQL 百張分表同步加欄位？存儲過程 + 游標 + 動態 SQL 一鍵搞定

業務常見「邏輯一張表，物理多分表」（如 log_imap_mail_201601...log_imap_mail_201612）。需給所有分表加同一字段，手工執行 ALTER TABLE 不現實。本文提供**純 SQL 存儲過程方案**，無需 Shell、Python 外部腳本，在 MySQL 內部完成批量 DDL。

場景還原

-- 12 個月份分表
CREATE TABLE log_imap_mail_201601 (id INT PRIMARY KEY, ...);
CREATE TABLE log_imap_mail_201602 (id INT PRIMARY KEY, ...);
...
CREATE TABLE log_imap_mail_201612 (id INT PRIMARY KEY, ...);
-- 需給所有表加欄位 `new_col VARCHAR(50) DEFAULT ''`

核心存儲過程

DROP PROCEDURE IF EXISTS batch_add_column;
DELIMITER //
CREATE PROCEDURE batch_add_column()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tbl_name VARCHAR(64);
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = 'testdb'      -- 當前庫
          AND TABLE_NAME LIKE 'log_imap_mail_2016%';  -- 分表前綴
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO tbl_name;
        IF NOT done THEN
            SET @sql = CONCAT('ALTER TABLE `', tbl_name, '` ADD COLUMN `new_col` VARCHAR(50) DEFAULT \'\'');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur;
END//
DELIMITER ;

-- 執行
CALL batch_add_column();

關鍵點解析

1. 游標遍歷 information_schema.TABLES：動態獲取符合命名規則的表名，避免硬編碼。
2. CONCAT 拼接 DDL + PREPARE/EXECUTE：MySQL 不支援在靜態 SQL 中使用變量作為表名，**必須用預處理語句動態執行**。
3. DEALLOCATE PREPARE 釋放資源：防止預處理語句洩漏導致 Prepared statement needs to be re-prepared 錯誤。
4. 事務不可控：DDL 隱含提交，若中間失敗，前面已成功的表不會回滾。建議：
   • 先在測試庫跑通。
   • 生產庫低峰期執行。
   • 關鍵表備份 .ibd 或邏輯備份。

三種游標循環寫法對比

本例用 REPEAT 最簡潔。

進階：多庫多表批量

DECLARE cur CURSOR FOR
SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS full_name
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN ('db_shard_01','db_shard_02','db_shard_03')
  AND TABLE_NAME LIKE 'order_%';
-- FETCH 直接得到 `db_shard_01.order_202401`，拼接 SQL 時無需再加庫前綴

替代方案對比

總結

分表批量加欄位，**存儲過程 + 信息模式游標 + 動態 PREPARE** 是最輕量、可版本化的方案。配合 pt-online-schema-change 處理核心大表，構建完整的 Schema 變更工具箱。