用非分区表交换子分区
你也可以执行ALTER TABLE … EXCHANGE PARTITION语句使用一个未分区表来交换一个已分区表的子分区。在下面的例子中，我们首先创建一个 表es，它按范围分区，并按键进行子分区，然后像对表e那样填充这个表，然后创建一个空的、无分区的表es2副本，如下所示：
mysql> CREATE TABLE es (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30)
-> )
-> PARTITION BY RANGE (id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (50),
-> PARTITION p1 VALUES LESS THAN (100),
-> PARTITION p2 VALUES LESS THAN (150),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO es VALUES
-> (1669, "Jim", "Smith"),
-> (337, "Mary", "Jones"),
-> (16, "Frank", "White"),
-> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
尽管在创建表时我们没有显式地命名任何子分区，但是我们可以通过查询INFORMATION_SCHEMA。PARTITIONS表的UBPARTITION_NAME来获得这些生 成的名称，如下所示：
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'es'; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 1 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 3 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.00 sec)
下面的ALTER TABLE语句将子分区p3sp0表es与未分区表es2交换：
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2; Query OK, 0 rows affected (0.01 sec)
您可以通过发出以下查询来验证行是否被交换：
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'es'; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 1 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 0 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.01 sec) mysql> SELECT * FROM es2; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ 3 rows in set (0.01 sec)
如果一个表被子分区了，你只能用一个未分区的表交换表的子分区，而不是整个分区，如下所示：
mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2; ERROR 1734 (HY000): Subpartitioned table, use subpartition instead of partition
MySQL使用的表结构的比较是非常严格的。分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。另外，两个表必须使用相同 的存储引擎：
mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
Table: es3
Create Table: CREATE TABLE `es3` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)
mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL