温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL分区表如何实现按月份归类

发布时间:2021-10-29 13:04:19 来源:亿速云 阅读:477 作者:小新 栏目:开发技术

小编给大家分享一下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分区表如何实现按月份归类”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI