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

-- 先把数据迁过去
INSERT INTO history_db.target_table
SELECT * FROM target_table WHERE batchid = 381;

-- 再把原表数据删掉
DELETE FROM target_table WHERE batchid = 381;

CREATE DEFINER=`root`@`%` 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 batch_migrate_and_delete('target_table', 381)