快捷導(dǎo)航

MySQL按時間進行表分區(qū)的方法代碼

更新時間：2024年09月29日 10:47:56 作者：逢生博客

本文介紹如何在MySQL中創(chuàng)建按月份分區(qū)的表,并通過修改配置文件或使用數(shù)據(jù)庫事件來實現(xiàn)自動分區(qū),文中通過代碼介紹的非常詳細,對大家學(xué)習(xí)或者使用MySQL具有一定的參考借鑒價值,需要的朋友可以參考下

創(chuàng)建按月份分區(qū)的表

create table if not exists table_name
(
    id          bigint auto_increment comment '主鍵id',
    
    create_by   varchar(64)                        not null comment '創(chuàng)建者',
    create_time datetime default CURRENT_TIMESTAMP not null comment '創(chuàng)建時間',
    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'))
);

開啟數(shù)據(jù)庫事件

臨時配置（服務(wù)重啟會失效）

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

修改my.cnf文件（推薦）

進入MySQL所在的服務(wù)器

sudo vi /etc/my.cnf

找到 [mysqld]，添加如下內(nèi)容

event_scheduler = ON

重啟 MySQL 服務(wù)以使更改生效

sudo systemctl restart mysqld

定時事件自動建分區(qū)

例：提前創(chuàng)建下個月的分區(qū)，如2024-04-01創(chuàng)建5月份的分區(qū) P202405，2024-05-01創(chuàng)建6月份的分區(qū)。

create event event_create_partition on schedule
    every '1' MONTH starts '2024-04-01 00:00:00'
    on completion preserve
    enable 
    do 
    BEGIN
    
    	-- 當(dāng)前時間 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;

注意： 數(shù)據(jù)庫event_scheduler = ON 要確認有開啟，否則 event_create_partition 事件不會執(zhí)行。

查看所有事件

SHOW EVENTS;

查詢表分區(qū)信息

SELECT
   TABLE_SCHEMA,
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_METHOD,
   PARTITION_EXPRESSION,
   SUBPARTITION_NAME,
   SUBPARTITION_METHOD,
   SUBPARTITION_EXPRESSION
FROM information_schema.partitions
WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名'
AND TABLE_NAME IN ('table_name')
ORDER BY PARTITION_NAME DESC;