1、创建分区表
DROP TABLE IF EXISTS `tb_parttion`;
CREATE TABLE tb_parttion (
`id` varchar(20) NOT NULL DEFAULT '',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ssq` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`dp_dm` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
)
PARTITION BY LIST COLUMNS(ssq) (
PARTITION p201901 VALUES IN ('201901'),
PARTITION p201902 VALUES IN ('201902'),
PARTITION p201903 VALUES IN ('201903'),
PARTITION p201904 VALUES IN ('201904')
);
2、查看分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
FROM_DAYS(partition_description) lessthan_sendtime,
table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME='tb_parttion';
3、动态添加分区
ALTER TABLE tb_parttion ADD PARTITION (
PARTITION bbb VALUES IN ('bbb')
)
4、动态删除分区
ALTER TABLE tb_parttion DROP PARTITION bbb;
5、清空分区数据
ALTER TABLE tb_parttion TRUNCATE PARTITION bbb;
