## モジュール 2: パーティションの交換 + この演習では、LIST COLUMNパーティションテーブルを作成し、テーブルにデータを挿入してから、EXCHANGE PARTITION を使用してパーティションと非パーティションテーブル間でデータを交換します。 ### 手順 1. 非パーティションテーブル `students` を作成する ```sql CREATE TABLE students ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ); ``` ```output tidb>CREATE TABLE students ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL -> ); Query OK, 0 rows affected (0.53 sec) ``` ### 手順 2. 変数 `tidb_enable_list_partition` の値を確認する ```sql SHOW VARIABLES LIKE 'tidb_enable_list_partition'; ``` ```output tidb>SHOW VARIABLES LIKE 'tidb_enable_list_partition'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_enable_list_partition | ON | +----------------------------+-------+ 1 row in set (0.00 sec) ``` + LISTパーティションテーブルを作成する前に、セッション変数 tidb_enable_list_partition の値を ON に設定する必要があります。 ### 手順 3. 新しいLIST COLUMNパーティションテーブル `students1` を作成する ```sql CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT ``` + エラー `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` の理由は、PARTITION BY LIST の後のキーが `INT` 型でなければならないためです。 ```sql CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing','Chicago')); ``` ```output ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + エラー `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` の理由は、ユニークキーを持つテーブルの場合、パーティション式のすべてのカラムをユニークキーに含める必要があるためです。プライマリキーもユニークキーです。 ```sql CREATE TABLE students1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing','Chicago')); ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### 手順 4. テーブル `students1` に値を挿入する ```sql SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; INSERT INTO students1 VALUES(1, 'Chars', 'Smith', 'NewYork', '2005-01-02', 1); INSERT INTO students1 VALUES(2, 'Tom', 'Garcia', 'NewYork', '2005-02-01', 6); INSERT INTO students1 VALUES(3, 'Crabb', 'Ford', 'NewYork', '2004-11-19', 7); ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; ``` ```output +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.01 sec) +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 3 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) ``` + すべてのデータが `p0` パーティションにあることが確認できます。 ### 手順 5. パーティションを交換する + テーブル `students1` のパーティションの1つを非パーティションテーブル `students` に交換します。 ```sql ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ``` ```output ERROR 1736 (HY000): Tables have different definitions ``` + エラー `ERROR 1736 (HY000): Tables have different definitions` の理由は、テーブル `students1` のプライマリキーがカラム `(id, city)` であるのに対し、テーブル `students` ではカラム `id` のみであるためです。 ```sql DROP TABLE students; CREATE TABLE students ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ); ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; SELECT * FROM students; ``` ```output +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) +----+-------+--------+---------+------------+------+ | id | fname | lname | city | DOB | dept | +----+-------+--------+---------+------------+------+ | 1 | Chars | Smith | NewYork | 2005-01-02 | 1 | | 2 | Tom | Garcia | NewYork | 2005-02-01 | 6 | | 3 | Crabb | Ford | NewYork | 2004-11-19 | 7 | +----+-------+--------+---------+------------+------+ 3 rows in set (0.00 sec) ``` ### 手順 6. テーブルをクリーンアップする ```sql DROP TABLE students; DROP TABLE students1; ``` ```output Query OK, 0 rows affected (0.52 sec) ```