Exchanging Partitions and Subpartitions with Tables--官方文

发布时间：2020-12-11 23:56:12 所属栏目：MySql教程来源：网络整理

导读：原文地址：https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html In MySQL 5.6,it is possible to exchange a table partition or subpartition with a table using? pt EXCHANGE PARTITION? p ?WITH TABLE? nt ,where? pt ?is t

原文地址：https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

In MySQL 5.6,it is possible to exchange a table partition or subpartition with a table using?ptEXCHANGE PARTITION?p?WITH TABLE?nt,where?pt?is the partitioned table and?p?is the partition or subpartition of?pt?to be exchanged with unpartitioned table?nt,provided that the following statements are true:

Table?nt?is not itself partitioned.

Table?nt?is not a temporary table.

The structures of tables?pt?and?nt?are otherwise identical.

Table?

?contains no foreign key references,and no other table has any foreign keys that refer to?.

There are no rows in?nt?that lie outside the boundaries of the partition definition for?p.

In addition to the?,?,and??privileges usually required for??statements,you must have the??privilege to perform?.

You should also be aware of the following effects of?:

Executing??does not invoke any triggers on either the partitioned table or the table to be exchanged.

Any?

?columns in the exchanged table are reset.


The??keyword has no effect when used with?.

The complete syntax of the??statement is shown here,where?pt?is the partitioned table,?p?is the partition or subpartition to be exchanged,and?nt?is the nonpartitioned table to be exchanged with?p:

pt 
    EXCHANGE PARTITION p 
    WITH TABLE nt;

One and only one partition or subpartition may be exchanged with one and only one nonpartitioned table in a single??statement. To exchange multiple partitions or subpartitions,use multiple??statements.??may not be combined with other??options. The partitioning and (if applicable) subpartitioning used by the partitioned table may be of any type or types supported in MySQL 5.6.



Exchanging a Partition with a Nonpartitioned Table
Suppose that a partitioned table??has been created and populated using the following SQL statements:

INSERT INTO e VALUES

(1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");

Now we create a nonpartitioned copy of??named?. This can be done using the?mysql?client as shown here:

 CREATE TABLE e2 LIKE e;
Query OK,0 rows affected (1.34 sec)
mysql> <strong class="userinput">ALTER TABLE e2 REMOVE PARTITIONING;

Query OK,0 rows affected (0.90 sec)

Records: 0  Duplicates: 0  Warnings: 0

You can see which partitions in table??contain rows by querying the??table,like this:

 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.00 sec)


For partitioned??tables,the row count given in the??column of the?table is only an estimated value used in SQL optimization,and is not always exact.


To exchange partition??in table??with table?,you can use the??statement shown here:

 ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK,0 rows affected (0.28 sec)

More precisely,the statement just issued causes any rows found in the partition to be swapped with those found in the table. You can observe how this has happened by querying the??table,as before. The table row that was previously found in partition??is no longer present:

 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 |
+----------------+------------+
4 rows in set (0.00 sec)

If you query table?,you can see that the?

 SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

The table to be exchanged with the partition does not necessarily have to be empty. To demonstrate this,we first insert a new row into table?,making sure that this row is stored in partition??by choosing an??column value that is less than 50,and verifying this afterwards by querying the??table:

 INSERT INTO e VALUES (41,"Michael","Green");              
Query OK,1 row affected (0.05 sec)                                
mysql> <strong class="userinput">SELECT PARTITION_NAME,TABLE_ROWS

->     <strong class="userinput">FROM INFORMATION_SCHEMA.PARTITIONS

->     <strong class="userinput">WHERE TABLE_NAME = 'e';

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

| PARTITION_NAME | TABLE_ROWS |

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

| p0             |          1 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

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

4 rows in set (0.00 sec)          

Now we once again exchange partition??with table??using the same??statement as previously:

 ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK,0 rows affected (0.28 sec)

