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

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