mysql 动态指定分区表

-- 创建一个RANGE分区表
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 动态插入数据到指定分区
DELIMITER $$
CREATE PROCEDURE InsertSales(IN sale_date DATE, IN amount DECIMAL(10, 2))
BEGIN
    DECLARE partition_name VARCHAR(10);
    SET partition_name = CONCAT('p', YEAR(sale_date) / 10);
    SET @sql = CONCAT('INSERT INTO sales (sale_date, amount) PARTITION(', partition_name, ') VALUES (?, ?)');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING sale_date, amount;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 调用存储过程插入数据
CALL InsertSales('2015-05-01', 100.00);

-- 检查分区是否存在
SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales' AND PARTITION_NAME = 'p15';

-- 如果分区不存在，可以动态添加分区
ALTER TABLE sales ADD PARTITION (PARTITION p15 VALUES LESS THAN (2015));