mysql跨分区查询语句

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

SELECT * FROM sales WHERE sale_date BETWEEN '2012-01-01' AND '2018-12-31';

-- 添加索引
ALTER TABLE sales ADD INDEX idx_sale_date (sale_date);

-- 优化查询
SELECT * FROM sales USE INDEX (idx_sale_date) WHERE sale_date BETWEEN '2012-01-01' AND '2018-12-31';

-- 重新分区
ALTER TABLE sales REORGANIZE PARTITION p0,p1 INTO (
    PARTITION p0 VALUES LESS THAN (2012),
    PARTITION p1 VALUES LESS THAN (2015)
);