原创

mysql 自动创建分区以及处理关于MAXVALUE问题

这篇博客主要是记录下对于mysql数据库如何分区以及分区时所带来的问题和解决方案
首先说一下大体思路:
1.创建数据库表,以及初始化的分区以及最大值分区
2.创建存储过程,完成分区
3.创建事件,完成定时任务调用存储过程来进行分区
4.列出各种操作的sql语句

1.创建数据库表,以及初始化的分区以及最大值分区。
由于分区时的分区字段必须包含在主键内,我需要按照时间来进行分区,所以我将时间和id设成了复合主键,来符合mysql对分区时候的要求
此处创建完的分区是包含了最大值的,也就是PARTITION p0 VALUES LESS THAN MAXVALUE这行代码。继续往下看这块会有坑以及解决办法。
代码如下:
创建t2表,

CREATE TABLE t2 (
     id VARCHAR(255) NOT NULL ,
     createtime DATETIME NOT NULL,
      PRIMARY KEY (id,createtime)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p201901 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p201902 VALUES LESS THAN (TO_DAYS('2019-02-01')),
PARTITION p201903 VALUES LESS THAN (TO_DAYS('2019-03-01')),
PARTITION p201904 VALUES LESS THAN (TO_DAYS('2019-04-01')),
PARTITION p201905 VALUES LESS THAN (TO_DAYS('2019-05-01')),
PARTITION p0 VALUES LESS THAN  MAXVALUE
);
  1. 创建存储过程,完成分区。
    在创建存储过程的时候,有几点需要说明的,
    在创建存储过程中,因为前面也提到的PARTITION p0 VALUES LESS THAN MAXVALUE最大值分区的问题,所以当再创建分区以及添加分区的时候,会报错,告诉我们MAXVALUE最大值分区应该是在最后的,所以此处给出的解决方案是,创建临时表,将最大值分区P0里的数据拿出来插入到临时表内,删除掉最大值分区,然后创建新添加新分区,再创建最大值分区添加,将临时表内的数据再重新插入到表内。这样还带来了一个好处是,我这块要最大值分区p0的原因是数据是由自动由工具分析出来的,既然是自动的,那么时间就有可能是错的,比如,2022年的某月某日,这个时候如果来这么一次操作,重新插入到表内的时候,若新建出来的分区时间段包含里之前的,那么这条数据则会被添加到新的分区,而不会还在p0分区了。

creat_partition_by_momth(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
这里是设置了两个参数,第一个为数据库名称,第二个为表名称,
我用到的t1表替代了临时表,当然最好是临时表,创建方式也很简单,创建方式如下,不过我这块没有用到。

`CREATE TEMPORARY TABLE tmp_table (

  id VARCHAR(255) NOT NULL ,
createtime DATETIME NOT NULL,

  ))`

如下是存储过程的代码:

DELIMITER $$

USE `user`$$

DROP PROCEDURE IF EXISTS `creat_partition_by_momth`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `creat_partition_by_momth`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;
    #用来判断是否存在最大值
    DECLARE PO_CNT INT UNSIGNED;
     #目前日期,为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #最大值区域名字 p0
     DECLARE PARTITIONMAXNAME VARCHAR(2);
    #分区的名称,格式为p201906
    DECLARE PARTITIONNAME VARCHAR(7);
     #当前分区名称的分区值上限,即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY VARCHAR(9);
   # SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    SET TARGET_DATE = DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY); 
    SET PARTITIONMAXNAME='p0';
    #SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
   # SET PARTITIONNAME = date_format(CURDATE(), 'p%Y%m');
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m' );
    #SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y%m%d' );
    #select PARTITIONNAME;
    #接下来要做的是将最大值表数据拿出来,删除最大值区,重新创建分区和再加上最大值分区,再将数据导入进去
    #-------------------------------------statrt--------------------------------------------------------#
    #SELECT * FROM t2 PARTITION (p201902);
     SELECT COUNT(*) INTO PO_CNT FROM information_schema.partitions
     WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONMAXNAME;
    IF PO_CNT != 0 THEN
    DELETE FROM t1;
     #select PO_CNT;
    # SELECT * FROM t2 PARTITION (p0);
     SET @str3=CONCAT('INSERT INTO t1 SELECT * FROM t2 PARTITION(',PARTITIONMAXNAME,')');
     PREPARE stmt FROM @str3;
     EXECUTE stmt;


     SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                            ' DROP PARTITION ', PARTITIONMAXNAME);
    # ALTER TABLE t2  DROP PARTITION PARTITIONMAXNAME;
      PREPARE STMT FROM @SQL;
          EXECUTE STMT;
          DEALLOCATE PREPARE STMT;
    END IF;
    #----------------------------------------end---------------------------------------------------------#
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
     WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

     IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (",
            TO_DAYS(PARTITION_ADD_DAY) ,") ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
     #---------------------------------------------重新增加最大值分区-重新添加数据--------------------#

       SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONMAXNAME, " VALUES LESS THAN MAXVALUE ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;

        SET @str3=CONCAT('INSERT INTO t2 SELECT * FROM t1');
     PREPARE stmt FROM @str3;
     EXECUTE stmt;
     DELETE FROM t1;
      #-------------------------------------------------------------------------------------------------------#
END$$

DELIMITER ;

3.创建时间,定时任务调用存过程
我此处因为测试设置的是5秒调用一次
t_event_history表为我调试的表,此处注释掉了。

DELIMITER $$

ALTER DEFINER=`root`@`localhost` EVENT `e_test` ON SCHEDULE EVERY 5 SECOND STARTS '2019-11-11 01:00:00' ON COMPLETION PRESERVE DISABLE DO BEGIN  
    DECLARE r_code CHAR(5) DEFAULT '00000';  
    DECLARE r_msg TEXT;  
    DECLARE v_error INTEGER;  
    DECLARE v_starttime DATETIME DEFAULT NOW();  
    DECLARE v_randno INTEGER DEFAULT FLOOR(RAND()*100001);  

    #INSERT INTO t_event_history (dbname,eventname,starttime,randno)   
    #修改下面的作业名(该作业的名称)  
    #VALUES(DATABASE(),'e_test', v_starttime,v_randno);    

    BEGIN  
        #异常处理段  
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    
        BEGIN  

            SET  v_error = 1;  
            GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE , r_msg = MESSAGE_TEXT;  
        END;  

        #此处为实际调用的用户程序过程  

        CALL creat_partition_by_momth('user','t2');
    END;  

   # UPDATE t_event_history SET endtime=NOW(),issuccess=ISNULL(v_error),duration=TIMESTAMPDIFF(SECOND,starttime,NOW()), #errormessage=CONCAT('error=',r_code,', message=',r_msg),randno=NULL WHERE starttime=v_starttime AND randno=v_randno;  

END$$

DELIMITER ;

4.操作的sql语句
查询t2表下有多少个分区情况

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='t2';

查询t2表下p201911分区内的数据
SELECT * FROM t2 PARTITION (p201911)
删除t2表下p201911分区,包括数据
ALTER TABLE t2 DROP PARTITION p201911;
events事件开始 e_test为我此处的事件名称
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;
events事件暂停
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;

新增t2表内的分区p0
ALTER TABLE t2 ADD PARTITION(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = INNODB);

若有不懂的可以评论留言,希望可以帮助到大家。

正文到此结束
本文目录