mysql 分区表数值示例

数据库环境：mysql 8.0

测试表：cus_p_bi_temp_drop_202212011644

查看建表语句:

show create table flux_bi.cus_p_bi_temp_drop_202212011644;


CREATE TABLE `cus_p_bi_temp_drop_202212011644` (
  `dt` int DEFAULT NULL,
  `warehouseProv` varchar(20),
  `orderNo` varchar(20) DEFAULT NULL,
  `createTime` datetime NOT NULL COMMENT 'createTime'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

两种思路建分区，一是用仓库，二是用DT，每天一个分区，建议DT

alter table  cus_p_bi_temp_drop_202212011644 partition by list COLUMNS(dt)(
	partition p20220701 values in (20220701),
	partition p20220702 values in (20220702),
	partition p20220703 values in (20220703),
	partition p20220704 values in (20220704),
	partition p20220705 values in (20220705),
	partition p20220706 values in (20220706),
	partition p20220707 values in (20220707),
	partition p20220708 values in (20220708),
	partition p20220709 values in (20220709));

查看分区，我是新建了半年的分区，所以数量会多一些

SELECT 
TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='cus_p_bi_temp_drop_202212011644';

检查sql语句是否应用了分区--OK，确实使用了分区

 清空分区数据--OK

ALTER TABLE  cus_p_bi_temp_drop_202212011644 truncate PARTITION p20221201

删除分区数据--OK

ALTER TABLE  cus_p_bi_temp_drop_202212011644 DROP PARTITION p20221201