MySQL自动化分区新增数字时间戳类型

Demonson

于 2023-06-05 15:18:12 发布

阅读量306

点赞数

分类专栏： MySQL MySQL 基础 MySQL 架构文章标签：自动化大数据运维

本文链接：https://blog.csdn.net/demonson/article/details/131048537

版权

MySQL 架构同时被 3 个专栏收录

95 篇文章 2 订阅

订阅专栏

MySQL 基础

67 篇文章 1 订阅

订阅专栏

MySQL

63 篇文章 3 订阅

订阅专栏

背景：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) */