The output of the following queries shows that the table row that was stored in partition??and the table row that was stored in table?,prior to issuing the??statement,have now switched places:

 SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> <strong class="userinput">SELECT PARTITION_NAME,TABLE_ROWS

->     <strong class="userinput">FROM INFORMATION_SCHEMA.PARTITIONS

->     <strong class="userinput">WHERE TABLE_NAME = 'e';

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

| PARTITION_NAME | TABLE_ROWS |

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

| p0             |          1 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

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

4 rows in set (0.00 sec)
mysql> <strong class="userinput">SELECT * FROM e2;

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

| id | fname   | lname |

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

| 41 | Michael | Green |

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

1 row in set (0.00 sec)


Non-Matching Rows
You should keep in mind that any rows found in the nonpartitioned table prior to issuing the??statement must meet the conditions required for them to be stored in the target partition; otherwise,the statement fails. To see how this occurs,first insert a row into??that is outside the boundaries of the partition definition for partition??of table?. For example,insert a row with an??column value that is too large; then,try to exchange the table with the partition again:

 INSERT INTO e2 VALUES (51,"Ellen","McDonald");
Query OK,1 row affected (0.08 sec)
mysql> <strong class="userinput">ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

<span class="errortext">ERROR 1707 (HY000): Found row that does not match the partition


The??keyword is accepted,but has no effect when used with?,as shown here:

 ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;


Exchanging a Subpartition with a Nonpartitioned Table
You can also exchange a subpartition of a subpartitioned table (see?) with a nonpartitioned table using an??statement. In the following example,we first create a table??that is partitioned by??and subpartitioned by?,populate this table as we did table?,and then create an empty,nonpartitioned copy??of the table,as shown here:

 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 (2.76 sec)
mysql> <strong class="userinput">INSERT INTO es VALUES

->     <strong class="userinput">(1669,

->     <strong class="userinput">(337,

->     <strong class="userinput">(16,

->     <strong class="userinput">(2005,"Black");

Query OK,4 rows affected (0.04 sec)

Records: 4  Duplicates: 0  Warnings: 0
mysql> <strong class="userinput">CREATE TABLE es2 LIKE es;

Query OK,0 rows affected (1.27 sec)
mysql> <strong class="userinput">ALTER TABLE es2 REMOVE PARTITIONING;

Query OK,0 rows affected (0.70 sec)

Records: 0  Duplicates: 0  Warnings: 0

Although we did not explicitly name any of the subpartitions when creating table?,we can obtain generated names for these by including the??of the??table from?when selecting from that table,as shown here:

 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)

The following??statement exchanges subpartition??table??with the nonpartitioned table?:

 ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK,0 rows affected (0.29 sec)

You can verify that the rows were exchanged by issuing the following queries:
 SELECT PARTITION_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.00 sec)
mysql> <strong class="userinput">SELECT * FROM es2;

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

| id   | fname | lname |

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

| 1669 | Jim   | Smith |

|  337 | Mary  | Jones |

| 2005 | Linda | Black |

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

3 rows in set (0.00 sec)

If a table is subpartitioned,you can exchange only a subpartition of the table—not an entire partition—with an unpartitioned table,as shown here:

 ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;

The comparison of table structures used by MySQL is very strict. The number,order,names,and types of columns and indexes of the partitioned table and the nonpartitioned table must match exactly. In addition,both tables must use the same storage engine:

 CREATE TABLE es3 LIKE e;
Query OK,0 rows affected (1.31 sec)
mysql> <strong class="userinput">ALTER TABLE es3 REMOVE PARTITIONING;

Query OK,0 rows affected (0.53 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql> <strong class="userinput">SHOW CREATE TABLE es3G

 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=latin1

1 row in set (0.00 sec)
mysql> <strong class="userinput">ALTER TABLE es3 ENGINE = MyISAM;

Query OK,0 rows affected (0.15 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql> <strong class="userinput">ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;

<span class="errortext">ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!