How to partition Mysql table by year and month of date field

1. Set the partition directly when creating the key table

CREATE TABLE your_table_name (
    id INT NOT NULL AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p2020_01 VALUES LESS THAN ('2020-02-01'),
    PARTITION p2020_02 VALUES LESS THAN ('2020-03-01'),
    PARTITION p2020_03 VALUES LESS THAN ('2020-04-01'),
    PARTITION p2021_01 VALUES LESS THAN ('2021-02-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

2. Already have table partitions

1. Preconditions for partitioning

Make sure the primary or unique key contains partition keys, and can be modified if the table has been created.

ALTER TABLE your_table_name DROP PRIMARY KEY;

ALTER TABLE your_table_name 
ADD PRIMARY KEY (id, xxdate);

2. Partition operation

Query the year and month of the date field that needs to be partitioned

SELECT DISTINCT DATE_FORMAT(xxdate, '%Y-%m') AS YM
FROM your_table_name 
ORDER BY YM;

Create a partition based on the year and month data queryed by the above sql statement

# I only have two months of data in 202307 and 202308ALTER TABLE your_table_name 
PARTITION BY RANGE COLUMNS(xxdate) (
    PARTITION p2023_07 VALUES LESS THAN ('2023-08-01'),
    PARTITION p2023_08 VALUES LESS THAN ('2023-09-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

3. Verification

EXPLAIN  SELECT * from your_table_name where xxdate BETWEEN '2023-07-01' and '2023-07-31';

partitions hits a target partition and the partition is successful, for example: the value of the partitions column is p2023_07

4. Pay attention

If there is a new month partition that needs to be added, it needs to be modified manually, otherwise it will be classified as p_max partition that affects query efficiency

ALTER TABLE your_table_name 
PARTITION BY RANGE COLUMNS(xxdate) (
    PARTITION p2023_07 VALUES LESS THAN ('2023-08-01'),
    PARTITION p2023_08 VALUES LESS THAN ('2023-09-01'),
    # Add here    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.