自动创建新的分区表
-- 创建一张管理分区的表
CREATE TABLE `t_bfi_partition` (
`sysId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增序列',
`table_schema` varchar(100) DEFAULT NULL COMMENT '库',
`table_name` varchar(100) DEFAULT NULL COMMENT '表名',
`partition_type` int(11) DEFAULT NULL COMMENT '表分区类型0：日 1：月 没用上',
PRIMARY KEY (`sysId`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='分区管理表 ';
-- 创建一个空的分区表
CREATE TABLE `test` (
`seq` varchar(20) NOT NULL COMMENT '映射编号',
`version` varchar(8) NOT NULL COMMENT '版本号',
`type` smallint(7) DEFAULT NULL COMMENT '基础信息类型',
`name` varchar(200) DEFAULT NULL COMMENT '名称',
`hex` varchar(10) DEFAULT NULL COMMENT 'HEX 编码字符串',
PRIMARY KEY (`seq`,`version`),
UNIQUE KEY `id_UNIQUE` (`seq`,`version`),
KEY `idx_version` (`version`,`type`),
KEY `idx_hex` (`hex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='基础信息映射表'
PARTITION BY LIST COLUMNS(version)
(PARTITION p20210106 VALUES IN ('20210303','20210304','20210305','20210306') ENGINE = InnoDB;
-- 插入到管理分区的表中
INSERT INTO `library`.`t_bfi_partition`(`sysId`, `table_schema`, `table_name`, `partition_type`) VALUES (1, 'library', 'test', 0);
-- 创建一个函数
CREATE DEFINER=`root`@`%` PROCEDURE `add_partition`()
BEGIN
-- 定义需要用到的变量
DECLARE v_sysdate date;
DECLARE v_mindate date;
-- 分区值
DECLARE v_pt1 varchar(20);
DECLARE v_pt2 varchar(20);
DECLARE v_pt3 varchar(20);
DECLARE v_pt4 varchar(20);
DECLARE partitionName varchar(20);
DECLARE v_maxval varchar(20);
DECLARE i int;
DECLARE tableschema VARCHAR(255);
DECLARE tablename VARCHAR(255);
DECLARE v_maxdate date;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 定义游标 查询出分区表对应的数据信息
DECLARE cur CURSOR FOR
SELECT
a.table_schema, a.table_name,
max(cast( REPLACE ( substring_index( partition_description, ',',- 1 ), '''', '' ) AS date )) AS val
FROM
INFORMATION_SCHEMA.PARTITIONS a, fhboot.T_BFI_PARTITION b
WHERE
a.table_schema = b.table_schema AND a.table_name = b.table_name AND b.partition_type = 0
GROUP BY table_schema,table_name;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标，开始处理新增分区
OPEN cur;
read_loop: LOOP
-- 提取游标里的数据，分别对应上面sql查询的3个属性
FETCH cur INTO tableschema,tablename,v_maxdate;
set v_sysdate = sysdate();
-- 如果最大分区的值 小于等于当前时间加2天的值 就循环创建分区
WHILE v_maxdate <= (v_sysdate + INTERVAL 2 DAY) DO
-- 这里是用List分区 4天为一个分区 所以4个值 对应在上方定义的变量
SET v_pt1 = date_format(v_maxdate+ INTERVAL 1 DAY ,'%Y%m%d');
SET v_pt2 = date_format(v_maxdate+ INTERVAL 2 DAY ,'%Y%m%d');
SET v_pt3 = date_format(v_maxdate+ INTERVAL 3 DAY ,'%Y%m%d');
SET v_pt4 = date_format(v_maxdate+ INTERVAL 4 DAY ,'%Y%m%d');
SET partitionName = date_format(v_maxdate + INTERVAL 4 DAY, '%Y%m%d');
--拼接sql语句
SET @sql = concat('alter table ',tableschema,'.',tablename,' add partition (partition p', partitionName, ' values in(''', v_pt1, ''',''', v_pt2, ''',''', v_pt3, ''',''', v_pt4, '''))');
-- SELECT @sql; -- 需要打印sql就放开
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- select v_maxdate;
-- 循环的条件变更 这里对应上面的天数
SET v_maxdate = v_maxdate + INTERVAL 4 DAY;
END WHILE;
-- 结束循环后 打印
IF done THEN
SELECT v_maxdate,'done';
LEAVE read_loop;
END IF;
END LOOP;
close cur;
END
-- 创建事件 每天执行
CREATE DEFINER=`root`@`%` EVENT `Partition_add_event` ON SCHEDULE EVERY 1 DAY STARTS '2021-03-08 18:10:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
CALL library.add_partition();
END
在旧表改成分区表
-- 有主键 要先删除主键
ALTER TABLE `表名test`
-- 设置新的分区建 并建立联合主键
MODIFY COLUMN `time` datetime NOT NULL ,
DROP PRIMARY KEY,
ALTER TABLE `trc_laneexlist` ADD PRIMARY KEY (`id` , `time`) USING BTREE;
-- 创建range分区表的 time作为分区键
alter TABLE `表名test`
PARTITION BY RANGE COLUMNS(time)
(
PARTITION p20210308 VALUES LESS THAN ('2021-03-08') ENGINE = InnoDB,
PARTITION p20210309 VALUES LESS THAN ('2021-03-09') ENGINE = InnoDB
);
分区相关命令
-- 统计分区中的数据数量
select count(*) from test partition (p20200708);
-- 删除分区
ALTER TABLE test DROP PARTITION p20200708
-- 增加分区
ALTER TABLE test ADD PARTITION (PARTITION p20200908 VALUES LESS THAN ("2020-09-08") )
-- 查看库下面所有分区
select TABLE_NAME,PARTITION_NAME from information_schema.partitions where table_schema='branchdb';
-- 把上面的tb05表中分解的p01和p02合并至p1上
alter table test reorganize partition p01,p02 into(partition p1 values less than (1000));
-- 整理分区碎片
ALTER TABLE test optimize partition p20200712,p20200713;
-- 修补分区索引数据
ALTER TABLE tb04 CHECK partition p1,p2;
-- 复制分区的数据到his表中
INSERT INTO test_his SELECT * FROM test PARTITION ( p20210309 )
-- 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `backUp_partition_test`()
BEGIN
DECLARE v_sysdate date;
DECLARE v_mindate date;
DECLARE v_pt varchar(20);
DECLARE v_maxval varchar(20);
DECLARE kubiaoName varchar(50);
DECLARE i int;
DECLARE tableschema VARCHAR(255);
DECLARE tablename VARCHAR(255);
DECLARE partitionname VARCHAR(255);
DECLARE v_maxdate date;
DECLARE done INT DEFAULT FALSE;
-- 获取表对应的分区时间和名字
DECLARE cur CURSOR FOR SELECT a.table_schema,a.table_name,min(PARTITION_NAME) as partitionname,min(cast(replace(partition_description, '''', '') AS date)) AS val FROM INFORMATION_SCHEMA.PARTITIONS a,branchdb.T_BFI_PARTITION b where a.table_schema=b.table_schema and a.table_name=b.table_name and b.partition_type=0 group by table_schema,table_name having count(1)>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE; OPEN cur; read_loop:
LOOP FETCH cur INTO tableschema,tablename,partitionname,v_maxdate;
SET v_sysdate = cast(sysdate() AS date);
SET kubiaoName = concat(tableschema,'.',tablename);
-- 同步分区数据到his
IF v_maxdate <= (v_sysdate - INTERVAL 60 DAY) THEN
SET @sql = concat('INSERT INTO ',kubiaoName,'_his ',' SELECT * FROM ',kubiaoName,' PARTITION (',partitionname,')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 删除分区数据
SET @sql_dpop = concat('ALTER TABLE ',kubiaoName,' DROP PARTITION ',partitionname);
PREPARE stmt2 FROM @sql_dpop;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END IF;
IF done THEN
SELECT 'done';
LEAVE read_loop;
END IF;
END LOOP;
close cur;
END
-- 创建定时任务
CREATE EVENT backUp_partition_success ON SCHEDULE EVERY 1 DAY STARTS '2020-08-14 00:02:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
CALL branchdb.backUp_partition();
END
