mysql建立存储过程实战，历史表数据迁移，历史表自动分区，

最新推荐文章于 2023-01-05 10:29:05 发布

墨眉亦无锋

最新推荐文章于 2023-01-05 10:29:05 发布

阅读量1.6k

点赞数 1

分类专栏： sql oralce java基础

本文链接：https://blog.csdn.net/LIUHUA121/article/details/81129412

版权

sql 同时被 3 个专栏收录

9 篇文章 0 订阅

订阅专栏

java基础

8 篇文章 0 订阅

订阅专栏

oralce

4 篇文章 0 订阅

订阅专栏

首先是写一个存储过程，还是比较简单的，主要是网上有很多现成的，大部分有点错误。下边的是实际应用过程中实战有效的。没有错误的。

首先是存储过程展示；

DELIMITER $$

DROP PROCEDURE IF EXISTS `create_Partition`$$

CREATE DEFINER=`root`@`%` PROCEDURE `create_Partition`(IN databaseName VARCHAR(50),IN tableName VARCHAR(50))
L_END:BEGIN       
     DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;  
     DECLARE P_NAME VARCHAR(255) DEFAULT 0;         
     DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;     
     DECLARE i INT DEFAULT 1;  
     DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0;   
        DECLARE MONTH_DAYS VARCHAR(5);
        DECLARE YEAR_NUM VARCHAR(10);
        DECLARE MONTH_NUM VARCHAR(5);
     SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;  

     IF ISEXIST_PARTITION <=> "" THEN  
       SELECT "Partition table not is exist" AS "*****ERROR*****";  
       LEAVE  L_END;  
     END IF;  

     SELECT partition_description INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;  


     IF MAX_PARTITION_DESCRIPTION <=> "" THEN  
       SELECT "Partition table is error" AS "*****ERROR*****";  
       LEAVE  L_END;  
     END IF;  


        select day(last_day(date_add(NOW(), interval 1 MONTH)))into MONTH_DAYS;

    SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');  
    WHILE i <= MONTH_DAYS DO  
                        select date_format(date_add(last_day(NOW()),interval i day),'%Y-%m-%d') into P_DESCRIPTION;
          ## SET P_DESCRIPTION = adddate(MAX_PARTITION_DESCRIPTION, INTERVAL i day);  
           SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');  
           SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN (','\'',P_DESCRIPTION,'\'','))');  
           SELECT @S;  
           PREPARE stmt2 FROM @S;  
           EXECUTE stmt2;  
           DEALLOCATE PREPARE stmt2;  
           SET i = i + 1 ;  
    END WHILE;            
END L_END$$

DELIMITER ;

别看这么长，实际用到的只是while部分。其余部分就是判断有效性和查询数据，为自动分区做准备数据用的。

写好了存储过程可以自己建一张表测试一下。

