快捷導(dǎo)航

MySQL按月自動(dòng)設(shè)置表分區(qū)的實(shí)現(xiàn)

更新時(shí)間：2024年08月08日 09:44:50 作者：LaFeng233

本文主要介紹了MySQL按月自動(dòng)設(shè)置表分區(qū)的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

開始檢查

首先，確保 ticket_history_info表是一個(gè)分區(qū)表。如果未設(shè)置分區(qū)，需要修改表結(jié)構(gòu)以支持分區(qū)，將 ticket_history_info 表設(shè)置為按日期范圍進(jìn)行分區(qū)

ALTER TABLE ticket_history_info
PARTITION BY RANGE (TO_DAYS(CALL_DATE)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2022-09-01'))
);

1.創(chuàng)建分區(qū)函數(shù)

-- 創(chuàng)建分區(qū)函數(shù)
DELIMITER //
CREATE FUNCTION get_partition_name(p_date DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE p_month VARCHAR(2);
    DECLARE p_year VARCHAR(4);
    SET p_month = LPAD(MONTH(p_date), 2, '0');
    SET p_year = YEAR(p_date);
    RETURN CONCAT('p', p_year, p_month); 
END//
DELIMITER ;

檢查是否成功創(chuàng)建函數(shù)

SHOW FUNCTION STATUS LIKE 'get_partition_name';

2.創(chuàng)建存儲(chǔ)過程，用于自動(dòng)生成分區(qū)

-- 存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE create_monthly_partition()
BEGIN
    DECLARE next_month VARCHAR(20);
    DECLARE next_month_first_day DATE;
    
    -- 計(jì)算下一個(gè)月份的名稱和下個(gè)月的第一天
    SET next_month = CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
    SET next_month_first_day = LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) + INTERVAL 1 DAY;
    
    -- 檢查分區(qū)是否已存在
    IF NOT EXISTS (
        SELECT NULL
        FROM information_schema.PARTITIONS
        WHERE TABLE_NAME = 'ticket_history_info' AND TABLE_SCHEMA = 'ccm' AND PARTITION_NAME = next_month
    ) THEN
        -- 創(chuàng)建新的分區(qū)
        SET @sql = CONCAT('ALTER TABLE ticket_history_info ADD PARTITION (PARTITION ', next_month, ' VALUES LESS THAN (TO_DAYS(\'', next_month_first_day, '\')))');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//
DELIMITER ;

進(jìn)行測(cè)試

檢查是否成功創(chuàng)建函數(shù)

SHOW PROCEDURE STATUS LIKE 'create_monthly_partition';

執(zhí)行函數(shù)

CALL create_monthly_partition();

查詢表分區(qū)，查看是否成功分區(qū)。

SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME = '表名' AND TABLE_SCHEMA = '數(shù)據(jù)庫名';

可通過修改本地系統(tǒng)時(shí)間，來進(jìn)行反復(fù)測(cè)試是否可按照月份進(jìn)行分區(qū)。

返回成功，顯示已創(chuàng)建表分區(qū)。

3.創(chuàng)建自動(dòng)刪除半年以前的表空間函數(shù)

DELIMITER //

CREATE PROCEDURE delete_old_partitions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE drop_partition_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT PARTITION_NAME 
        FROM information_schema.PARTITIONS 
        WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名' 
          AND TABLE_NAME = 'ticket_history_info' 
          AND PARTITION_DESCRIPTION < TO_DAYS(NOW() - INTERVAL 6 MONTH);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO drop_partition_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ticket_history_info DROP PARTITION ', drop_partition_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;

   -- 刪除超過半年的數(shù)據(jù)
    DELETE FROM ticket_history_info_copy1
    WHERE CALL_DATE < CURDATE() - INTERVAL 6 MONTH;

END//

DELIMITER ;

4.創(chuàng)建調(diào)度任務(wù)，修改到每月最后一天執(zhí)行執(zhí)行存儲(chǔ)過程

CREATE EVENT IF NOT EXISTS monthly_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS CONCAT(DATE_FORMAT(LAST_DAY(CURRENT_DATE), '%Y-%m-'), DAY(LAST_DAY(CURRENT_DATE)), ' 23:00:00')
DO
BEGIN
    CALL create_monthly_partition();
    CALL delete_old_partitions();
END;

查看事件調(diào)度器是否啟動(dòng)

SET GLOBAL event_scheduler = ON;

查看事件

SHOW EVENTS;

或

SHOW EVENTS FROM `數(shù)據(jù)庫名`;

創(chuàng)建成功

5.進(jìn)行測(cè)試

插入測(cè)試數(shù)據(jù)進(jìn)行測(cè)試，根據(jù)CALL_DATE字段進(jìn)行數(shù)據(jù)分區(qū)，

INSERT INTO ticket_history_info (CALL_DATE, SRC_ADD, CALLING_NUM, DURATION) VALUES
('2024-05-24 00:00:00', 'Address1', '1234567890', 60),
('2024-06-20 00:00:00', 'Address2', '0987654321', 120),
('2024-05-10 00:00:00', 'Address3', '1122334455', 180),
('2024-10-01 00:00:00', 'Address4', '5566778899', 240);

-查詢每個(gè)分區(qū)的行數(shù)

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'ccm' AND TABLE_NAME = 'ticket_history_info';

或者單獨(dú)查詢某一個(gè)分區(qū)的數(shù)據(jù)

SELECT *
FROM ticket_history_info PARTITION (p202406);

到此這篇關(guān)于MySQL按月自動(dòng)設(shè)置表分區(qū)的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 表分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: