MySQL 大批量 LONGTEXT 数据迁移导致临时表空间溢出的解决方案

事情是这样的我有一个 MySQL 表大概 100 个 G但数据只有 10000 条。看起来行数不多奈何每条数据里塞了好几个LONGTEXT字段单条数据体量巨大。最近需要对这张表做一个备份操作根据批次 IDbatchid把某批数据备份到另一个库然后删掉原表里对应的数据。目标库有同名表用来归档历史数据。思路很直接两条 SQL 搞定-- 先把数据迁过去 INSERT INTO history_db.target_table SELECT * FROM target_table WHERE batchid 381; -- 再把原表数据删掉 DELETE FROM target_table WHERE batchid 381;结果一执行直接报错要么是SQL 错误 [3] [HY000]: Error writing file C:\Windows\TEMP\MLa4gsg6dpk9s2z8ry (OS errno 28 - No space left on device)要么是SQL 错误 [1114] [HY000]: The table C:\Windows\TEMP#sql6f88_bd086f_2 is fullMySQL 是装在Windows服务器的不过明明装在 D 盘为什么 C 盘会爆实际上MySQL 在执行大事务或者大查询时会在系统临时目录C:\Windows\TEMP生成临时文件。当一次操作的数据量太大临时文件直接把 C 盘写满了SQL 自然就挂了。INSERT ... SELECT一次性搬运大量LONGTEXT数据临时表膨胀得飞快现象就是服务器剩余50G的C盘迅速飙红待执行失败后又恢复正常。既然一步到位不行那就化整为零——用存储过程分批处理CREATE DEFINERroot% PROCEDURE business_db.batch_migrate_and_delete( IN p_table_name VARCHAR(128), IN p_batchid BIGINT ) batch_migrate_and_delete: BEGIN DECLARE v_min_id BIGINT; DECLARE v_max_id BIGINT; DECLARE v_batch_size INT DEFAULT 500; -- 获取 id 范围 SET sql_range CONCAT( SELECT MIN(id), MAX(id) INTO v_min, v_max FROM , p_table_name, WHERE batchid , p_batchid ); PREPARE stmt FROM sql_range; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_min_id v_min; SET v_max_id v_max; IF v_min_id IS NULL THEN LEAVE batch_migrate_and_delete; END IF; -- 每批先迁移到历史库再删除原表数据 WHILE v_min_id v_max_id DO SET sql_insert CONCAT( INSERT INTO history_db., p_table_name, SELECT * FROM , p_table_name, WHERE batchid , p_batchid, AND id BETWEEN , v_min_id, AND , v_min_id v_batch_size - 1 ); PREPARE stmt FROM sql_insert; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET sql_delete CONCAT( DELETE FROM , p_table_name, WHERE batchid , p_batchid, AND id BETWEEN , v_min_id, AND , v_min_id v_batch_size - 1 ); PREPARE stmt FROM sql_delete; EXECUTE stmt; DEALLOCATE PREPARE stmt; COMMIT; SET v_min_id v_min_id v_batch_size; END WHILE; END batch_migrate_and_delete创建存储过程后执行call即可CALL batch_migrate_and_delete(target_table, 381)存储过程的核心思路很简单先用MIN(id)和MAX(id)圈出要处理的数据范围然后每次取 500 条v_batch_size做完 insert 和 delete 立刻COMMIT再继续下一批。这样每批事务体量都很小临时文件来不及撑爆 C 盘就已经释放了。⚠️ 需要注意的是​v_batch_size要结合实际调。我这里的表单条数据就很大设 500 差不多。如果你的表行数多但单条很小可以适当调大反过来单条更大就调小一些核心是控制每批事务的数据总量。按id BETWEEN分批的前提是 id 连续或大致连续。如果 id 有大量空洞可以换成LIMIT配合游标的方式分批。迁移和删除放在同一个事务批次里。先 insert 成功再 delete万一 insert 失败当前批次不会被删掉数据不会丢。