CREATE TABLE `realtable` (
  `id` int(10) unsigned NOT NULL COMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT '产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `uid` int(10) unsigned NOT NULL COMMENT '客户ID',
  `atime` varchar(255) NOT NULL DEFAULT '',
  `utime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(atime)
(PARTITION p30 VALUES LESS THAN ('2018-07-09') ENGINE = InnoDB,
 PARTITION p31 VALUES LESS THAN ('2018-07-10') ENGINE = InnoDB,
 PARTITION p32 VALUES LESS THAN ('2018-07-11') ENGINE = InnoDB,
 PARTITION p33 VALUES LESS THAN ('2018-07-12') ENGINE = InnoDB,
 PARTITION p20180801 VALUES LESS THAN ('2018-08-01') ENGINE = InnoDB,
 PARTITION p20180802 VALUES LESS THAN ('2018-08-02') ENGINE = InnoDB,
 PARTITION p20180803 VALUES LESS THAN ('2018-08-03') ENGINE = InnoDB,
 PARTITION p20180804 VALUES LESS THAN ('2018-08-04') ENGINE = InnoDB,
 PARTITION p20180805 VALUES LESS THAN ('2018-08-05') ENGINE = InnoDB,
 PARTITION p20180806 VALUES LESS THAN ('2018-08-06') ENGINE = InnoDB,
 PARTITION p20180807 VALUES LESS THAN ('2018-08-07') ENGINE = InnoDB,
 PARTITION p20180808 VALUES LESS THAN ('2018-08-08') ENGINE = InnoDB,
 PARTITION p20180809 VALUES LESS THAN ('2018-08-09') ENGINE = InnoDB,
 PARTITION p20180810 VALUES LESS THAN ('2018-08-10') ENGINE = InnoDB,
 PARTITION p20180811 VALUES LESS THAN ('2018-08-11') ENGINE = InnoDB,
 PARTITION p20180812 VALUES LESS THAN ('2018-08-12') ENGINE = InnoDB,
 PARTITION p20180813 VALUES LESS THAN ('2018-08-13') ENGINE = InnoDB,
 PARTITION p20180814 VALUES LESS THAN ('2018-08-14') ENGINE = InnoDB,
 PARTITION p20180815 VALUES LESS THAN ('2018-08-15') ENGINE = InnoDB,
 PARTITION p20180816 VALUES LESS THAN ('2018-08-16') ENGINE = InnoDB,
 PARTITION p20180817 VALUES LESS THAN ('2018-08-17') ENGINE = InnoDB,
 PARTITION p20180818 VALUES LESS THAN ('2018-08-18') ENGINE = InnoDB,
 PARTITION p20180819 VALUES LESS THAN ('2018-08-19') ENGINE = InnoDB,
 PARTITION p20180820 VALUES LESS THAN ('2018-08-20') ENGINE = InnoDB,
 PARTITION p20180821 VALUES LESS THAN ('2018-08-21') ENGINE = InnoDB,
 PARTITION p20180822 VALUES LESS THAN ('2018-08-22') ENGINE = InnoDB,
 PARTITION p20180823 VALUES LESS THAN ('2018-08-23') ENGINE = InnoDB,
 PARTITION p20180824 VALUES LESS THAN ('2018-08-24') ENGINE = InnoDB,
 PARTITION p20180825 VALUES LESS THAN ('2018-08-25') ENGINE = InnoDB,
 PARTITION p20180826 VALUES LESS THAN ('2018-08-26') ENGINE = InnoDB,
 PARTITION p20180827 VALUES LESS THAN ('2018-08-27') ENGINE = InnoDB,
 PARTITION p20180828 VALUES LESS THAN ('2018-08-28') ENGINE = InnoDB,
 PARTITION p20180829 VALUES LESS THAN ('2018-08-29') ENGINE = InnoDB,
 PARTITION p20180830 VALUES LESS THAN ('2018-08-30') ENGINE = InnoDB,
 PARTITION p20180831 VALUES LESS THAN ('2018-08-31') ENGINE = InnoDB) */;

其中7月份是手动建的，其余的是调用改存储过程生成的。

调用SQL语句：

 CALL create_Partition('liuhua','realtable');

可能在调用过程中会报错root不存在，此时需要授权一下。SQL语句如下。

grant all privileges on *.* to root@"%" identified by ".";

这样就可以实现按照天自动分区。

存储过程写完了，分区效果实现了，那么就可以写一个事件去执行了。

DELIMITER $$
CREATE DEFINER=`root`@`%` EVENT `auto_set_partitions`
 ON SCHEDULE EVERY 1 MONTH STARTS '2018-08-11 17:18:00' 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN  
        CALL create_Partition('liuhua','realtable');

    END $$
DELIMITER ;

注意：1 该存储过程就几乎不需要修改就可以直接使用；

2 其中的时间字段是varcha类型的，其他的时间类型会报错，类型不正确。

3 可以修改的地方，存储过程的吗名字，和时间字段的格式。

具体的内容意思就不做详细介绍了。