MySQL partition Exchange Exchange partition

Exchanging data between tables and partitions

mysql5.6 start supporting ALTER TABLE: Exchange partition syntax, which allows data in a partition or sub-partition to be exchanged with data in another non-partitioned table, which is equivalent to moving data from a partition to a non-partitioned table if the data in a non-partitioned table is empty. If the data in the partition table is empty, it is equivalent to importing data from the external table into the partition, that is, which side is not empty, which side is being moved out, which side is empty, and which side is loaded with data.

to use alter TABLE...EXCHANGE partition statement, the following conditions must be met:

A : The table to be exchanged needs to have the exact same table structure as the partitioned table, but the table to be exchanged cannot contain partitions

B : Data in a non-partitioned table must be within the partition definition of the interchange (that is, the definition scope for the upper partition column)

C : The exchange table cannot contain foreign keys, or other tables contain foreign key references to the table

D : Users need alter,insert,create in addition to permissions, you also need a drop permissions, in addition, there are two small details to note:

a : When using this statement, triggers on the interchange table and the swapped table are not triggered

b :auto_increment column will be reset

Example:

To create a partitioned table:

Mysql> CREATE TABLE E (

-ID int not NULL,

-fname varchar (+),

lname varchar (+))

- partition by range (ID) (

-partition P0 values less than (+),

-partition P1 values less than (+),

-partition P2 values less than (),

- partition P3 values less than maxvalue);

Query OK, 0 rows affected (1.20 sec)

Insert data:

Mysql> INSERT into e values (1669, ' Jim ', ' Smith '), (337, ' Mary ', ' Jones '), (+, ' Frank ', ' withe '), (2005, ' Linda ', ' Black ') );

Query OK, 4 rows affected (0.03 sec)

Records:4 duplicates:0 warnings:0

To create a non-partitioned table:

Mysql> CREATE table E2 like E;

Query OK, 0 rows affected (0.15 sec)

Mysql> ALTER TABLE E2 remove partitioning;

Query OK, 0 rows affected (0.30 sec)

records:0 duplicates:0 warnings:0

Use the following statement to observe the data in the partitioned table:

Mysql> Select Partition_name,table_rows from information_schema.partitions where table_name= ' e ';

+----------------+------------+

| Partition_name | Table_rows |

+----------------+------------+

|         P0 | 1 |

|         P1 | 0 |

|         P2 | 0 |

|         P3 | 3 |

+----------------+------------+

4 rows in Set (0.16 sec)

Use the following statement to move the data in the partition P0 of table E to the E2 table:

mysql> ALTER TABLE E exchange partition p0 with table E2;

Query OK, 0 rows affected (0.01 sec)

To view the number of data rows for the P0 partition again:

Mysql> Select Partition_name,table_rows from information_schema.partitions where table_name= ' e ';

+----------------+------------+

| Partition_name | Table_rows |

+----------------+------------+

|         P0 | 0 |

|         P1 | 0 |

|         P2 | 0 |

|         P3 | 3 |

+----------------+------------+

When you find that the number of data rows for the P0 partition is 0, view the data in the E2 table:

Mysql> SELECT * from E2;

+----+-------+-------+

| ID | fname | lname |

+----+-------+-------+

| 16 | Frank | withe |

+----+-------+-------+

1 row in Set (0.00 sec)

the data in the P0 partition is found to be transferred to the E2 table, and the interchange statement is executed again:

mysql> ALTER TABLE E exchange partition p0 with table E2;

Query OK, 0 rows affected (0.01 sec)

Mysql> SELECT * from E2;

Empty Set (0.00 sec)

Mysql> select * from E;

+------+-------+-------+

| ID | fname | lname |

+------+-------+-------+

| Frank | withe |

| 1669 | Jim | smith |

| 337 | Mary | Jones |

| 2005 | Linda | Black |

+------+-------+-------+

4 rows in Set (0.00 sec)

The Discovery Table E2 data is transferred to the P0 partition of partition table E, Note that when the data is transferred back, the select Partition_name,table_rows from Information_schema.partitions where table_name= ' e '; This sentence can not be detected in real time the change in the number of rows in the partition.

Note: This post refers to "InnoDB Technology insider InnoDB Storage Engine Second Edition"

MySQL partition Exchange Exchange partition