chunkcopy.sql

BEGIN
    DROP TABLE IF EXISTS `mytable_tmp`;
    CREATE TABLE `mytable_tmp` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned DEFAULT NULL,
--    [...]
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    CREATE TEMPORARY TABLE `insert_batch` AS SELECT [...];
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    INSERT INTO `insert_batch` (SELECT [...]);
    SET @offset = 0;
    SET @limit = 1000;
    SELECT @rows := COUNT(*) FROM `insert_batch`;
    SET @rows = @rows + @limit + 1;
    CopyLoop: LOOP
        PREPARE stmt FROM "INSERT INTO mytable_tmp (SELECT * FROM `insert_batch` LIMIT ?, ?);";
        EXECUTE stmt USING @offset, @limit;
        DEALLOCATE PREPARE stmt;
        SET @offset = @offset + @limit;
        IF @offset > @rows THEN
            LEAVE CopyLoop;
        END IF;
        DO SLEEP(1);
    END LOOP CopyLoop;
    DROP TEMPORARY TABLE `insert_batch`;
    DROP TABLE IF EXISTS `mytable_old`;
    RENAME TABLE `mytable` TO `mytable_old`;
    RENAME TABLE `mytable_tmp` TO `mytable`;
END