CREATE TABLE user_range (
create_time datetime NULL DEFAULT NULL COMMENT '创建时间',
update_by varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
update_time datetime NULL DEFAULT NULL COMMENT '更新时间',
remark varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注'
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'bao' ROW_FORMAT = Compact
PARTITION BY RANGE COLUMNS(create_time) (
PARTITION p0 VALUES LESS THAN ('2024-11-01'),
PARTITION p1 VALUES LESS THAN ('2024-12-01'),
PARTITION p2 VALUES LESS THAN ('2025-01-01'),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
在MySQL中，当表存在主键（primary key）或唯一键（unique key）时，分区的列必须是这些键的一个组成部分的原因主要涉及到数据的完整性和查询性能；
修改分区，也可创建
ALTER TABLE user_range
PARTITION BY RANGE COLUMNS(create_time) (
PARTITION p0 VALUES LESS THAN ('2024-11-01'),
PARTITION p1 VALUES LESS THAN ('2024-12-01'),
PARTITION p2 VALUES LESS THAN ('2025-01-01'),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
EXPLAIN PARTITIONS SELECT * FROM user_range WHERE create_time BETWEEN '2024-10-01' AND '2024-11-31'
查看分区信息
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='user_range'
