sql脚本

-- 创建临时表记录分批复制的日志
CREATE TABLE temp_copy_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_start_id INT,
    batch_end_id INT,
    start_time DATETIME,
    end_time DATETIME,
    duration INT,
    records_copied INT
);

CREATE TABLE demo_user (
	id INT NOT NULL,
	name varchar(100) NULL,
	age INT NULL,
	createby varchar(100) NULL,
	createdate TIMESTAMP NULL,
	updateby varchar(100) NULL,
	updatedate TIMESTAMP NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;
CREATE INDEX demo_user_age_IDX USING BTREE ON gts_test.demo_user (age);

CREATE TABLE `A` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `createby` varchar(100) DEFAULT NULL,
  `createdate` timestamp NULL DEFAULT NULL,
  `updateby` varchar(100) DEFAULT NULL,
  `updatedate` timestamp NULL DEFAULT NULL,
  KEY `demo_user_age_IDX` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `B` (
  `id` int NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `createby` varchar(100) DEFAULT NULL,
  `createdate` timestamp NULL DEFAULT NULL,
  `updateby` varchar(100) DEFAULT NULL,
  `updatedate` timestamp NULL DEFAULT NULL,
  KEY `demo_user_age_IDX` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `C` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `createby` varchar(100) DEFAULT NULL,
  `createdate` timestamp NULL DEFAULT NULL,
  `updateby` varchar(100) DEFAULT NULL,
  `updatedate` timestamp NULL DEFAULT NULL,
  KEY `demo_user_age_IDX` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 初始化变量
SET @batch_size = 10000; -- 每批次复制的记录数
SET @total_records = 2000000; -- A表中年龄大于18的记录总数
SET @batch_count = CEIL(@total_records / @batch_size); -- 计算需要多少批次
SET @current_batch = 0;
SET @total_copied = 0; -- 记录已复制的记录数

-- 将A表中年龄大于18的数据复制到C表
INSERT INTO C (id, name, age, createby, createdate, updateby, updatedate)
SELECT id, name, age, createby, createdate, updateby, updatedate
FROM A
WHERE age > 18;

-- 循环分批复制数据
WHILE @current_batch < @batch_count DO
    -- 获取当前批次的起始和结束ID
    SET @start_id = @current_batch * @batch_size + 1;
    SET @end_id = IF(@current_batch = @batch_count - 1, @total_records, @start_id + @batch_size - 1);

    -- 检查日志表中是否已经存在相同的id范围
    IF NOT EXISTS (SELECT 1 FROM temp_copy_log WHERE batch_start_id = @start_id AND batch_end_id = @end_id) THEN
        -- 开始复制数据
        START TRANSACTION;
        INSERT INTO B (uasename, age, createby, createdate, updateby, updatedate)
        SELECT name, age, createby, createdate, updateby, updatedate
        FROM C
        WHERE id >= @start_id AND id <= @end_id;
        COMMIT;

        -- 记录当前批次的复制日志
        INSERT INTO temp_copy_log (batch_start_id, batch_end_id, start_time, end_time, duration, records_copied)
        VALUES (@start_id, @end_id, NOW(), NOW(), TIMESTAMPDIFF(SECOND, NOW(), NOW()), @batch_size);

        -- 更新已复制的记录数
        SET @total_copied = @total_copied + @batch_size;
    ELSE
        -- 如果日志已存在，跳过该批次复制
        SELECT 'Skipping batch from ' + CAST(@start_id AS CHAR) + ' to ' + CAST(@end_id AS CHAR) AS message;
    END IF;

    -- 更新批次计数器
    SET @current_batch = @current_batch + 1;
END WHILE;

-- 校验复制总数是否正确，并记录日志
IF @total_copied = @total_records THEN
    SELECT 'All records copied successfully.' AS message;
ELSE
    SELECT 'Error: Total copied records do not match the expected count.' AS message;
END IF;

DELIMITER $$
CREATE PROCEDURE MockData()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 200000 DO
    INSERT INTO A (id, name, age, createby, createdate, updateby, updatedate)
    VALUES (i, CONCAT('Name', i), FLOOR(RAND() * 100), CONCAT('User', i), NOW(), CONCAT('User', i), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL MockData();


DELIMITER $$
CREATE PROCEDURE MockData()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 200000 DO
    INSERT INTO A (id, name, age, createby, createdate, updateby, updatedate)
    VALUES (i, CONCAT('Name', i), FLOOR(RAND() * 100), CONCAT('User', i), NOW(), CONCAT('User', i), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL MockData();


DELIMITER $$

CREATE PROCEDURE InsertData()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= 200000 DO
    INSERT INTO A (id, name, age, createby, createdate, updatedate)
    VALUES (i, CONCAT('Name', i), FLOOR(RAND() * 100), 'User', NOW(), NOW());
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

CALL InsertData();

DELIMITER $$

CREATE PROCEDURE InsertData()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= 200000 DO
    INSERT INTO A (id, name, age, createby, createdate, updatedate)
    VALUES (i, CONCAT('Name', i), FLOOR(RAND() * 100), 'User', NOW(), NOW());
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

CALL InsertData();