Mysql8 创建表，按年/月或其他条件进行分区存储

一.创建订单表orders ：

--------------------如果表有主键，那么分区列也必须设置成主键--------------------------

CREATE TABLE orders (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p2021_01 VALUES LESS THAN ('2021-02-01'),
    PARTITION p2021_02 VALUES LESS THAN ('2021-03-01'),
    PARTITION p2021_03 VALUES LESS THAN ('2021-04-01'),
    PARTITION p2023_01 VALUES LESS THAN ('2023-02-01')
);

测试看看

INSERT INTO orders(id, sale_date, amount) VALUES(1,'1992-01-01',10.1)

二.更改分区办法：

1.改为按月进行分区存储

 
ALTER TABLE orders PARTITION BY RANGE (MONTH(sale_date)) (  
 PARTITION p310 VALUES LESS THAN (4)
                 );

2.改为按年进行分区存储

 
ALTER TABLE orders PARTITION BY RANGE (year(sale_date)) (  
 PARTITION p310 VALUES LESS THAN (2021)
                 );

3.改为按COLUMNS进行分区存储

ALTER TABLE orders PARTITION BY RANGE COLUMNS(sale_date) (  
 PARTITION p3101 VALUES LESS THAN ('2021-01-01') );

注意 COLUMNS(sale_date) 外部没有括号

二.增加分区

ALTER TABLE orders ADD PARTITION (  
 PARTITION p202302 VALUES LESS THAN ('2023-03-01')  
 );

四.查询分区

SELECT 1
FROM information_schema.partitions
WHERE table_schema = 'test_db' -- 替换为您的数据库名
AND TABLE_NAME = 'orders' -- 替换为您的表名
AND partition_name = '20230301'; -- 替换为您想要检查的分区名