mysql分割多条记录

-- 创建原表
CREATE TABLE original_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP
);

-- 创建新表
CREATE TABLE new_table (
    id INT PRIMARY KEY,
    original_id INT,
    description TEXT,
    FOREIGN KEY (original_id) REFERENCES original_table(id)
);

-- 将数据从原表移动到新表
INSERT INTO new_table (id, original_id, description)
SELECT id, id, description FROM original_table;

-- 创建多个子表
CREATE TABLE table_1 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP
);

CREATE TABLE table_2 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP
);

-- 将数据分散到多个子表中（假设使用id进行分片）
DELIMITER $$
CREATE PROCEDURE distribute_data()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM original_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF v_id % 2 = 0 THEN
            INSERT INTO table_1 (id, name, created_at) VALUES (v_id, (SELECT name FROM original_table WHERE id = v_id), (SELECT created_at FROM original_table WHERE id = v_id));
        ELSE
            INSERT INTO table_2 (id, name, created_at) VALUES (v_id, (SELECT name FROM original_table WHERE id = v_id), (SELECT created_at FROM original_table WHERE id = v_id));
        END IF;
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

-- 调用存储过程
CALL distribute_data();