表分区sql

分区处理函数

1. 创建分区

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

2. 执行函数

CREATE DEFINER=`ry-vue`@`%` PROCEDURE `MigrateData`()
BEGIN
    DECLARE batch_size BIGINT DEFAULT 5000;
    DECLARE max_id BIGINT DEFAULT 0;
    DECLARE min_id BIGINT DEFAULT 0;
    DECLARE current_id BIGINT DEFAULT 0;
    DECLARE rows_affected BIGINT DEFAULT 1;
    DECLARE next_id BIGINT DEFAULT 0;
    
    -- 使用事务确保数据一致性
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 获取ID范围
    SELECT MIN(id), MAX(id) INTO min_id, max_id FROM mkp_trade;
    SET current_id = min_id;
    
    -- 添加循环标签
    migration_loop: WHILE current_id <= max_id DO
        -- 检查当前ID是否存在
        SELECT MIN(id) INTO next_id FROM mkp_trade WHERE id >= current_id LIMIT 1;
        
        IF next_id IS NULL THEN
            -- 使用标签退出循环
            LEAVE migration_loop;
        END IF;
        
        -- 设置实际要查询的ID范围
        SET current_id = next_id;
        SET @end_id = LEAST(current_id + batch_size - 1, max_id);
        
        -- 插入数据
        INSERT INTO mkp_trade_range
        SELECT * FROM mkp_trade 
        WHERE id BETWEEN current_id AND @end_id;
        
        -- 获取实际插入的行数
        SET rows_affected = ROW_COUNT();
        
        IF rows_affected = 0 THEN
            -- 没有插入数据，直接跳到下一批
            SET current_id = @end_id + 1;
        ELSE
            -- 成功插入数据，更新current_id
            SET current_id = @end_id + 1;
        END IF;
        
        -- 每批提交后暂停0.1秒（减少负载）
        DO SLEEP(0.1);
    END WHILE migration_loop;
    COMMIT;
    END;

3. 拷贝原始表数据到分区表

INSERT INTO partitioned_table (id, order_date, amount)
SELECT id, order_date, amount FROM original_table;

4. 验证数据

SELECT COUNT(*) FROM original_table;
SELECT COUNT(*) FROM partitioned_table;

5. 用分区表替换原始表

RENAME TABLE original_table TO original_table_backup;
RENAME TABLE partitioned_table TO original_table;