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 ;