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));

――シンタックスデモ
――ステップ1：hash_range_1からtempに移動します
ALTER TABLE  hash_range_1 
EXCHANGE subpartition template s20240111
with table temp with validation;
-- ステップ2：tempからhash_range_2に移動します
ALTER TABLE  hash_range_2 
EXCHANGE subpartition template s20240111
with table temp with validation;