快捷導(dǎo)航

mysql數(shù)據(jù)庫的分區(qū)表示例代碼

更新時間：2024年11月09日 08:31:22 作者：材化胖虎

這篇文章主要介紹了mysql數(shù)據(jù)庫的分區(qū)表的相關(guān)資料,文章介紹了兩種創(chuàng)建SQL表分區(qū)的方法,分別是手動創(chuàng)建和使用MySQL的定時事件來自動創(chuàng)建分區(qū),手動創(chuàng)建分區(qū)時,需要在代碼中判斷分區(qū)并新增,可能會引入一些問題,需要的朋友可以參考下

1.SQL表創(chuàng)建

下面以時間范圍進行創(chuàng)建（每月一個分區(qū)，表中創(chuàng)建了四個月的分區(qū)）

創(chuàng)建：

CREATE TABLE test_table (  
    id INT NOT NULL AUTO_INCREMENT,  
    content VARCHAR(255),  
    create_time DATETIME NOT NULL,
   PRIMARY KEY (id, create_time) 
) PARTITION BY RANGE (TO_DAYS(create_time)) (  
    PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-01')),  
    PARTITION p20240701 VALUES LESS THAN (TO_DAYS('2024-07-01')),  
    PARTITION p20241801 VALUES LESS THAN (TO_DAYS('2024-08-01')),  
    PARTITION p20240901 VALUES LESS THAN (TO_DAYS('2024-09-01'))
);  

查詢分區(qū)詳情：
SELECT *
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_NAME = 'test_table';

2、mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="*.infrastructure.mapper.TestTableMapper">

    <resultMap id="TestTable" type="*.domain.entity.TestTable">
        <id column="id" property="id" typeHandler="org.apache.ibatis.type.LongTypeHandler"/>
        <result property="content" column="content" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"
                typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
    </resultMap>

    <!-- 創(chuàng)建新分區(qū) -->
    <update id="createNewPartition">
        ALTER TABLE TEST_TABLE
            ADD PARTITION (  
                PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
            )
    </update>

    <!-- 刪除舊分區(qū) -->
    <update id="dropPartition">
        ALTER TABLE TEST_TABLE
        DROP
        PARTITION
        ${partitionName}
    </update>

    <!--查詢是否存在分區(qū)-->
    <select id="exitsPartition" resultType="boolean">
        SELECT COUNT(1) > 0
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME = 'TEST_TABLE'
          AND PARTITION_NAME = #{partitionName}
    </select>

</mapper>

3、service

package *.domain.service;

import *.domain.entity.TestTable;
import *.infrastructure.repo.TestTableRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.Random;

@Service
public class TestTableService {
    @Autowired
    TestTableRepository repository;

	// 插入數(shù)據(jù)，如果分區(qū)不存在，就創(chuàng)建分區(qū)，重新插入
    public void insert() {
        TestTable testTable = new TestTable();
        testTable.setContent("test");

        Random random = new Random();
        int i = Math.abs(random.nextInt()) % 365;
        LocalDateTime dateTime = LocalDateTime.now().minusDays(i);
        testTable.setCreateTime(dateTime);

        try {
            repository.getBaseMapper().insert(testTable);
        } catch (DataAccessException e) {
            LocalDate nextMonthFirstDay = YearMonth.from(dateTime).plusMonths(1).atDay(1);
            String lessThanValue = nextMonthFirstDay.format(DateTimeFormatter.ISO_DATE);
            String partitionName = "p" + lessThanValue.replaceAll("-", "");
            // 創(chuàng)建分區(qū)時加鎖，如果是多節(jié)點，需要分布式鎖
            synchronized (this) {
                if (!repository.getBaseMapper().exitsPartition(partitionName)) {
                    repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
                }
            }
            repository.getBaseMapper().insert(testTable);
        }
    }

	// 創(chuàng)建分區(qū)
    public void createNewPartition(String partitionName, String lessThanValue) {
        repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
    }

	// 刪除分區(qū)
    public void dropPartition(String partitionName) {
        repository.getBaseMapper().dropPartition(partitionName);
    }
}

----------------分割線-------------------------------

上述方法用代碼來判斷分區(qū)，新增分區(qū)，可能會引入一些奇奇怪怪的問題，因此，優(yōu)化如下：

【針對mysql，使用mysql的定時事件】

1、首先確認mysql的時間調(diào)度器是否已經(jīng)開啟：

-- 查詢事件調(diào)度器是否開啟
SHOW VARIABLES LIKE 'event_scheduler'; 

-- 確保事件調(diào)度器已經(jīng)開啟  
SET GLOBAL event_scheduler = ON;

2、寫存儲過程，用于創(chuàng)建新的分區(qū), 這里是按天創(chuàng)建新的分區(qū)

DELIMITER //  
  
CREATE PROCEDURE `AddDailyPartition`()  
BEGIN  
    DECLARE tomorrow DATE;  
    DECLARE partition_name VARCHAR(20);  
  
    -- 計算明天的日期  
    SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%Y-%m-%d');  
    SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m%d'));  
  
    -- 構(gòu)建ALTER TABLE語句來添加分區(qū)  
    SET @sql = CONCAT('ALTER TABLE TEST_TABLE ',  
                      'ADD PARTITION (PARTITION ', partition_name,   
                      ' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');  
  
    -- 執(zhí)行ALTER TABLE語句  
    PREPARE stmt FROM @sql;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
END //  
  
DELIMITER ;

3、創(chuàng)建定時事件，調(diào)用存儲過程

-- 創(chuàng)建定時事件  
CREATE EVENT `CreateDailyPartition`  
    ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE())  
    DO CALL AddDailyPartition();

4、查看已經(jīng)創(chuàng)建的定時事件

SELECT * FROM information_schema.EVENTS; 

在查看事件時，重要的列包括：
EVENT_NAME: 事件的名稱。
EVENT_SCHEMA: 事件所屬的數(shù)據(jù)庫。
STATUS: 事件的狀態(tài)，比如是否為ENABLED或DISABLED。
STARTS: 事件開始的時間。
ENDS: 事件結(jié)束的時間（如果有設(shè)置的話）。
LAST_EXECUTED: 事件上一次執(zhí)行的時間。
EVENT_DEFINITION: 事件定義，即事件中要執(zhí)行的SQL語句。