文章目录
- 创建按月份分区的表
- 开启数据库事件
- 临时配置(服务重启会失效)
- 修改`my.cnf`文件(推荐)
- 定时事件自动建分区
- 查询表分区信息
创建按月份分区的表
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 P202403 values less than (TO_DAYS('2024-04-01')) ,partition P202404 values less than (TO_DAYS('2024-05-01'))
);
开启数据库事件
临时配置(服务重启会失效)
-- 开启事件
SET GLOBAL event_scheduler = ON;
-- 查看事件是否开启
SHOW VARIABLES LIKE '%event_sche%';
-- 查看所有事件
SHOW EVENTS;
修改my.cnf
文件(推荐)
- 进入MySQL所在的服务器
sudo vi /etc/my.cnf
- 找到 [mysqld],添加如下内容
event_scheduler = ON
- 重启 MySQL 服务以使更改生效
sudo systemctl restart mysqld
定时事件自动建分区
- 例:2024-04-01 创建5月份的分区 P202405,5月1号创建6月份的分区。
create event event_create_partition on scheduleevery '1' MONTH starts '2024-04-01 00:00:00'on completion preserveenable do BEGIN-- 当前时间 2024-04-01 00:00:00-- _DATE = 2024-05-01DECLARE _DATE VARCHAR(10) DEFAULT ADDDATE(SUBDATE(CURDATE(), DAY(CURDATE()) - 1), INTERVAL 1 MONTH);-- P202405SET @PARTITION_NAME = CONCAT('P', DATE_FORMAT(_DATE, '%Y%m'));-- 2024-06-01SET @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 事件不会执行。
查询表分区信息
SELECTTABLE_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;