MySql table partitioning (based on time datetime)

For timestamp type partitions, please move = > MySql table partitions (according to time timestamp)

Environment:

MySql8.0.18 (unverified for 5.6and5.7)

The field type of the partition condition is datetime

The complete sql operation table partition statement is as follows:

-1. Delete the table drop table tasking testament-= =-- 2. Create a table and partition it. The field to be partitioned must be a primary key, or there is no primary key in the table when the partition is created. Method 1: the table and the partition create create table t_test (id int, dates timestamp) partition by range (unix_timestamp (dates)) (--partition p1 values less than (unix_timestamp ('2018-01-01')) less than 2018-01-01) Partition p2 values less than (unix_timestamp ('2018-02-01')), partition p3 values less than (unix_timestamp ('2018-03-01')),-- partition p4 values less than maxvalue greater than 2018-03-01) -- = =-- 2.2 method 2: create tables and partitions separately-- 2.2.1 create tables create table `ttest` (`id` int (11) not null, `dates` datetime (0) not null on update current_timestamp (0), primary key (`id`, `dates`));-- 3. Modify partition information alter table t_test partition by range (to_days (dates)) (--partition p1 values less than (to_days ('2020-01-01') less than 2020-01-01), partition p2 values less than (to_days ('2020-02-01')), partition p3 values less than (to_days ('2020-03-01')), partition p4 values less than (to_days ('2020-04-01')) -- partition p5 values less than maxvalue greater than 2020-04-01) -- = =-- 4. Delete and add a new partition (Note: if the last partition was partition pnow values less than maxvalue;, then delete the partition first, then execute the new partition statement, and then add back to the partition)-- 4.1Delete a partition (Note: when you delete a partition, all data in that partition will also be deleted;) alter table t_test drop partition p5 Add a new partition alter table t_test add partition (partition p6 values less than (to_days ('2020-05-01')); add a new partition (partition p7 values less than maxvalue) alter table t_test add partition (partition p7 partition);-- = =-- 5. Query the number of partitions in this table-- 5. 1 query the corresponding number of select partition_name part, partition_expression expr, partition_description descr, from_days (partition_description) expirydate, table_rows from information_ schema.`partitions`where table_name='t_test';-- 6. Create test data-less than 2020-01-01 2 insert into `t _ test` values ('1th,' 2018-01-02 15insert into 00lv 00'); insert into `ttest` values ('2mm,' 2019-12-02 15lv 00lv 00');-- 2020-01-01 to 2020-02-01 1 insert into `t_ test` values ('3mm,' 2020-01-02 1600purl') -- 2020-02-01 to 2020-03-01 2 insert into `ttest` values ('44th,' 2020-02-03 1500insert into 00'); insert into `ttest` values ('5mm,' 2020-02-03 1500values');-2020-03-01 to 2020-04-01 1 insert into `ttest` values ('6mm,' 2020-03-03 1500values') -- 2020-04-01 to 2020-05-01 1 insert into `ttest` values ('7mm,' 2020-04-03 15insert into 1500insert into 00');-- > 2020-05-01 4 insert into `ttest` values ('8mm,' 2020-05-03 1500values'); insert into `ttest` values ('8mm,' 2020-06-03 15insert into 0000'); insert into `ttest` ('8mm,' 2020-06-06) Insert into `t _ test` values ('8mm,' 2021-01-01 15purl 0000');-- 6. Query data select * from t_test

We can view the partition information using the sql in step 5

You can see that there are two messages before 2020-01-01, one before 2020-01-01 and 2020-02-01, and so on, there are four messages after 2020-05-01.

You can also use the Navicat for MySQL tool to operate partitions: Navicat for MySQL for table partitioning (illustration)

For timestamp type partitions, please move = > MySql table partitions (according to time timestamp)