推广文案类型有哪些_网站建设公司兴田德润专业_亚马逊查关键词排名工具_网站优化工具

分区处理函数

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`()
BEGINDECLARE 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 SQLEXCEPTIONBEGINROLLBACK;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_rangeSELECT * 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_idSET 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;