论坛 › PostgreSQL › mysql的分区表

张国伟 发表于 2024-10-26 06:36:26

mysql的分区表

1.SQL表创建
下面以时间范围进行创建（每月一个分区，表中创建了四个月的分区）
创建：

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'))
);

查询分区详情：
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>

    <!-- 创建新分区 -->
    <update id="createNewPartition">
      ALTER TABLE TEST_TABLE
            ADD PARTITION (
                PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
            )
    </update>

    <!-- 删除旧分区 -->
    <update id="dropPartition">
      ALTER TABLE TEST_TABLE
      DROP
      PARTITION
      ${partitionName}
    </update>

    <!--查询是否存在分区-->
    <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;

        // 插入数据，如果分区不存在，就创建分区，重新插入
    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("-", "");
            // 创建分区时加锁，如果是多节点，需要分布式锁
            synchronized (this) {
                if (!repository.getBaseMapper().exitsPartition(partitionName)) {
                  repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
                }
            }
            repository.getBaseMapper().insert(testTable);
      }
    }

        // 创建分区
    public void createNewPartition(String partitionName, String lessThanValue) {
      repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
    }

        // 删除分区
    public void dropPartition(String partitionName) {
      repository.getBaseMapper().dropPartition(partitionName);
    }
}
----------------分割线-------------------------------
上述方法用代码来判定分区，新增分区，大概会引入一些奇奇怪怪的题目，因此，优化如下：

【针对mysql，使用mysql的定时事件】
1、首先确认mysql的时间调理器是否已经开启：
-- 查询事件调度器是否开启
SHOW VARIABLES LIKE 'event_scheduler';

-- 确保事件调度器已经开启
SET GLOBAL event_scheduler = ON; 2、写存储过程，用于创建新的分区, 这里是按天创建新的分区
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'));

    -- 构建ALTER TABLE语句来添加分区
    SET @sql = CONCAT('ALTER TABLE TEST_TABLE ',
                      'ADD PARTITION (PARTITION ', partition_name,   
                      ' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');

    -- 执行ALTER TABLE语句
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ; 3、创建定时事件，调用存储过程
-- 创建定时事件
CREATE EVENT `CreateDailyPartition`
    ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE())
    DO CALL AddDailyPartition(); 4、查察已经创建的定时事件
SELECT * FROM information_schema.EVENTS;

在查看事件时，重要的列包括：
EVENT_NAME: 事件的名称。
EVENT_SCHEMA: 事件所属的数据库。
STATUS: 事件的状态，比如是否为ENABLED或DISABLED。
STARTS: 事件开始的时间。
ENDS: 事件结束的时间（如果有设置的话）。
LAST_EXECUTED: 事件上一次执行的时间。
EVENT_DEFINITION: 事件定义，即事件中要执行的SQL语句。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

查看完整版本: mysql的分区表