自動創(chuàng)建新的分區(qū)表
-- 創(chuàng)建一張管理分區(qū)的表
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 '表分區(qū)類型0：日 1：月 沒用上',
PRIMARY KEY (`sysId`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='分區(qū)管理表 ';
-- 創(chuàng)建一個空的分區(qū)表
CREATE TABLE `test` (
`seq` varchar(20) NOT NULL COMMENT '映射編號',
`version` varchar(8) NOT NULL COMMENT '版本號',
`type` smallint(7) DEFAULT NULL COMMENT '基礎(chǔ)信息類型',
`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='基礎(chǔ)信息映射表'
PARTITION BY LIST COLUMNS(version)
(PARTITION p20210106 VALUES IN ('20210303','20210304','20210305','20210306') ENGINE = InnoDB;
-- 插入到管理分區(qū)的表中
INSERT INTO `library`.`t_bfi_partition`(`sysId`, `table_schema`, `table_name`, `partition_type`) VALUES (1, 'library', 'test', 0);
-- 創(chuàng)建一個函數(shù)
CREATE DEFINER=`root`@`%` PROCEDURE `add_partition`()
BEGIN
-- 定義需要用到的變量
DECLARE v_sysdate date;
DECLARE v_mindate date;
-- 分區(qū)值
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;
-- 遍歷數(shù)據(jù)結(jié)束標志
DECLARE done INT DEFAULT FALSE;
-- 定義游標 查詢出分區(qū)表對應(yīng)的數(shù)據(jù)信息
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;
-- 將結(jié)束標志綁定到游標
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打開游標咖祭，開始處理新增分區(qū)
OPEN cur;
read_loop: LOOP
-- 提取游標里的數(shù)據(jù)金句，分別對應(yīng)上面sql查詢的3個屬性
FETCH cur INTO tableschema,tablename,v_maxdate;
set v_sysdate = sysdate();
-- 如果最大分區(qū)的值 小于等于當前時間加2天的值 就循環(huán)創(chuàng)建分區(qū)
WHILE v_maxdate <= (v_sysdate + INTERVAL 2 DAY) DO
-- 這里是用List分區(qū) 4天為一個分區(qū) 所以4個值 對應(yīng)在上方定義的變量
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;
-- 循環(huán)的條件變更 這里對應(yīng)上面的天數(shù)
SET v_maxdate = v_maxdate + INTERVAL 4 DAY;
END WHILE;
-- 結(jié)束循環(huán)后 打印
IF done THEN
SELECT v_maxdate,'done';
LEAVE read_loop;
END IF;
END LOOP;
close cur;
END
-- 創(chuàng)建事件 每天執(zhí)行
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
在舊表改成分區(qū)表
-- 有主鍵 要先刪除主鍵
ALTER TABLE `表名test`
-- 設(shè)置新的分區(qū)建 并建立聯(lián)合主鍵
MODIFY COLUMN `time` datetime NOT NULL ,
DROP PRIMARY KEY,
ALTER TABLE `trc_laneexlist` ADD PRIMARY KEY (`id` , `time`) USING BTREE;
-- 創(chuàng)建range分區(qū)表的 time作為分區(qū)鍵
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
);
分區(qū)相關(guān)命令
-- 統(tǒng)計分區(qū)中的數(shù)據(jù)數(shù)量
select count(*) from test partition (p20200708);
-- 刪除分區(qū)
ALTER TABLE test DROP PARTITION p20200708
-- 增加分區(qū)
ALTER TABLE test ADD PARTITION (PARTITION p20200908 VALUES LESS THAN ("2020-09-08") )
-- 查看庫下面所有分區(qū)
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));
-- 整理分區(qū)碎片
ALTER TABLE test optimize partition p20200712,p20200713;
-- 修補分區(qū)索引數(shù)據(jù)
ALTER TABLE tb04 CHECK partition p1,p2;
-- 復(fù)制分區(qū)的數(shù)據(jù)到his表中
INSERT INTO test_his SELECT * FROM test PARTITION ( p20210309 )
-- 創(chuàng)建存儲過程
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;
-- 獲取表對應(yīng)的分區(qū)時間和名字
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);
-- 同步分區(qū)數(shù)據(jù)到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;
-- 刪除分區(qū)數(shù)據(jù)
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
-- 創(chuàng)建定時任務(wù)
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
