MySQL自动化分区新增数字时间戳类型
时间：09-11来源：作者：点击数：
文章描述了一个针对Zabbix的history表进行MySQL分区的自动化过程，特别是增加了对数字时间戳类型的兼容性。通过修改存储过程`partition_maintenance`，实现了根据int类型的时间戳字段`clock`进行分区，并展示了不同时间戳类型的分区SQL示例。
背景：MySQL自动化分区现阶段只支持datetime类型，需要添加数字类时间戳类型，用于zabbix的history历史表数据自动化分区。
1.zibbix的历史表结构
CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2.修改存储过程部分
partition_maintenance
DELIMITER $$
USE `managerdb`$$
DROP PROCEDURE IF EXISTS `partition_maintenance`$$
CREATE DEFINER=`root`@`%` PROCEDURE `partition_maintenance`(p_schema_name VARCHAR(32), p_table_name VARCHAR(32), p_keep_data_days INT, p_create_next_intervals INT,p_par_column VARCHAR(64))
BEGIN
/*
*/
DECLARE l_older_than_partition_date VARCHAR(16);
DECLARE l_partition_name VARCHAR(16);
DECLARE l_old_partition_name VARCHAR(16);
DECLARE l_less_than_timestamp DATETIME;
DECLARE l_cur_time VARCHAR(64);
DECLARE l_partition_type VARCHAR(16);
-- 数字时间戳新增
-- 检查分区字段类型
SELECT data_type INTO l_partition_type
FROM information_schema.columns
WHERE table_schema = p_schema_name
AND table_name = p_table_name
AND column_name = p_par_column;
#验证是否为分区表单独执行
CALL partition_verify_new(p_schema_name, p_table_name, p_keep_data_days,p_par_column,p_create_next_intervals); -- 验证TABLE_NAME表是否为分区表。如果不是则转换为分区表
SET l_cur_time = DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL p_keep_data_days DAY) , '%Y-%m-%d 00:00:00');
SET @__interval = 1;
create_loop: LOOP
IF @__interval > p_create_next_intervals+p_keep_data_days THEN -- 如果__interval大于一次创建14个分区，则退出
LEAVE create_loop;
END IF;
SET l_less_than_timestamp = DATE_ADD(l_cur_time, INTERVAL @__interval DAY);
SET l_partition_name = DATE_FORMAT(DATE_ADD(l_cur_time, INTERVAL @__interval-1 DAY), 'p%Y%m%d'); -- 获取当前分区表名称
IF(l_partition_name != l_old_partition_name) THEN
-- 数字时间戳新增
IF l_partition_type = 'int' THEN
CALL partition_create(p_schema_name, p_table_name, l_partition_name, UNIX_TIMESTAMP(l_less_than_timestamp)); -- 创建当前分区表
ELSE
CALL partition_create(p_schema_name, p_table_name, l_partition_name, TO_DAYS(l_less_than_timestamp)); -- 创建当前分区表
END IF;
END IF;
SET @__interval=@__interval+1;
SET l_old_partition_name = l_partition_name;
END LOOP;
SET l_older_than_partition_date=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL p_keep_data_days DAY), '%Y%m%d'); -- 201608150000 获取最小分区时间
CALL partition_drop(p_schema_name, p_table_name, l_older_than_partition_date); -- 删除过期分区
UPDATE par_info -- 更新配置表
SET `update_time` = NOW(),
`min_partition_name` = CONCAT('p',l_older_than_partition_date),
`max_partition_name` = l_partition_name
WHERE `table_schema` =p_schema_name
AND `table_name`= p_table_name;
END$$
DELIMITER ;
partition_verify_new
DELIMITER $$
USE `managerdb`$$
DROP PROCEDURE IF EXISTS `partition_verify_new`$$
CREATE DEFINER=`root`@`%` PROCEDURE `partition_verify_new`(p_schema_name VARCHAR(64), p_table_name VARCHAR(64),p_keep_data_days INT, p_par_column VARCHAR(64),p_create_next_intervals INT)
BEGIN
/*
*/
DECLARE l_partition_name VARCHAR(16);
DECLARE l_partition_type VARCHAR(16);
DECLARE l_retrows INT(11);
DECLARE l_future_timestamp TIMESTAMP;
-- 检查该表是不是分区表
SELECT COUNT(1)
INTO l_retrows
FROM information_schema.partitions
WHERE table_schema = p_schema_name
AND table_name = p_table_name
AND l_partition_name IS NULL;
-- 数字时间戳新增
-- 检查分区字段类型
SELECT data_type INTO l_partition_type
FROM information_schema.columns
WHERE table_schema = p_schema_name
AND table_name = p_table_name
AND column_name = p_par_column;
IF l_partition_type = "int" THEN
SET @__PARTITION_FUN="UNIX_TIMESTAMP";
ELSE
SET @__PARTITION_FUN="TO_DAYS";
END IF;
IF l_retrows = 1 THEN
-- 数字时间戳新增
IF l_partition_type = 'int' THEN
-- ALTER TABLE zabbix.history partition by range(`clock`);
SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, CONCAT(" PARTITION BY RANGE(",p_par_column,")("));
ELSE
-- alter table zabbix.history partition by range(to_days(clock));
SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, CONCAT(" PARTITION BY RANGE(TO_DAYS(",p_par_column,"))("));
END IF;
SET @__TEMP_DAYS =p_keep_data_days;
verify_loop: LOOP
-- 将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中
SET l_future_timestamp = TIMESTAMPADD(HOUR, 24, CONCAT(DATE_SUB(CURDATE(),INTERVAL @__TEMP_DAYS DAY), " ", '00:00:00'));
SET l_partition_name = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL @__TEMP_DAYS DAY), 'p%Y%m%d');
-- 数字时间戳新增
-- ALTER TABLE track add PARTITION (PARTITION p201708160002 VALUES LESS THAN ( TO_DAYS('2017-11-25 00:00:00')));
SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "PARTITION ", l_partition_name, " VALUES LESS THAN (",@__PARTITION_FUN,"('", l_future_timestamp, "')),");
SET @__TEMP_DAYS=@__TEMP_DAYS-1;
IF @__TEMP_DAYS<-p_create_next_intervals
THEN
LEAVE verify_loop;
END IF;
END LOOP;
SET @__PARTITION_SQL = CONCAT(SUBSTRING(@__PARTITION_SQL,1,LENGTH(@__PARTITION_SQL)-1),');');
SELECT @__PARTITION_SQL;
PREPARE STMT FROM @__PARTITION_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END$$
DELIMITER ;
3.运行结果
-- 分区字段类型：int (数字时间戳)
ALTER TABLE zabbix.history PARTITION BY RANGE(clock)(PARTITION p20230521 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-22 00:00:00')),
PARTITION p20230522 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-23 00:00:00')),PARTITION p20230523 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-24 00:00:00')),
PARTITION p20230524 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-25 00:00:00')),PARTITION p20230525 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-26 00:00:00')),
PARTITION p20230526 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-27 00:00:00')),PARTITION p20230527 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-28 00:00:00')),
PARTITION p20230528 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-29 00:00:00')),PARTITION p20230529 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-30 00:00:00')),
PARTITION p20230530 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),PARTITION p20230531 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')),
PARTITION p20230601 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-02 00:00:00')),PARTITION p20230602 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-03 00:00:00')),
PARTITION p20230603 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-04 00:00:00')),PARTITION p20230604 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-05 00:00:00')),
PARTITION p20230605 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-06 00:00:00')),PARTITION p20230606 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-07 00:00:00')),
PARTITION p20230607 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-08 00:00:00')),PARTITION p20230608 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-09 00:00:00')),
PARTITION p20230609 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')),PARTITION p20230610 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-11 00:00:00')),
PARTITION p20230611 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-12 00:00:00')),PARTITION p20230612 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-13 00:00:00')),
PARTITION p20230613 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-14 00:00:00')),PARTITION p20230614 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-15 00:00:00')),
PARTITION p20230615 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-16 00:00:00')),PARTITION p20230616 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-17 00:00:00')),
PARTITION p20230617 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-18 00:00:00')),PARTITION p20230618 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-19 00:00:00')),
PARTITION p20230619 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')),
PARTITION p20230620 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-21 00:00:00')));
-- 分区字段类型：datetime
ALTER TABLE zabbix.history2 PARTITION BY RANGE(TO_DAYS(clock))(PARTITION p20230530 VALUES LESS THAN (TO_DAYS('2023-05-31 00:00:00')),
PARTITION p20230531 VALUES LESS THAN (TO_DAYS('2023-06-01 00:00:00')),PARTITION p20230601 VALUES LESS THAN (TO_DAYS('2023-06-02 00:00:00')),
PARTITION p20230602 VALUES LESS THAN (TO_DAYS('2023-06-03 00:00:00')),PARTITION p20230603 VALUES LESS THAN (TO_DAYS('2023-06-04 00:00:00')),
PARTITION p20230604 VALUES LESS THAN (TO_DAYS('2023-06-05 00:00:00')),PARTITION p20230605 VALUES LESS THAN (TO_DAYS('2023-06-06 00:00:00')),
PARTITION p20230606 VALUES LESS THAN (TO_DAYS('2023-06-07 00:00:00')),PARTITION p20230607 VALUES LESS THAN (TO_DAYS('2023-06-08 00:00:00')),
PARTITION p20230608 VALUES LESS THAN (TO_DAYS('2023-06-09 00:00:00')));
****************************************************************************************
CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20230506 VALUES LESS THAN (1683388800) ENGINE = InnoDB,
PARTITION p20230507 VALUES LESS THAN (1683475200) ENGINE = InnoDB,
PARTITION p20230508 VALUES LESS THAN (1683561600) ENGINE = InnoDB,
PARTITION p20230509 VALUES LESS THAN (1683648000) ENGINE = InnoDB,
PARTITION p20230510 VALUES LESS THAN (1683734400) ENGINE = InnoDB,
PARTITION p20230511 VALUES LESS THAN (1683820800) ENGINE = InnoDB,
PARTITION p20230512 VALUES LESS THAN (1683907200) ENGINE = InnoDB,
PARTITION p20230513 VALUES LESS THAN (1683993600) ENGINE = InnoDB,
PARTITION p20230514 VALUES LESS THAN (1684080000) ENGINE = InnoDB,
PARTITION p20230515 VALUES LESS THAN (1684166400) ENGINE = InnoDB,
PARTITION p20230516 VALUES LESS THAN (1684252800) ENGINE = InnoDB,
PARTITION p20230517 VALUES LESS THAN (1684339200) ENGINE = InnoDB,
PARTITION p20230518 VALUES LESS THAN (1684425600) ENGINE = InnoDB,
PARTITION p20230519 VALUES LESS THAN (1684512000) ENGINE = InnoDB,
PARTITION p20230520 VALUES LESS THAN (1684598400) ENGINE = InnoDB,
PARTITION p20230521 VALUES LESS THAN (1684684800) ENGINE = InnoDB,
PARTITION p20230522 VALUES LESS THAN (1684771200) ENGINE = InnoDB,
PARTITION p20230523 VALUES LESS THAN (1684857600) ENGINE = InnoDB,
PARTITION p20230524 VALUES LESS THAN (1684944000) ENGINE = InnoDB,
PARTITION p20230525 VALUES LESS THAN (1685030400) ENGINE = InnoDB,
PARTITION p20230526 VALUES LESS THAN (1685116800) ENGINE = InnoDB,
PARTITION p20230527 VALUES LESS THAN (1685203200) ENGINE = InnoDB,
PARTITION p20230528 VALUES LESS THAN (1685289600) ENGINE = InnoDB,
PARTITION p20230529 VALUES LESS THAN (1685376000) ENGINE = InnoDB,
PARTITION p20230530 VALUES LESS THAN (1685462400) ENGINE = InnoDB,
PARTITION p20230531 VALUES LESS THAN (1685548800) ENGINE = InnoDB,
PARTITION p20230601 VALUES LESS THAN (1685635200) ENGINE = InnoDB,
PARTITION p20230602 VALUES LESS THAN (1685721600) ENGINE = InnoDB,
PARTITION p20230603 VALUES LESS THAN (1685808000) ENGINE = InnoDB,
PARTITION p20230604 VALUES LESS THAN (1685894400) ENGINE = InnoDB,
PARTITION p20230605 VALUES LESS THAN (1685980800) ENGINE = InnoDB,
PARTITION p20230606 VALUES LESS THAN (1686067200) ENGINE = InnoDB,
PARTITION p20230607 VALUES LESS THAN (1686153600) ENGINE = InnoDB,
PARTITION p20230608 VALUES LESS THAN (1686240000) ENGINE = InnoDB,
PARTITION p20230609 VALUES LESS THAN (1686326400) ENGINE = InnoDB,
PARTITION p20230610 VALUES LESS THAN (1686412800) ENGINE = InnoDB,
PARTITION p20230611 VALUES LESS THAN (1686499200) ENGINE = InnoDB,
PARTITION p20230612 VALUES LESS THAN (1686585600) ENGINE = InnoDB,
PARTITION p20230613 VALUES LESS THAN (1686672000) ENGINE = InnoDB,
PARTITION p20230614 VALUES LESS THAN (1686758400) ENGINE = InnoDB,
PARTITION p20230615 VALUES LESS THAN (1686844800) ENGINE = InnoDB,
PARTITION p20230616 VALUES LESS THAN (1686931200) ENGINE = InnoDB,
PARTITION p20230617 VALUES LESS THAN (1687017600) ENGINE = InnoDB,
PARTITION p20230618 VALUES LESS THAN (1687104000) ENGINE = InnoDB,
PARTITION p20230619 VALUES LESS THAN (1687190400) ENGINE = InnoDB,
PARTITION p20230620 VALUES LESS THAN (1687276800) ENGINE = InnoDB) */
方便获取更多学习、工作、生活信息请关注本站微信公众号
上一篇:mysql中like % %模糊查询的实现
下一篇:MySQL视图全面详解
湘公网安备 43102202000103号