mysql表分区例子

最新推荐文章于 2021-12-22 17:02:58 发布

a107494639

最新推荐文章于 2021-12-22 17:02:58 发布

阅读量564

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/a107494639/article/details/48706749

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.表结构：

CREATE TABLE `students` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` int(11) DEFAULT NULL,
  `stu_sex` int(11) DEFAULT NULL,
  `cla_id` int(11) DEFAULT NULL,
  `stu_phone` char(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`stu_id`,`create_time`)
)PARTITION BY RANGE (TO_DAYS(create_time))
(PARTITION p20150819 VALUES LESS THAN (736195))

2.存储过程，每执行一次，会创建一周内的分区（每天一个分区）

CREATE PROCEDURE `createPart`()
BEGIN
  DECLARE retrows int;
	DECLARE pname varchar(64);
	SET @a = 1;
	WHILE @a <= 7 DO
		set pname=REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL @a DAY))),'-','');
		select count(1) into retrows from `information_schema`.`PARTITIONS` where partition_name=pname and table_name='students';  
    if retrows=0 then
			SET @SQL=CONCAT('ALTER TABLE students ADD PARTITION(','PARTITION ',REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL @a DAY))),'-',''),' VALUES LESS THAN (',to_days(date_add(now(),interval @a+1 day)),'))');
			PREPARE STMT FROM @SQL;
			EXECUTE STMT;
			DEALLOCATE PREPARE STMT;
		end if;
		SET @a = @a+1;
  END WHILE;
END

3.存储过程，每执行一次，会删除前2天~前7天的分区

CREATE PROCEDURE `dropPart`()
BEGIN
	declare pname varchar(32);
  declare retrows int;
	SET @a = -2;
	WHILE @a >= -7 DO
		set pname=REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL @a DAY))),'-','');
		select count(1) into retrows from `information_schema`.`PARTITIONS` where partition_name=pname and table_name='students';  
		if retrows >0 then
				SET @SQL=CONCAT('ALTER TABLE students drop PARTITION ',REPLACE(concat('p',date(DATE_ADD(NOW(),INTERVAL @a DAY))),'-',''));
				PREPARE STMT FROM @SQL;
				EXECUTE STMT;
				DEALLOCATE PREPARE STMT;
		end if;
		SET @a = @a-1;
  END WHILE;
end