MySQL 5.7 用非分区表交换子分区

用非分区表交换子分区
你也可以执行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