mysql分区关联查询

-- 创建分区表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT,
    customer_name VARCHAR(100),
    PRIMARY KEY (customer_id)
) PARTITION BY LIST (customer_id) (
    PARTITION p0 VALUES IN (1, 2, 3),
    PARTITION p1 VALUES IN (4, 5, 6),
    PARTITION p2 VALUES IN (7, 8, 9)
);

-- 插入示例数据
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2015-01-01', 100.00),
(2, '2018-05-15', 200.00),
(3, '2021-11-30', 300.00);

INSERT INTO customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie');

-- 关联查询
SELECT o.order_id, o.order_date, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;