MySQL交换分区的实例详解

2024-02-17 11:16:05 146

MySQL交换分区的实例详解

前言

在介绍交换分区之前，我们先了解一下mysql分区。

数据库的分区有两种：水平分区和垂直分区。而MySQL暂时不支持垂直分区，因此接下来说的都是水平分区。水平分区即：以行为单位对表进行分区。比如：按照时间分区，每一年一个分区等。

在MySQL中，分区是可以交换的，可以将一个分区表中的一个分区和一个普通表中的数据互换。

交换分区的实现

1、交换分区的语法

altertableptexchangepartitionpwithtablent;

解释：

将分区表pt的分区p和一个普通表nt中的数据进行互换。

交换的前提条件：

普通表nt不为临时表，且不是分区表。
表结构和分区表pt的结构一致。
普通表nt没有外键引用。
普用表nt若为非空。在MySQL5.6以及之前的版本，必须在分区的范围内；在MySQL5.7之后则可以不再其分区范围内，也依然会成功存入该分区中。

2、实验交换分区

(1)创建一个分区表，并插入数据

#创建一个分区表e
CREATETABLEe(
idINTNOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30)
)
PARTITIONBYRANGE(id)(
PARTITIONp0VALUESLESSTHAN(50),
PARTITIONp1VALUESLESSTHAN(100),
PARTITIONp2VALUESLESSTHAN(150),
PARTITIONp3VALUESLESSTHAN(MAXVALUE)
);

#向分区表e中插入几条数据
INSERTINTOeVALUES
(1669,"Jim","Smith"),
(337,"Mary","Jones"),
(16,"Frank","White"),
(2005,"Linda","Black");

(2)创建一个和分区表e结构一致的普通表e2

#创建一个表e2
mysql>createtablee2likee;

#删除表e2的分区，使之成为一个普通表
mysql>altertablee2removepartitioning;

(3)查看表e的分区中各有多少行

mysql>selectPARTITION_NAME,TABLE_ROWS
->FROMINFORMATION_SCHEMA.PARTITIONS
->WHERETABLE_NAME='e';
+----------------+------------+
|PARTITION_NAME|TABLE_ROWS|
+----------------+------------+
|p0|1|
|p1|0|
|p2|0|
|p3|3|
+----------------+------------+
4rowsinset(0.00sec)

(4)将表e的分区p0交换到普通表e2中

mysql>altertableeexchangepartitionp0withtablee2;

(5)查看结果

#表e中分区P0的数据已经没有了
mysql>SELECTPARTITION_NAME,TABLE_ROWS
->FROMINFORMATION_SCHEMA.PARTITIONS
->WHERETABLE_NAME='e';
+----------------+------------+
|PARTITION_NAME|TABLE_ROWS|
+----------------+------------+
|p0|0|
|p1|0|
|p2|0|
|p3|3|
+----------------+------------+
4rowsinset(0.00sec)

#在表e2中有了一条来自表e的分区p0的数据
mysql>SELECT*FROMe2;
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|Frank|White|
+----+-------+-------+
1rowinset(0.00sec)

由此发现，表e的分区p0的数据搬移到了表e2中。

上面的例子，是将一个分区和一个普通的空表交换，这样相当于将一个分区的数据搬移了出去，通常使用在数据的归档中。

同样，交换分区也可以在一个分区和一个非空普通表中发生交换，这样普通表中的数据会搬迁到指定分区，该指定分区的数据会搬迁到普通表中。如下操作：

(6)再向分区表e的p0中添加一条数据

#在表e中添加一条在分区p0范围内的数据
mysql>INSERTINTOeVALUES(41,"Michael","Green");
QueryOK,1rowaffected(0.05sec)

#确认插入的数据确实以存放在分区p0
mysql>SELECTPARTITION_NAME,TABLE_ROWS
->FROMINFORMATION_SCHEMA.PARTITIONS
->WHERETABLE_NAME='e';
+----------------+------------+
|PARTITION_NAME|TABLE_ROWS|
+----------------+------------+
|p0|1|
|p1|0|
|p2|0|
|p3|3|
+----------------+------------+
4rowsinset(0.00sec)

(7)执行交换分区

mysql>altertableeexchangepartitionp0withtablee2;
QueryOK,0rowsaffected(0.28sec)

(8)查看结果

#之前把表e的p0交换到表e2的一条数据，现在又回到了表e
mysql>SELECT*FROMe;
+------+-------+-------+
|id|fname|lname|
+------+-------+-------+
|16|Frank|White|
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+-------+-------+
4rowsinset(0.00sec)

mysql>SELECT*FROMe2;
+----+---------+-------+
|id|fname|lname|
+----+---------+-------+
|41|Michael|Green|
+----+---------+-------+
1rowinset(0.00sec)

由此看出，表e的p0和表e2的数据做了互换！这种场景，可以在普通表数据搬移到分区表的某个分区中使用。

但是，有一点需要注意：

若普通表的数据不在要交换的分区范围内，上面交换分区的语法，不可以执行成功！请看下面的操作。

(9)在普通表e2中插入一条超出表e的p0范围的数据，执行上面的交换语句

mysql>INSERTINTOe2VALUES(51,"Ellen","McDonald");
QueryOK,1rowaffected(0.08sec)

mysql>altertableeexchangepartitionp0withtablee2;
ERROR1707(HY000):Foundrowthatdoesnotmatchthepartition

在MySQL5.6版本中，该问题无法解决；而在MySQL5.7.5版本中，增加了一个选项WITHOUTVALIDATION，可以解决上面的报错。操作如下：

mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2WITHOUTVALIDATION;
QueryOK,0rowsaffected(0.02sec)

查看结果：

#id为51的数据也存放在表e的p0中
mysql>select*fromepartition(p0);
+------+---------+----------+
|id|fname|lname|
+------+---------+----------+
|41|Michael|Green|
|51|Ellen|McDonald|
+------+---------+----------+

mysql>SELECT*FROMe2;
+----+---------+-------+
|id|fname|lname|
+----+---------+-------+
|16|Frank|White|
+----+---------+-------+
1rowinset(0.00sec)

总结

通过上述实践操作发现，MySQL的交换分区的功能，有两种适用场景：

分区表的某个分区和一个空的普通表交换，使得分区表的该分区的数据搬移出去。
分区表的某个分区和一个非空普通表交换，使得普通表的数据搬移到分区表的指定分区中。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

MySQL交换分区的实例详解

热门推荐

随机推荐