在目标数据实例创建一个库

`partition_cfg`

创建两张表

`partition_table_cfg`

`partition_table_log`

建表语句如下

CREATE TABLE `sys_partition_table_cfg` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`table_name` varchar(200) DEFAULT NULL,

`partition_key` varchar(64) DEFAULT NULL COMMENT '表分区字段',

`partition_type` int(10) DEFAULT NULL COMMENT '表分区字段的类型 0:timestamp,1:datetime,2:bigint',

`is_partition` int(10) DEFAULT NULL COMMENT '是否已创建分区表',

`partition_num` int(10) DEFAULT NULL COMMENT '分区字段为日期时初始化历史数据分区数,其它为0',

`schema_name_pre` varchar(64) DEFAULT NULL COMMENT '表模式名称',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `sys_partition_table_log` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`table_name` varchar(100) NOT NULL,

`is_success` varchar(200) NOT NULL,

`create_time` datetime NOT NULL COMMENT '创建时间',

`TABLE_SCHEMA` varchar(64) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16429 DEFAULT CHARSET=utf8

创建三个存储过程

`cfg_init`

`create_partition_event`

`create_partition_init`

具体代码如下

DELIMITER $

USE `partition_cfg`$

DROP PROCEDURE IF EXISTS `cfg_init`$

CREATE PROCEDURE `cfg_init`(

IN `v_table_name` VARCHAR(200) CHARSET utf8 COLLATE utf8_general_ci ,IN `v_partition_key` VARCHAR(64) CHARSET utf8 COLLATE utf8_general_ci ,IN `v_schema_name_pre` VARCHAR(64) CHARSET utf8 COLLATE utf8_general_ci ,IN `v_DROP_NUMBER` INT(11) )

SQL SECURITY INVOKER

BEGIN

DECLARE v_partition_type INT (10) ;

DECLARE v_is_partition INT (10) ;

DECLARE v_partition_num INT (10) ;

DECLARE v_number INT (10) ;

DECLARE v_number1 INT (10) ;

DECLARE v_sql VARCHAR (1000) ;

SELECT

COUNT(*) INTO v_number1

FROM

partition_cfg.sys_partition_table_cfg

WHERE table_name = v_table_name

AND schema_name_pre = v_schema_name_pre

AND partition_key = v_partition_key ;

IF v_number1 = 0

THEN SET v_sql = CONCAT(

'SELECT COUNT(*) into @v_number FROM information_schema.COLUMNS WHERE table_schema LIKE ',

'''',

'%',

v_schema_name_pre,

'%',

'''',

' AND table_name= ',

'''',

v_table_name,

'''',

' AND column_name=',

'''',

v_partition_key,

'''',

' AND data_type=',

'''',

'datetime',

''''

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

SET v_number = @v_number ;

END ;

-- 初始化历史分区数,分区键类型

IF v_number > 0

THEN SET v_partition_type = 1 ;

SET v_is_partition = 0 ;

SET v_partition_num = 31 ;

-- 初始化数据

INSERT INTO partition_cfg.sys_partition_table_cfg (

table_name,

partition_key,

partition_type,

is_partition,

partition_num,

schema_name_pre

)

VALUES

(

v_table_name,

v_partition_key,

v_partition_type,

v_is_partition,

v_partition_num,

v_schema_name_pre

) ;

COMMIT ;

END IF ;

END IF ;

-- 删除分区初始化配置

IF v_DROP_NUMBER >0 THEN

-- 初始化数据

INSERT INTO partition_cfg.drop_partitiontable_cfg (

TABLE_NAME,SCHEMA_NAME_PRE,DROP_NUMBER

)

VALUES

(

v_table_name,

v_schema_name_pre,

v_drop_number

) ;

COMMIT ;

END IF;

END$

DELIMITER ;

DELIMITER $

2

USE `scm_partition_cfg`$

DROP PROCEDURE IF EXISTS `create_partition_event`$

CREATE PROCEDURE `create_partition_event`(

)

SQL SECURITY INVOKER

BEGIN

/*声明变量*/

DECLARE table_partition_name VARCHAR (100) ;

DECLARE v_number INT (10) ;

DECLARE v_sql VARCHAR (1000) ;

DECLARE v_table_name VARCHAR (200) ;

DECLARE v_partition_type INT (10) ;

DECLARE v_table_schema VARCHAR (200) ;

DECLARE v_schema_name_type VARCHAR (64) ;

-- 遍历数据结束标志

DECLARE done INT DEFAULT FALSE ;

-- 游标

DECLARE cur CURSOR FOR

SELECT

table_name,

partition_type,

schema_name_pre

FROM

partition_cfg.sys_partition_table_cfg

WHERE is_partition = 1 ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;

-- 打开游标

OPEN cur ;

-- 开始循环

read_loop :

LOOP

FETCH cur INTO v_table_name,

v_partition_type,

v_schema_name_type ;

-- 声明结束的时候

IF done

THEN LEAVE read_loop ;

END IF ;

-- 循环每个模式处理

-- 遍历数据结束标志

BEGIN

DECLARE done_schema INT DEFAULT FALSE ;

-- 游标

DECLARE cur_schema CURSOR FOR

SELECT DISTINCT

a.table_schema

FROM

information_schema.tables a,

information_schema.partitions b

WHERE a.table_name = v_table_name

AND a.table_schema LIKE CONCAT('%', v_schema_name_type, '%')

AND a.table_name = b.table_name

AND a.table_schema = b.table_schema

AND b.partition_name IS NOT NULL ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_schema = TRUE ;

-- 打开游标

OPEN cur_schema ;

-- 开始循环

read_loop_schema :

LOOP

FETCH cur_schema INTO v_table_schema ;

-- 声明结束的时候

IF done_schema

THEN LEAVE read_loop_schema ;

END IF ;

/*创建分区表*/

-- 获取当前分区最大分区号对应的分区名

SELECT

CONCAT(

'p',

SUBSTRING(partition_name, 2) + 1

) INTO table_partition_name

FROM

information_schema.PARTITIONS

WHERE table_name = v_table_name

AND table_schema = v_table_schema

ORDER BY partition_ordinal_position DESC

LIMIT 1 ;

-- 获取4天后分区键对应的分区值

IF v_partition_type = 0

THEN

SELECT

UNIX_TIMESTAMP(

DATE_FORMAT(

DATE_ADD(NOW(), INTERVAL 5 DAY),

'%Y-%m-%d'

)

) INTO v_number ;

ELSEIF v_partition_type = 1

THEN

SELECT

TO_DAYS(

DATE_FORMAT(

DATE_ADD(NOW(), INTERVAL 5 DAY),

'%Y-%m-%d'

)

) INTO v_number ;

ELSE

SELECT

UNIX_TIMESTAMP(

DATE_FORMAT(

DATE_ADD(NOW(), INTERVAL 5 DAY),

'%Y-%m-%d'

)

) * 1000 INTO v_number ;

END IF ;

-- 拼接创建分区表的sql语句

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

' ADD PARTITION (PARTITION ',

table_partition_name,

' VALUES LESS THAN (',

v_number,

') ENGINE = INNODB)'

) ;

