在 MySQL 5.6 中,可以使用 将表分区或子分区与表交换,其中
是分区表,
是要与未分区表交换的分区或子
分区,前提是以下语句为真:
ALTER
TABLE
pt
EXCHANGE PARTITION
p
WITH TABLE
nt
pt
p
pt
nt
表
nt
本身没有分区。表
nt
不是临时表。pt
表的 结构nt
在其他方面是相同的。表
nt
不包含外键引用,并且没有其他表具有引用 的任何外键nt
。中没有行
nt
位于 的分区定义边界之外p
。两个表必须使用相同的字符集和排序规则。
对于
InnoDB
表,两个表必须使用相同的行格式。要确定InnoDB
表的行格式,请查询INFORMATION_SCHEMA.INNODB_SYS_TABLES
.的任何分区级
MAX_ROWS
设置p
都必须与为MAX_ROWS
设置的表级值 相同nt
。的任何分区级MIN_ROWS
设置的设置p
也必须与为 设置的任何表级MIN_ROWS
值 相同nt
。这在任何一种情况下都是正确的,无论是否
pt
有一个明确的表级MAX_ROWS
或MIN_ROWS
选项有效。两个表和 之间 的
AVG_ROW_LENGTH
不能不同。pt
nt
pt
没有任何使用该DATA DIRECTORY
选项的分区。INDEX DIRECTORY
表和要与之交换的分区之间不能不同。TABLESPACE
任何一个表中都不能使用 表或分区选项。
除了语句通常需要的 、 和 权限ALTER
外
INSERT
,
您还必须具有
执行
.
CREATE
ALTER TABLE
DROP
ALTER TABLE ...
EXCHANGE PARTITION
您还应该注意以下影响
ALTER TABLE ...
EXCHANGE PARTITION
:
执行
ALTER TABLE ... EXCHANGE PARTITION
不会在分区表或要交换的表上调用任何触发器。交换表中的任何
AUTO_INCREMENT
列都被重置。关键字与 一起使用
IGNORE
时无效ALTER TABLE ... EXCHANGE PARTITION
。
语句的完整语法
ALTER TABLE ...
EXCHANGE PARTITION
如下所示,其中
pt
是分区表,
p
是要交换的分区或子分区,是要交换nt
的非分区表
p
:
ALTER TABLE pt
EXCHANGE PARTITION p
WITH TABLE nt;
一个且只有一个分区或子分区可以在单个
ALTER TABLE
EXCHANGE PARTITION
语句中与一个且只有一个非分区表交换。要交换多个分区或子分区,请使用多个
ALTER TABLE
EXCHANGE PARTITION
语句。EXCHANGE
PARTITION
不得与其他
ALTER TABLE
选项结合使用。分区表使用的分区和(如果适用)子分区可以是 MySQL 5.6 支持的任何类型。
与非分区表交换分区
假设e
已使用以下 SQL 语句创建并填充分区表:
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
e
现在我们创建一个named
的非分区副本e2
。这可以使用
mysql客户端来完成,如下所示:
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
您可以通过查询表来查看表中哪些分区e
包含行
INFORMATION_SCHEMA.PARTITIONS
,如下所示:
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.00 sec)
对于分区表,表列中
InnoDB
给出的行数
只是 SQL 优化中使用的估计值,并不总是准确的。
TABLE_ROWS
INFORMATION_SCHEMA.PARTITIONS
p0
要将表中
的
分区e
与表交换e2
,您可以使用
ALTER
TABLE
此处显示的语句:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)
更准确地说,刚刚发出的语句导致分区中找到的任何行与表中找到的行交换。INFORMATION_SCHEMA.PARTITIONS
您可以像以前一样通过查询表来观察这是如何发生的
。之前在分区p0
中找到的表行不再存在:
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 |
+----------------+------------+
4 rows in set (0.00 sec)
如果你查询表e2
,你可以看到
现在可以在那里找到
“丢失”的行:
mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
要与分区交换的表不一定非得是空的。为了演示这一点,我们首先向表中插入一个新行,通过选择一个小于 50 的列值
e
确保该行存储在分区中,然后通过查询表来验证这一点:
p0
id
PARTITIONS
mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)
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.00 sec)
现在我们再次使用与之前相同的
语句将
分区p0
与表交换:e2
ALTER
TABLE
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)
以下查询的输出显示存储在 partitionp0
中的表行和存储在 table 中的表行,e2
在发出
ALTER
TABLE
语句之前,现在已经交换了位置:
mysql> 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> 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)
mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)
不匹配的行
您应该记住,在发出
ALTER TABLE ...
EXCHANGE PARTITION
语句之前在非分区表中找到的任何行都必须满足将它们存储在目标分区中所需的条件;否则,声明失败。要查看这是如何发生的,首先将一行插入表e2
的分区定义边界之外的
行。例如,插入列值过大的行;然后,再次尝试将表与分区交换:
p0
e
id
mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition
关键字被接受,但与IGNORE
一起使用时无效EXCHANGE PARTITION
,如下所示:
mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition
与非分区表交换子分区
您还可以使用语句将子分区表的子分区(请参阅第 19.2.6 节,“子分区”)与非
分区表交换ALTER TABLE ...
EXCHANGE PARTITION
。在以下示例中,我们首先创建一个es
由 分区RANGE
和子分区的
KEY
表,像对表 一样填充此表
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 (2.76 sec)
mysql> INSERT INTO es VALUES
-> (1669, "Jim", "Smith"),
-> (337, "Mary", "Jones"),
-> (16, "Frank", "White"),
-> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
虽然我们在创建表时没有明确命名任何子分区,但我们可以通过在从该
表中
进行选择时es
包含表的 来获取这些子分区的生成名称
,如下所示:
SUBPARTITION_NAME
PARTITIONS
INFORMATION_SCHEMA
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.29 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.00 sec)
mysql> SELECT * FROM es2;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)
如果一个表被子分区,你只能用一个未分区的表交换表的一个子分区——而不是整个分区,如下所示:
mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition
MySQL使用的表结构比较非常严格。分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。此外,两个表必须使用相同的存储引擎:
mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)
mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 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=latin1
1 row in set (0.00 sec)
mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 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