MySQL按时间进行表分区的方法代码

目录

• 创建按月份分区的表
• 开启数据库事件
• 临时配置（服务重启会失效）
• 修改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_TIMESTwww.devze.comAMP 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'))
);

开启数据库事件编程

临时配置（服务重启会失效）

-- 开启事件
SET GLOBAL event_scheduler = ON;
-- 查看事件是否开启
SHOW VARIABLES LIKE '%event_sche%';

修改my.cnf文件（推荐）

• 进入mysql所在的服务器

sudo vi /etc/my.cnf

• 找到 [mysqld]，添加如下内容

event_scheduler = ON

• 重启 MySQL 服务以使更改生效

sudo systemctl restart mysqld

定时事件自动建分区

• 例：提前创建下个月的分区，如2024-04-01创建5月份的分区 P202405，2024-05-01创建6月份的分区。

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',android 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 事件不会执行。

• 查看所有事件

SHOW EVENTS;

查询表分区信息

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;

总结 

到此这篇关于MySQL按时间进行表分区的文章就介绍到这了,更多相关MySQL按时间表分区内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！