2.ORACLE分区表迁移MySQL分区表

介绍

由于MySQL没有类似于ORACLE间隔分区类似的功能,所以迁移分区表的时候工作量较大,下面就把常用到的一些工具脚本及操作过程列在下面。

操作

1 修改分区表定义

ORACLE增加了间隔分区功能,可以在数据插入的时候自动添加分区,但是MySQL就不具备这个功能,因为是数据迁移,所以需要提前将ORACLE的表分区在MySQL表中进行创建,为了实现这部分功能我采用以下几步过程。

1.1 创建MySQL分区表

RANGE分区表结构如下,采用了ORACLE间隔分区技术的表定义:

create table TEST1
(
  userid     VARCHAR2(2000),
  time       DATE,
  deptid  VARCHAR2(3000),
  state      VARCHAR2(100),
  updatetime DATE,
  deptjson   VARCHAR2(4000)
)PARTITION BY RANGE(time)
INTERVAL(NUMTODSINTERVAL(7,'DAY'))
STORE IN (TEST1)
(PARTITION TEST1 VALUES LESS THAN (TO_DATE('2014-11-20','YYYY-MM-DD'))TABLESPACE TEST1);

软后修改表定义为MySQL版本

CREATE TABLE TEST1 (
	userid VARCHAR(2000),
	time DATE,
	deptid VARCHAR(3000),
	state VARCHAR(100),
	updatetime DATE,
	deptjson VARCHAR(4000)
)
PARTITION BY RANGE COLUMNS(time) (
	PARTITION part20141120 VALUES LESS THAN ('2014-11-20'),
	PARTITION pmax VALUES LESS THAN (MAXVALUE) # 这个分区必须有,用于新增分区
)

1.2 创建添加分区脚本

DELIMITER ;;
	
	USE `atdatabase`;;
	
	DROP PROCEDURE IF EXISTS `add_all_partitions`;;
	
	CREATE PROCEDURE add_all_partitions (
		IN tablename VARCHAR(20), 
		IN begintime VARCHAR(20), 
		IN endtime VARCHAR(20), 
		IN intervalday INT
	)
	BEGIN
		WHILE str_to_date(@begintime, '%Y%m%d') <= str_to_date(@endtime, '%Y%m%d') DO
		SET @date_begintime = str_to_date(@begintime, '%Y%m%d%');
		SET @s1 = CONCAT('ALTER TABLE ', @tablename, ' REORGANIZE PARTITION pmax INTO (PARTITION part', @begintime, ' VALUES LESS THAN (''', DATE(@date_begintime), '''),PARTITION pmax VALUES LESS THAN(MAXVALUE))');
		SELECT @s1;
		PREPARE stmt FROM @s1;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		SET @begintime = DATE_FORMAT(DATE_ADD(@date_begintime, INTERVAL intervalday DAY), '%Y%m%d');
		END WHILE;
	END;;
DELIMITER ;;

测试代码

SET @tablename='test1';
SET @begintime=20180802;
SET @endtime=20180902;
SET @intervalday=7;
CALL add_all_partitions(@tablename,@begintime,@endtime,@intervalday);

1.3 编写定时添加分区脚本

DELIMITER $$
	
USE `atdatabase`$$
	
DROP PROCEDURE IF EXISTS `add_partition`$$
	
CREATE PROCEDURE `add_partition` (
	IN tablename VARCHAR(20), 
	IN intervalday INT
)
BEGIN
	/* 到系统表查出这个表的最大分区,得到最大分区的日期。在创建分区的时候,名称就以日期格式存放,方便后面维护 */
	SELECT REPLACE(partition_name, 'part', '')
	INTO @P12_Name
	FROM INFORMATION_SCHEMA.PARTITIONS
	WHERE table_name = @tablename
		AND partition_name != 'pmax'
	ORDER BY partition_ordinal_position DESC
	LIMIT 1;
	SET @Max_date = DATE(DATE_ADD(@P12_Name + 0, INTERVAL intervalday DAY)) + 0;
	SET @s1 = CONCAT('ALTER TABLE ', @tablename, ' REORGANIZE PARTITION pmax INTO (PARTITION part', @Max_date, ' VALUES LESS THAN (''', DATE(@Max_date), '''),PARTITION pmax VALUES LESS THAN(MAXVALUE))');
	/* 输出查看增加分区语句*/
	SELECT @s1;
	PREPARE stmt FROM @s1;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END$$
	
DELIMITER ;

测试代码

SET @tablename='test1';
SET @intervalday=7;
CALL add_partition(@tablename,@intervalday);

1.4 添加定时器

DELIMITER ;;  
CREATE EVENT partition_event  
          ON SCHEDULE  
          EVERY 7 day STARTS '2018-10-26 23:59:59'   
          DO  
      BEGIN  

          CALL add_partition; 
        
 END ;;  
DELIMITER ;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值