tencent cloud

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

-- Move data from hash_range_1 to hash_range_2 in s20240111.

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 is identical to 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));

-- Create a temp table with the same partitions and table structure as 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));

-- Syntax demonstration
-- Step 1: hash_range_1 --> temp
ALTER TABLE  hash_range_1 
EXCHANGE subpartition template s20240111
with table temp with validation;
-- Step 2: temp --> hash_range_2
ALTER TABLE  hash_range_2 
EXCHANGE subpartition template s20240111
with table temp with validation;