MySQL 一鍵修改所有表存儲引擎：INFORMATION_SCHEMA 動態生成 ALTER 批次

核心 SQL

SELECT GROUP_CONCAT(CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=ARCHIVE;') SEPARATOR ' ')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'userinfo' AND TABLE_TYPE = 'BASE TABLE';

執行流程

1. 在 MySQL 客戶端執行上述查詢
2. 複製結果欄位 (一長串 ALTER TABLE ... 语句)
3. 貼上執行，完成全庫引擎遷移

參數說明

• TABLE_SCHEMA = 'userinfo'：目標資料庫名，請修改
• ENGINE=ARCHIVE：目標引擎，可改 InnoDB、MyISAM、CSV 等
• TABLE_TYPE = 'BASE TABLE'：排除 VIEW
• GROUP_CONCAT 預設長度 1024，表多時需調大：
  SET SESSION group_concat_max_len = 1000000;

進階：存儲過程自動化

DELIMITER //
CREATE PROCEDURE change_all_engine(IN db_name VARCHAR(64), IN new_engine VARCHAR(32))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE t_name VARCHAR(64);
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO t_name;
        IF done THEN LEAVE read_loop; END IF;
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', t_name, '` ENGINE=', new_engine);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END//
DELIMITER ;

-- 呼叫
CALL change_all_engine('userinfo', 'InnoDB');

遷移前必讀

• 鎖表風險：ALTER TABLE 會鎖表 (MyISAM 全鎖、InnoDB 線上 DDL 視版本)，建議低峰期或使用 pt-online-schema-change
• 外鍵約束：InnoDB 間遷移需先 SET FOREIGN_KEY_CHECKS=0
• 資料特性：ARCHIVE 僅支援插入/查詢、無索引、壓縮率高，適合日誌/歸檔表
• 備份先行：mysqldump --single-transaction -R -E db_name > backup.sql