Partition representation example code of mysql database

create：

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

Query partition details：
SELECT *
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_NAME = 'test_table';

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-////DTD Mapper 3.0//EN"
        "/dtd/">
<mapper namespace="*.">

    <resultMap  type="*.">
        <id column="id" property="id" typeHandler=""/>
        <result property="content" column="content" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"
                typeHandler=""/>
    </resultMap>

    <!-- Create a new partition -->
    <update >
        ALTER TABLE TEST_TABLE
            ADD PARTITION (  
                PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
            )
    </update>

    <!-- Delete the old partition -->
    <update >
        ALTER TABLE TEST_TABLE
        DROP
        PARTITION
        ${partitionName}
    </update>

    <!--Query if there is a partition-->
    <select  resultType="boolean">
        SELECT COUNT(1) > 0
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME = 'TEST_TABLE'
          AND PARTITION_NAME = #{partitionName}
    </select>

</mapper>

package *.;

import *.;
import *.;

import ;
import ;
import ;

import ;
import ;
import ;
import ;
import ;

@Service
public class TestTableService {
    @Autowired
    TestTableRepository repository;

	// Insert data. If the partition does not exist, create the partition and insert it again    public void insert() {
        TestTable testTable = new TestTable();
        ("test");

        Random random = new Random();
        int i = (()) % 365;
        LocalDateTime dateTime = ().minusDays(i);
        (dateTime);

        try {
            ().insert(testTable);
        } catch (DataAccessException e) {
            LocalDate nextMonthFirstDay = (dateTime).plusMonths(1).atDay(1);
            String lessThanValue = (DateTimeFormatter.ISO_DATE);
            String partitionName = "p" + ("-", "");
            // Add locks when creating partitions. If it is multiple nodes, a distributed lock is required            synchronized (this) {
                if (!().exitsPartition(partitionName)) {
                    ().createNewPartition(partitionName, lessThanValue);
                }
            }
            ().insert(testTable);
        }
    }

	// Create partition    public void createNewPartition(String partitionName, String lessThanValue) {
        ().createNewPartition(partitionName, lessThanValue);
    }

	// Delete the partition    public void dropPartition(String partitionName) {
        ().dropPartition(partitionName);
    }
}

-- Query whether the event scheduler is on
SHOW VARIABLES LIKE 'event_scheduler'; 

-- Make sure the event scheduler is turned on  
SET GLOBAL event_scheduler = ON;  

DELIMITER //  
  
CREATE PROCEDURE `AddDailyPartition`()  
BEGIN  
    DECLARE tomorrow DATE;  
    DECLARE partition_name VARCHAR(20);  
  
    -- Calculate the date of tomorrow  
    SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%Y-%m-%d');  
    SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m%d'));  
  
    -- BuildALTER TABLEStatement to add partitions  
    SET @sql = CONCAT('ALTER TABLE TEST_TABLE ',  
                      'ADD PARTITION (PARTITION ', partition_name,   
                      ' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');  
  
    -- implementALTER TABLEStatement  
    PREPARE stmt FROM @sql;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
END //  
  
DELIMITER ;  

-- Create a timed event  
CREATE EVENT `CreateDailyPartition`  
    ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE())  
    DO CALL AddDailyPartition();  

SELECT * FROM information_schema.EVENTS; 

When viewing events，Important columns include：
EVENT_NAME: The name of the event。
EVENT_SCHEMA: The database to which the event belongs。
STATUS: Status of events，For example, whether it isENABLEDorDISABLED。
STARTS: Time of the event。
ENDS: Time of the end of the event（If there is a setting）。
LAST_EXECUTED: The last time the event was executed。
EVENT_DEFINITION: Event definition，That is, what to execute in the eventSQLStatement。