mysql 修改分区表,mysql 5.7自动建立分区表 已有表改成分区表

自动创建新的分区表

-- 创建一张管理分区的表

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