-- 异常处理

BEGIN

DECLARE EXIT HANDLER FOR 1493

INSERT INTO `partition_cfg`.`sys_partition_table_log` (

TABLE_NAME,

IS_SUCCESS,

CREATE_TIME,

table_schema

)

VALUES

(

v_table_name,

'VALUES LESS THAN value must be strictly increasing for each partition',

NOW(),

v_table_schema

) ;

-- 执行动态sql

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

INSERT INTO `partition_cfg`.`sys_partition_table_log` (

TABLE_NAME,

IS_SUCCESS,

CREATE_TIME,

table_schema

)

VALUES

(

v_table_name,

'add partition sucess',

NOW(),

v_table_schema

) ;

END ;

END LOOP ;

-- 关闭游标

CLOSE cur_schema ;

END ;

END LOOP ;

-- 关闭游标

CLOSE cur ;

END$

DELIMITER ;

3

DELIMITER $

USE `partition_cfg`$

DROP PROCEDURE IF EXISTS `create_partition_init`$

CREATE PROCEDURE `create_partition_init`(

)

SQL SECURITY INVOKER

BEGIN

/*声明变量*/

DECLARE v_event_scheduler VARCHAR (10) ;

DECLARE v_sql TEXT ;

DECLARE v_sql1 TEXT DEFAULT '' ;

DECLARE v_sql2 TEXT DEFAULT '' ;

DECLARE v_table_name VARCHAR (256) ;

DECLARE v_table_schema VARCHAR (256) ;

DECLARE v_partition_column VARCHAR (32) ;

DECLARE v_partition_num INT (10) ;

DECLARE v_partition_name VARCHAR (32) ;

DECLARE v_partition_name_no INT UNSIGNED DEFAULT 1 ;

DECLARE v_table_primary VARCHAR (64) ;

DECLARE v_partition_no INT (10) ;

DECLARE v_constraint_name VARCHAR (64) ;

DECLARE v_partition_type INT (10) ;

DECLARE v_constraint_table_name VARCHAR (64) ;

DECLARE v_schema_name_pre VARCHAR (64) ;

DECLARE v_auto_increment INT (10) ;

DECLARE v_column_name VARCHAR (32) ;

DECLARE v_table_old_num INT(10);

-- 1:判断event_scheduler是否开启

SELECT

@@global.event_scheduler INTO v_event_scheduler ;

