mysql已有表新建分区

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
);

ALTER TABLE sales
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 创建表
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
);

-- 插入示例数据
INSERT INTO sales (sale_date, amount) VALUES
('2009-01-01', 100),
('2015-06-15', 200),
('2021-12-31', 300);

-- 新建分区
ALTER TABLE sales
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 查询分区表
SELECT * FROM sales WHERE sale_date BETWEEN '2010-01-01' AND '2020-12-31';