ALTER TABLE pt EXCHANGE SUBPARTITION TEMPLATE p WITH TABLE nt WITH VALIDATION / WITHOUT VALIDATION

-- 将s20240111中的数据从hash_range_1转到hash_range_2

CREATE TABLE hash_range_1(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
SUBPARTITION BY RANGE(tdsql_day(hired))
SUBPARTITION TEMPLATE
(SUBPARTITION s20240111 VALUES LESS THAN (20240111),
 SUBPARTITION s20240112 VALUES LESS THAN (20240112),
 SUBPARTITION s20240113 VALUES LESS THAN (20240113))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- (hash_range_2与hash_range_1完全相同)
CREATE TABLE hash_range_2(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
SUBPARTITION BY RANGE(tdsql_day(hired))
SUBPARTITION TEMPLATE
(SUBPARTITION s20230111 VALUES LESS THAN (20240111),
 SUBPARTITION s20230112 VALUES LESS THAN (20240112),
 SUBPARTITION s20230113 VALUES LESS THAN (20240113))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- 新建一个临时temp表，一级分区和表结构和hash_range_1一致
CREATE TABLE temp(
id INT NOT NULL,
fname VARCHAR(30),
hired date NOT NULL DEFAULT '9999-12-31',
primary key (id, hired))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(murmurHashCodeAndMod(id,2))
(PARTITION p1 VALUES IN (0), 
 PARTITION p2 VALUES IN (1));

-- 语法演示
-- 第一步: hash_range_1--> temp
ALTER TABLE  hash_range_1 
EXCHANGE subpartition template s20240111
with table temp with validation;
-- 第二步: temp --> hash_range_2
ALTER TABLE  hash_range_2 
EXCHANGE subpartition template s20240111
with table temp with validation;