-- 如果event_scheduler没有开启,分区任务不做

IF v_event_scheduler = 'OFF'

THEN

INSERT INTO sys_partition_table_log (

table_name,

is_success,

create_time

)

VALUES

(

'event_scheduler',

'event_scheduler is OFF',

NOW()

) ;

-- 如果event_scheduler开启,分区任务开始

-- 2:表分区初始化处理

ELSE

BEGIN

-- 2.1循环处理每个表

-- 遍历数据结束标志

DECLARE done_table INT DEFAULT FALSE ;

-- 游标

DECLARE cur_table CURSOR FOR

SELECT

table_name,

partition_key,

-- partition_num,

partition_type,

schema_name_pre

FROM

partition_cfg.sys_partition_table_cfg

WHERE is_partition = 0 ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_table = TRUE ;

-- 打开游标

OPEN cur_table ;

-- 开始循环

read_loop_table :

LOOP

FETCH cur_table INTO v_table_name,

v_partition_column,

-- v_partition_num,

v_partition_type,

v_schema_name_pre ;

-- 声明结束的时候

IF done_table

THEN LEAVE read_loop_table ;

END IF ;

-- 2.1.1循环每个模式处理

-- 遍历数据结束标志

BEGIN

DECLARE done_schema INT DEFAULT FALSE ;

-- 游标

DECLARE cur_schema CURSOR FOR

SELECT

table_schema

FROM

information_schema.tables

WHERE table_name = v_table_name

AND table_schema LIKE CONCAT('%', v_schema_name_pre, '%') ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_schema = TRUE ;

-- 打开游标

OPEN cur_schema ;

-- 开始循环

read_loop_schema :

LOOP

FETCH cur_schema INTO v_table_schema ;

-- 声明结束的时候

IF done_schema

THEN LEAVE read_loop_schema ;

END IF ;

-- 重置保留天数为默认值

SELECT

partition_num INTO v_partition_num

FROM

partition_cfg.sys_partition_table_cfg

WHERE table_name = v_table_name

LIMIT 1 ;

-- 2.1.1.1删除外键

BEGIN

DECLARE done_fk INT DEFAULT FALSE ;

-- 游标

DECLARE cur_fk CURSOR FOR

SELECT

constraint_name,

table_name

FROM

information_schema.REFERENTIAL_CONSTRAINTS

WHERE constraint_schema = v_table_schema

AND (

table_name = v_table_name

OR referenced_table_name = v_table_name

) ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_fk = TRUE ;

-- 打开游标

OPEN cur_fk ;

-- 开始循环

read_loop_fk :

LOOP

FETCH cur_fk INTO v_constraint_name,

v_constraint_table_name ;

-- 声明结束的时候

IF done_fk

THEN LEAVE read_loop_fk ;

END IF ;

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_constraint_table_name,

' DROP FOREIGN KEY ',

v_constraint_name

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

END LOOP ;

-- 关闭游标

CLOSE cur_fk ;

END ;

-- 2.1.1.2创建分区表new

-- 创建new表

SET v_sql = CONCAT(

'CREATE TABLE ',

v_table_schema,

'.',

v_table_name,

'_new like ',

v_table_schema,

'.',

v_table_name

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

-- 2.1.1.3删除new表主键

SELECT

COUNT(*) INTO v_auto_increment

FROM

information_schema.COLUMNS

WHERE table_schema = v_table_schema

AND table_name = v_table_name

AND extra = 'auto_increment' ;

IF v_auto_increment = 1

THEN

SELECT

column_name INTO v_column_name

FROM

information_schema.COLUMNS

WHERE table_schema = v_table_schema

AND table_name = v_table_name

AND extra = 'auto_increment' ;

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

'_new',

' MODIFY ',

v_column_name,

' BIGINT(20)'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

END IF ;

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

'_new',

' DROP PRIMARY KEY'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

BEGIN

-- 2.1.1.4添加新主键

-- 遍历数据结束标志

DECLARE done_primary INT DEFAULT FALSE ;

-- 游标

DECLARE cur_primary CURSOR FOR

SELECT

column_name

FROM

information_schema.`KEY_COLUMN_USAGE`

WHERE constraint_schema = v_table_schema

AND table_name = v_table_name

AND constraint_name = 'PRIMARY' ;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_primary = TRUE ;

-- 打开游标

OPEN cur_primary ;

-- 开始循环

read_loop_primary :

LOOP

FETCH cur_primary INTO v_table_primary ;

-- 声明结束的时候

IF done_primary

THEN LEAVE read_loop_primary ;

END IF ;

-- 拼接主键sql字符串

SET v_sql1 = CONCAT(v_sql1, ',', v_table_primary) ;

END LOOP ;

-- 关闭游标

CLOSE cur_primary ;

END ;

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

'_new',

' ADD PRIMARY KEY(',

SUBSTRING(v_sql1, 2),

',',

v_partition_column,

')'

) ;

