Shulou(Shulou.com)06/02 Report--

Editor to share with you how to achieve monthly classification of MySQL partition tables, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Build a table

A normal innodb has only one idb file after it is created:

Create table normal_table (id int primary key, no int) view database files:

Normal_table.ibd

Create a partition table partitioned by month, pay attention! In addition to the regular primary key, the month field (the field used for partitioning) must also be the primary key:

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))

View the database file:

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

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"); query

Select count (*) from partition_table; > 12 query data for the second partition (second quarter): select * from partition_table PARTITION (quarter2); 4 2021-04-25 tom45 2021-05-25 tom56 2021-06-25 tom6 deletion

When you delete a table, all partition files for the table are deleted

Supplement: Mysql automatically partitions by monthly schedule

The two core stored procedures:

Auto_create_partition is to create a table partition, which is called to create a table partition that ends next month.

Auto_del_partition deletes table partitions to facilitate historical data space recycling.

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 packs, @ 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 (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

Here is an example

-- suppose there is a table called records, and the partition condition is set to partition DROP TABLE IF EXISTS `records` by end_time monthly; 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` -- create an Event, execute once a month, and keep data 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 for up to 6 months

A few points to note:

For Mysql 5.1 or later, the index field of a table partition must be a primary key

In a stored procedure, DECLARE must follow BEGIN, or it will report an error that you can't understand.

The DECLARE of the cursor needs to be defined and declared, otherwise an error will be reported.

If you install Mysql yourself, it is possible that the Event feature is not enabled, and an error will be prompted when creating the Event. Modify the my.cnf, add event_scheduler=1 under "mysqld", and then restart.

The above is all the contents of the article "how to classify MySQL partition tables by month". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.