mysql按天自动生成表分区的执行语句

1CREATE TABLE `jk_data` (
2  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '数据ID主键',
3  `jk_date` datetime NOT NULL COMMENT '时间日期格式',
4  `jk_time` int(11) DEFAULT NULL COMMENT 'Unit格式的时间',
5  `jk_sn` varchar(20) DEFAULT NULL COMMENT '设备序列号SN',
6  `jk_counter` int(11) DEFAULT '0' COMMENT '设备计数器',
7  `jk_ip` varchar(50) DEFAULT NULL COMMENT '设备在网络中的ip地址',
8  `jk_isphysical` tinyint(2) DEFAULT '0' COMMENT '是否匹配了物理公式',
9  `jk_eid` tinyint(4) DEFAULT '0' COMMENT 'ID号',
10  `jk_wid` tinyint(2) DEFAULT '0' COMMENT '通道',
11  `jk_wave` int(11) DEFAULT '0' COMMENT '波长',
12  `jk_phyvalue` decimal(10,6) DEFAULT '0.000000' COMMENT '物理量',
13  `jk_serialnum` varchar(20) DEFAULT NULL COMMENT '序列号',
14  `jk_error` tinyint(2) DEFAULT '0' COMMENT '错误代码',
15  `jk_warning` tinyint(2) DEFAULT '0' COMMENT '警告代码',
16  PRIMARY KEY (`id`,`jk_date`) USING BTREE
17) ENGINE=InnoDB AUTO_INCREMENT=304894 DEFAULT CHARSET=utf8
18/*!50100 PARTITION BY RANGE (TO_DAYS(jk_date)-1)
19(PARTITION jk_data20201109 VALUES LESS THAN (738103) ENGINE = InnoDB,
20 PARTITION jk_data20201110 VALUES LESS THAN (738104) ENGINE = InnoDB,
21 PARTITION jk_data20201111 VALUES LESS THAN (738105) ENGINE = InnoDB,
22 PARTITION jk_data20201112 VALUES LESS THAN (738106) ENGINE = InnoDB,
23 PARTITION jk_data20201113 VALUES LESS THAN (738107) ENGINE = InnoDB,
24 PARTITION jk_data20201114 VALUES LESS THAN (738108) ENGINE = InnoDB,
25 PARTITION jk_data20201115 VALUES LESS THAN (738109) ENGINE = InnoDB,
26 PARTITION jk_data20201116 VALUES LESS THAN (738110) ENGINE = InnoDB,
27 PARTITION jk_data20201117 VALUES LESS THAN (738111) ENGINE = InnoDB,
28 PARTITION jk_data20201118 VALUES LESS THAN (738112) ENGINE = InnoDB) */

创建存储过程

1DROP PROCEDURE IF EXISTS topv.create_partition_jkdata;
2CREATE DEFINER=`topv`@`%` PROCEDURE `create_partition_jkdata`()
3BEGIN
4/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
5    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
6    START TRANSACTION;
7
8/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
9    SELECT REPLACE(partition_name,'jk_data','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
10    WHERE table_name='jk_data' ORDER BY partition_ordinal_position DESC LIMIT 1;
11     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
12/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
13    SET @s1=CONCAT('ALTER TABLE jk_data ADD PARTITION (PARTITION jk_data',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
14    /* 输出查看增加分区语句*/
15    SELECT @s1;
16    PREPARE stmt2 FROM @s1;
17    EXECUTE stmt2;
18    DEALLOCATE PREPARE stmt2;
19/* 取出最小的分区的名称，并删除掉 。
20    注意：删除分区会同时删除分区内的数据，慎重 */
21    /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
22    where table_name='jk_data' order by partition_ordinal_position limit 1;
23    SET @s=concat('ALTER TABLE jk_data DROP PARTITION ',@P0_Name);
24    PREPARE stmt1 FROM @s;
25    EXECUTE stmt1;
26    DEALLOCATE PREPARE stmt1; */
27/* 提交 */
28    COMMIT ;
29 END;

Done！

本文同步分享在博客“漏刻有时”（CSDN）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

mysql按天自动生成表分区的执行语句

评论区

相关推荐