-- 执行动态sql,创建主键

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

-- 2.1.1.5执行创建分区表语句

IF v_partition_type = 1

THEN SET v_partition_name = CONCAT('p', v_partition_name_no) ;

SET v_partition_no = TO_DAYS('2003-01-01');

SET v_sql1 = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

'_new',

' PARTITION BY RANGE (TO_DAYS(',

v_partition_column,

'))

(PARTITION ',

v_partition_name,

' VALUES LESS THAN (',

v_partition_no,

') ENGINE = INNODB,'

) ;

-- 执行动态sql，创建分区表

-- 创建分区冗余到未来第四天，v_partition_num > -5

WHILE

v_partition_num > - 5 DO SET v_partition_name_no = v_partition_name_no + 1 ;

SET v_partition_name = CONCAT('p', v_partition_name_no) ;

SET v_partition_num = v_partition_num - 1 ;

SET v_partition_no = TO_DAYS(

DATE_SUB(NOW(), INTERVAL v_partition_num DAY)

) ;

SET v_sql2 = CONCAT(

v_sql2,

' PARTITION ',

v_partition_name,

' VALUES LESS THAN (',

v_partition_no,

') ENGINE = INNODB,'

) ;

END WHILE ;

SET v_sql = CONCAT(

v_sql1,

TRIM(TRAILING ',' FROM v_sql2),

')'

) ;

-- 执行动态sql,创建分区表

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

ELSE

INSERT INTO scm_partition_cfg.sys_partition_table_log (

TABLE_NAME,

IS_SUCESS,

CREATE_TIME

)

VALUES

(

v_table_name,

'Temporary does not support',

NOW()

) ;

END IF ;

-- 2.1.1.6 修改原表名为后缀old

SELECT

COUNT(*) INTO v_auto_increment

FROM

information_schema.COLUMNS

WHERE table_schema = v_table_schema

AND table_name = v_table_name

AND extra = 'auto_increment' ;

IF v_auto_increment = 1

THEN

SELECT

column_name INTO v_column_name

FROM

information_schema.COLUMNS

WHERE table_schema = v_table_schema

AND table_name = v_table_name

AND extra = 'auto_increment' ;

SET v_sql = CONCAT(

'ALTER TABLE ',

v_table_schema,

'.',

v_table_name,

'_new',

' MODIFY ',

v_column_name,

' BIGINT(20) AUTO_INCREMENT'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

END IF ;

SELECT

COUNT(*) INTO v_table_old_num

FROM

information_schema.tables

WHERE table_name = CONCAT(v_table_name,'_old')

AND table_schema=v_table_schema;

IF v_table_old_num =1 THEN

SET v_sql = CONCAT(

'drop table ',

v_table_schema,

'.',

v_table_name,

'_old'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

END IF;

SET v_sql = CONCAT(

'alter table ',

v_table_schema,

'.',

v_table_name,

' rename to ',

v_table_schema,

'.',

v_table_name,

'_old'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

-- 2.1.1.7 修改new表为原表名

SET v_sql = CONCAT(

'alter table ',

v_table_schema,

'.',

v_table_name,

'_new',

' rename to ',

v_table_schema,

'.',

v_table_name

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

-- 2.1.1.8 通过insert into导入old数据

SET v_sql = CONCAT(

'insert into ',

v_table_schema,

'.',

v_table_name,

' select * from ',

v_table_schema,

'.',

v_table_name,

'_old'

) ;

-- 执行动态sql

BEGIN

SET @v_sql = v_sql ;

PREPARE stmt FROM @v_sql ;

EXECUTE stmt ;

DEALLOCATE PREPARE stmt ;

END ;

-- 初始化sql字符串为''

SET v_partition_name = '' ;

SET v_partition_name_no = 1 ;

SET v_partition_no = 0 ;

SET v_sql = '' ;

SET v_sql1 = '' ;

SET v_sql2 = '' ;

END LOOP ;

-- 关闭游标

CLOSE cur_schema ;

END ;

-- 更新配置表

UPDATE

scm_partition_cfg.sys_partition_table_cfg

SET

is_partition = 1

WHERE table_name = v_table_name ;

END LOOP ;

-- 关闭游标

CLOSE cur_table ;

END ;

END IF ;

END$

DELIMITER ;

创建一个job

`event_create_partition_event`

DELIMITER $

create EVENT `event_sp_create_partition_event` ON SCHEDULE EVERY 1 DAY STARTS now() ON COMPLETION PRESERVE ENABLE DO BEGIN

CALL partition_cfg.sp_create_partition_event();

END$

DELIMITER ;