MySQL 自动根据年份动态创建范围分区_mysql 按年分区

前言

要在MySQL中按年对日期进行分区，可以使用自动递增存储过程的方式实现动态分区，它并没有像Oracle里面的**INTERVAL (numtoyminterval(1, ‘year’))**方法。

创建动态分区

1.创建一个包含所有分区的表，并使用InnoDB存储引擎。例如，创建一个名为mytable的表。

CREATE TABLE mytable (
  id INT,
  create_time DATE
) ENGINE=InnoDB;
1
2
3
4

2.接下来，使用下面的语句来创建分区：

ALTER TABLE mytable
PARTITION BY RANGE(YEAR(create_time)) (
  PARTITION p1 VALUES LESS THAN (2022),
  PARTITION p2 VALUES LESS THAN (2023),
  PARTITION p2 VALUES LESS THAN (2024)
);
1
2
3
4
5
6

注意：此时可能会报 a primary key must include all columns in the table is partitioning function，意思是 一个唯一键必须包含表分区函数所有的列，不知道为啥MySQL要这么设计，此时你需要将一个唯一值(比如唯一id)加上create_time做联合主键。
详情链接: https://blog.csdn.net/run_boy_2022/article/details/131735670
https://blog.csdn.net/qq_33326449/article/details/104292311

3.为了实现动态分区，你可以为每个新的年份自动生成一个分区。可以使用存储过程来实现这一点。下面是一个例子：

DELIMITER //

CREATE PROCEDURE create_new_partition()
BEGIN
  DECLARE current_year INT;
  SELECT YEAR(CURDATE()) INTO current_year;
  SET @partition_name = CONCAT('p', current_year);
  SET @sql = CONCAT(
    'ALTER TABLE mytable ADD PARTITION (PARTITION ', @partition_name, ' VALUES LESS THAN (', current_year + 1, '))'
  );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

该存储过程将当前年份作为变量 current_year，然后创建一个新的分区，命名为 p当前年份，并将其添加到表中。如果数据库日期有偏差，可以将年份+2，在当年12月时进行定时任务。
你可以定期运行这个存储过程，例如在每年的开始或月初，以便自动为下一个新的年份添加分区。
请注意，分区表的性能可能会受到分区数量的影响。如果分区过多，可能会导致性能下降。建议根据实际需要和系统性能进行分区设置。

创建定时任务

你可以创建一个定时任务来每年初自动执行上面那个存储过程。

CREATE EVENT create_partition_event
ON SCHEDULE
  EVERY 1 YEAR
  STARTS '2024-01-01 00:00:00'
DO
  CALL create_new_partition();
1
2
3
4
5
6

这个事件被命名为 create_partition_event，它将在每年的 1 月 1 日 00:00:00 开始执行。它使用 create_new_partition() 存储过程来创建新分区。你可以根据需要自定义事件的名称、执行时间和频率。

附常用命令

-- 查询表中可用分区
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME = '表名';

-- 清除表中全部分区
ALTER TABLE 表名 REMOVE PARTITIONING;

-- 清除指定表中分区
ALTER TABLE 表名 DROP PARTITION 分区名;

-- 查询定时任务是否已开启，如果开关是ON状态，说明定时任务是开启的
SHOW VARIABLES LIKE 'event_scheduler';

-- 查询全部定时任务
select *  from mysql.event;
1
2
3
4
5
6
7
8
9
10
11
12
13
14