mysql 分区表创建 删除 修改 查看

分区表的创建

分区表的主键/唯一键都必须包含分区键

-- 创建范围分区表
CREATE TABLE `test_first` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usr_id` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '用户号',
	`remark` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  `rec_upd_ts` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '记录更新时间',
  `rec_crt_ts` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '记录创建时间',
  `get_date` date NOT NULL COMMENT '分区字段，权益获取日期',
  PRIMARY KEY (`id`,`get_date`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试分区表'
/*!50500 PARTITION BY RANGE  COLUMNS(get_date)
(PARTITION p_2021 VALUES LESS THAN ('2022-1-1') ,
 PARTITION p_2022 VALUES LESS THAN ('2023-1-1') ,
 PARTITION p_2023 VALUES LESS THAN ('2024-1-1') ,
 PARTITION p_2024 VALUES LESS THAN ('2025-1-1') ,
 PARTITION p_2025 VALUES LESS THAN ('2026-1-1') ,
 PARTITION p_2026 VALUES LESS THAN ('2027-1-1') ,
 PARTITION p_2027 VALUES LESS THAN ('2028-1-1') ,
 PARTITION p_2028 VALUES LESS THAN ('2029-1-1') ,
 PARTITION p_2029 VALUES LESS THAN ('2030-1-1') ,
 PARTITION p_2030 VALUES LESS THAN ('2031-1-1') ,
 PARTITION p_2031 VALUES LESS THAN ('2032-1-1') ,
 PARTITION p_catchall VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

说明：
注意：range对应的分区建值必须时数值或可比较大小类型）。
range：代表分区方式为范围分区
less than：表示小于
表示小于某个具体值，比如 less than (2022-1-1)，那么分区字段的值小于2022-1-1的分在该分区，不包含2022-1-1。
PARTITION p_catchall VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 表示其余范围内的将默认落到p_catchall分区，若无需默认分区则去除该语句。

-- 创建list分区表
CREATE TABLE `test_second` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usr_id` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '用户号',
	`remark` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  `rec_upd_ts` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '记录更新时间',
  `rec_crt_ts` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '记录创建时间',
	`type` char(2) COLLATE utf8_bin NOT NULL COMMENT '类型',
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试list分区表'
/*!50500 PARTITION BY LIST  COLUMNS(type)
(PARTITION p_1 values IN ('01','11') ,
 PARTITION p_2 values IN ('02','12') ,
 PARTITION p_3 values IN ('03','13') ) */;

说明：
LIST:代表分区方式为给定值分区
in : 表示为给定值时
当type值为‘01’，‘11’时就存放p_1分区

新增分区

-- 新增范围分区
alter TABLE `test_first` add PARTITION(
  PARTITION p_2032 VALUES LESS THAN ('2033-1-1') ENGINE = InnoDB
);

-- 新增list分区
alter TABLE `test_second` add PARTITION(
  PARTITION p_4 values IN ('04','14') ENGINE = InnoDB
);

说明：因为分区是递增的，如果创建分区时执行了范围外的默认分区，则不能新添加分区，因为即便不添加分区，数据也会落到默认分区中。且如果没有默认分区，新添加分区应添加在最后

删除分区

-- 删除指定分区(表中数据也会清理)
alter table `test_first`  drop PARTITION p_2029 ;

-- 删除分区中的数据，分区仍保留
alter table `test_first`  truncate PARTITION p_2029 ;

-- 删除多个分区(表中数据也会清理)
alter table `test_first`  drop PARTITION p_2028,p_2029 ;

--删除表的所有分区（只删除分区，不删除表中数据）
alter table test_first remove partitioning;

查看分区

-- 查看是否为分区表
SHOW table STATUS;

-- 查看指定表的分区信息
 select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='test_first';  

-- 查看指定分区中的数据
SELECT * FROM test_first PARTITION(p_2022);

将一个分区的数据更新到另一个分区中可直接更新该字段分区值或者新增到另一分区并删除原分区

-- 修改分区值添加到另一分区中
update test_first set get_date = '2022-01-01' where get_date < '2022-01-01'; 

-- 将2021分区数据添加到2022分区
insert into test_first (usr_id,remark,get_date,rec_crt_ts,rec_upd_ts) select usr_id,remark,'2022-01-01',rec_crt_ts,rec_upd_ts from test_first where get_date < '2022-01-01';