创建按月份分区的表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
create table if not exists table_name ( id bigint auto_increment comment '主键id',
create_by varchar(64) not null comment '创建者', create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间', update_by varchar(64) null comment '更新者', update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间', primary key (id, create_time) ) comment '测试表名' partition by range (TO_DAYS(create_time)) ( partition P202401 values less than (TO_DAYS('2024-02-01')) , partition P202402 values less than (TO_DAYS('2024-03-01')) , partition P202403 values less than (TO_DAYS('2024-04-01')) , partition P202404 values less than (TO_DAYS('2024-05-01')) ); |
开启数据库事件
临时配置（服务重启会失效）
|
1 2 3 4 |
-- 开启事件 SET GLOBAL event_scheduler = ON; -- 查看事件是否开启 SHOW VARIABLES LIKE '%event_sche%'; |
修改my.cnf文件（推荐）
- 进入MySQL所在的服务器
|
1 |
sudo vi /etc/my.cnf |
- 找到 [mysqld]，添加如下内容
|
1 |
event_scheduler = ON |
- 重启 MySQL 服务以使更改生效
|
1 |
sudo systemctl restart mysqld |
定时事件自动建分区
- 例：提前创建下个月的分区，如2024-04-01创建5月份的分区 P202405，2024-05-01创建6月份的分区。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
create event event_create_partition on schedule every '1' MONTH starts '2024-04-01 00:00:00' on completion preserve enable do BEGIN
-- 当前时间 2024-04-01 00:00:00 -- _DATE = 2024-05-01 DECLARE _DATE VARCHAR(10) DEFAULT ADDDATE(SUBDATE(CURDATE(), DAY(CURDATE()) - 1), INTERVAL 1 MONTH); -- P202405 SET @PARTITION_NAME = CONCAT('P', DATE_FORMAT(_DATE, '%Y%m')); -- 2024-06-01 SET @SHEAR_DATE = CONCAT('', ADDDATE(LAST_DAY(_DATE), 1));
SET @SQL = CONCAT('ALTER TABLE table_name ADD PARTITION (PARTITION ', @PARTITION_NAME, ' VALUES LESS THAN (TO_DAYS("', @SHEAR_DATE, '")))'); PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT;
END; |
注意： 数据库event_scheduler = ON 要确认有开启，否则 event_create_partition 事件不会执行。
- 查看所有事件
|
1 |
SHOW EVENTS; |
查询表分区信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_NAME, SUBPARTITION_METHOD, SUBPARTITION_EXPRESSION FROM information_schema.partitions WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME IN ('table_name') ORDER BY PARTITION_NAME DESC; |
