Mysql表如何按照日期字段的年月分区

关清奇

一、创键表时直接设置分区

1. CREATE TABLE your_table_name (
   
2.     id INT NOT NULL AUTO_INCREMENT,
   
3.     sale_date DATE NOT NULL,
   
4.     amount DECIMAL(10, 2) NOT NULL,
   
5.     PRIMARY KEY (id, sale_date)
   
6. ) PARTITION BY RANGE COLUMNS(sale_date) (
   
7.     PARTITION p2020_01 VALUES LESS THAN ('2020-02-01'),
   
8.     PARTITION p2020_02 VALUES LESS THAN ('2020-03-01'),
   
9.     PARTITION p2020_03 VALUES LESS THAN ('2020-04-01'),
   
10.     PARTITION p2021_01 VALUES LESS THAN ('2021-02-01'),
    
11.     PARTITION p_max VALUES LESS THAN (MAXVALUE)
    
12. );

复制代码

二、已有表分区


1、分区的前置条件

确保主键或唯一键包含分区键，若已经创建表可以修改

1. ALTER TABLE your_table_name DROP PRIMARY KEY;
   
2. 
   
3. ALTER TABLE your_table_name
   
4. ADD PRIMARY KEY (id, xxdate);

复制代码

2、分区操作

查询需要分区的日期字段所含有的年月

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

复制代码

根据上面sql语句查询的年月数据创建分区

1. # 我这只有202307、202308两个月数据
   
2. ALTER TABLE your_table_name
   
3. PARTITION BY RANGE COLUMNS(xxdate) (
   
4.     PARTITION p2023_07 VALUES LESS THAN ('2023-08-01'),
   
5.     PARTITION p2023_08 VALUES LESS THAN ('2023-09-01'),
   
6.     PARTITION p_max VALUES LESS THAN (MAXVALUE)
   
7. );

复制代码

三、验证

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

复制代码

partitions 命中一个目标分区则分区成功，如：partitions列的值为 p2023_07

四、注意

如果有新的月份分区需要增加，则需要手动去修改，否则归为p_max分区影响查询效率

1. ALTER TABLE your_table_name
   
2. PARTITION BY RANGE COLUMNS(xxdate) (
   
3.     PARTITION p2023_07 VALUES LESS THAN ('2023-08-01'),
   
4.     PARTITION p2023_08 VALUES LESS THAN ('2023-09-01'),
   
5.     # 在这里添加
   
6.     PARTITION p_max VALUES LESS THAN (MAXVALUE)
   
7. );

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持晓枫资讯。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！