MySQL分区表实现按月份归类

建表

一般的表（innodb）创建后只有一个 idb 文件:

create table normal_table(id int primary key, no int)

查看数据库文件：

normal_table.ibd

创建按月份分区的分区表，注意！除了常规主键外，月份字段（用来分区的字段）也必须是主键：

create table partition_table(id int auto_increment, create_date date, name varchar(10), 
primary key(id, create_date)) engine=innodb default charset=utf8 
partition by range(month(create_date))(
partition quarter1 values less than(4),
partition quarter2 values less than(7),
partition quarter3 values less than(10),
partition quarter4 values less than(13)
);

查看数据库文件：

partition_table#p#quarter1.ibd  
partition_table#p#quarter2.ibd  
partition_table#p#quarter3.ibd  
partition_table#p#quarter4.ibd

插入

insert into partition_table(create_date, name) values("2021-01-25", "tom1");
insert into partition_table(create_date, name) values("2021-02-25", "tom2");
insert into partition_table(create_date, name) values("2021-03-25", "tom3");
insert into partition_table(create_date, name) values("2021-04-25", "tom4");
insert into partition_table(create_date, name) values("2021-05-25", "tom5");
insert into partition_table(create_date, name) values("2021-06-25", "tom6");
insert into partition_table(create_date, name) values("2021-07-25", "tom7");
insert into partition_table(create_date, name) values("2021-08-25", "tom8");
insert into partition_table(create_date, name) values("2021-09-25", "tom9");
insert into partition_table(create_date, name) values("2021-10-25", "tom10");
insert into partition_table(create_date, name) values("2021-11-25", "tom11");
insert into partition_table(create_date, name) values("2021-12-25", "tom12");

查询

select count(*) from partition_table;
> 12

 
查询第二个分区（第二季度）的数据：
select * from partition_table partition(quarter2);

4 2021-04-25 tom4
5 2021-05-25 tom5
6 2021-06-25 tom6

删除

当删除表时，该表的所有分区文件都会被删除

补充：mysql自动按月表分区

核心的两个存储过程：

auto_create_partition为创建表分区，调用后为该表创建到下月结束的表分区。
auto_del_partition为删除表分区，方便历史数据空间回收。

delimiter $$
drop procedure if exists auto_create_partition$$
create procedure `auto_create_partition`(in `table_name` varchar(64))
begin
   set @next_month:=concat(date_format(date_add(now(),interval 2 month),'%y%m'),'01');
   set @sql = concat( 'alter table `', table_name, '`',
     ' add partition (partition p', @next_month, " values less than (to_days(",
       @next_month ,")) );" );
   prepare stmt from @sql;
   execute stmt;
   deallocate prepare stmt;
end$$

drop procedure if exists auto_del_partition$$
create procedure `auto_del_partition`(in `table_name` varchar(64),in `reserved_month` int)
begin
 declare v_finished integer default 0;
 declare v_part_name varchar(100) default "";
 declare part_cursor cursor for 
  select partition_name from information_schema.partitions where table_schema = schema()
   and table_name=@table_name and partition_description < to_days(concat(date_format(date_sub(now(),interval reserved_month month),'%y%m'),'01'));
 declare continue handler for 
  not found set v_finished = true;
 open part_cursor;
read_loop: loop
 fetch part_cursor into v_part_name;
 if v_finished = 1 then
  leave read_loop;
 end if;
 set @sql = concat( 'alter table `', table_name, '` drop partition ', v_part_name, ";" );
 prepare stmt from @sql;
 execute stmt;
 deallocate prepare stmt;
 end loop;
 close part_cursor;
end$$

delimiter ;

下面是示例

-- 假设有个表叫records,设置分区条件为按end_time按月分区
drop table if exists `records`;
create table `records` (
  `id` int(11) not null auto_increment,
  `start_time` datetime not null,
  `end_time` datetime not null,
  `memo` varchar(128) character set utf8mb4 not null,
  primary key (`id`,`end_time`)
) 
partition by range (to_days(end_time))(
 partition p20200801 values less than ( to_days('20200801'))
);

drop event if exists `records_auto_partition`;

-- 创建一个event,每月执行一次，同时最多保存6个月的数据
delimiter $$
create event `records_auto_partition`
on schedule every 1 month on completion preserve
enable
do
begin
call auto_create_partition('records');
call auto_del_partition('records',6);
end$$
delimiter ;

几点注意事项：

对于mysql 5.1以上版本来说，表分区的索引字段必须是主键
存储过程中，declare 必须紧跟着begin，否则会报看不懂的错误
游标的declare需要在定义声明之后，否则会报错
如果是自己安装的mysql，有可能event功能是未开启的，在创建event时会提示错误；修改my.cnf,在 [mysqld] 下添加event_scheduler=1后重启即可。

到此这篇关于mysql分区表实现按月份归类的文章就介绍到这了,更多相关mysql按月表分区内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL分区表实现按月份归类

目录

建表

查看数据库文件：

插入

查询

删除

补充：mysql自动按月表分区

相关推荐