设置mysql表的储存上限

DELIMITER //

CREATE TRIGGER check_table_size_before_insert
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    DECLARE table_size INT;
    SELECT SUM(data_length + index_length) INTO table_size
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table';

    IF table_size > 1073741824 THEN -- 1GB
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Table size limit exceeded';
    END IF;
END //

DELIMITER ;

CREATE EVENT clean_old_data
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
    DELETE FROM your_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 MONTH);
    OPTIMIZE TABLE your_table;
END;

CREATE TABLE your_table (
    id INT NOT NULL,
    data VARCHAR(255),
    created_at DATETIME
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);