## 模块 2：交换分区 + 在模块中，我们将创建一个 List COLUMNS 分区表，向表中插入数据，然后使用 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 COLUMNS 分区表 `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` 的一个分区与非分区表 `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) ```