在MySQL 8.0中,可以用using来交换一个表分区或子分区ALTER TABLE
,在那里pt
交换分区p
与表nt
pt
分区表和p
分区还是子分区pt
与未分区表交换nt
,只要下列陈述成立:
表格
nt
本身没有分区。表格
nt
不是临时表。表的结构
pt
而且nt
在其他方面是相同的。表格
nt
不包含外键引用,且没有其他表具有引用的外键nt
.里面没有行
nt
在划分定义的边界之外p
.此条件不适用于没有验证
使用。为
InnoDB
表,两个表使用相同的行格式。的行格式InnoDB
表,查询INFORMATION_SCHEMA。INNODB_TABLES
.nt
没有任何分区使用数据目录
选择。这一限制已被取消InnoDB
表在MySQL 8.0.14及以后版本。
除了改变
,插入
,创建
通常需要的特权ALTER TABLE
陈述,你必须有下降
表演特权修改表…交换分区
.
您还应该注意的影响修改表…交换分区
:
执行
修改表…交换分区
不对分区表或要交换的表调用任何触发器。任何
AUTO_INCREMENT
交换表中的列将被重置。的
忽略
与使用时,关键字没有效果修改表…交换分区
.
的语法修改表…交换分区
在这里,在哪里pt
是分区表,p
分区(或子分区)是否要交换nt
要与非分区表交换吗p
:
ALTER TABLEpt交换分区p与表nt;
可选地,您可以追加与验证
或没有验证
.当没有验证
,则修改表…交换分区
在将分区与非分区表交换时,操作不会执行任何逐行验证,从而允许数据库管理员负责确保行处于分区定义的边界内。与验证
是默认值。
一个分区或子分区可以与单个分区中的一个且仅一个非分区表交换修改表交换分区
声明。要交换多个分区或子分区,请使用multiple修改表交换分区
语句。交换分区
可不可以与其他结合ALTER TABLE
选项。分区表使用的分区和子分区(如果适用)可以是MySQL 8.0支持的任何类型。
用非分区表交换分区
假设有一个分区表e
已经使用以下SQL语句创建和填充:
CREATE TABLE e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)) PARTITION BY RANGE (id)(分区p0值小于(50),分区p1值小于(100),分区p2值小于(150),分区p3值小于(MAXVALUE));插入e值(1669年,“吉姆”,“史密斯”),(337年,“玛丽”,“琼斯”),(16,“弗兰克”,“白”),(2005年,“琳达”,“黑”);
的非分区副本e
命名e2
.可以使用mysql客户端如下所示:
CREATE TABLE e2 LIKE e;mysql> ALTER TABLE e2 REMOVE partition;查询OK, 0行受影响(0.07秒)记录:0重复:0警告:0
您可以在表中看到哪些分区e
控件包含行INFORMATION_SCHEMA。分区
表,像这样:
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMATABLE_NAME = 'e'的分区;+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 2行集(0.00秒)
为分区InnoDB
表中给出的行数TABLE_ROWS
的列INFORMATION_SCHEMA。分区
表只是用于SQL优化的估计值,并不总是准确的。
交换分区p0
在表e
与表e2
,你可以使用ALTER TABLE
,如图所示:
mysql> ALTER TABLE e PARTITION p0 WITH TABLE e2查询OK, 0行受影响(0.04秒)
更准确地说,刚刚发出的语句导致在分区中找到的任何行与在表中找到的行交换。方法可以观察到这是如何发生的INFORMATION_SCHEMA。分区
和之前一样。先前在分区中找到的表行p0
不再存在:
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMATABLE_NAME = 'e'的分区;+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 0 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
如果你查询表e2
,你可以看到”失踪”Row现在可以在那里找到:
mysql> SELECT * FROM e2;+----+-------+-------+ | id |帧| lname | +----+-------+-------+ | 弗兰克16 | |白色 | +----+-------+-------+ 1行集(0.00秒)
要与分区交换的表不一定是空的。为了演示这一点,我们首先向表中插入一个新行e
,确保该行存储在分区中p0
通过选择id
列值小于50,然后通过查询分区
表:
mysql> INSERT INTO e VALUES (41, "Michael", "Green");mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMATABLE_NAME = 'e'的分区;+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
现在我们再次交换分区p0
与表e2
使用相同的ALTER TABLE
如上所述:
mysql> ALTER TABLE e PARTITION p0 WITH TABLE e2查询OK, 0行受影响(0.28秒)
以下查询的输出显示了存储在分区中的表行p0
以及存储在table中的表行e2
,在发出ALTER TABLE
声明,现在交换位置:
mysql> SELECT * FROM e;+------+-------+-------+ | id |帧| lname | +------+-------+-------+ | 16 |弗兰克。怀特| | 1669 | |吉姆史密斯| | 337 | |玛丽琼斯| | 2005 | |琳达|黑色 | +------+-------+-------+ 4行集(0.00秒)mysql >选择PARTITION_NAME TABLE_ROWS INFORMATION_SCHEMA。TABLE_NAME = 'e'的分区;+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)从e2 mysql > SELECT *;+----+---------+-------+ | id |帧| lname | +----+---------+-------+ | 迈克尔41 | |绿色 | +----+---------+-------+ 1行集(0.00秒)
Nonmatching行
命令之前在非分区表中找到的任何行都应该记住修改表…交换分区
语句必须满足将它们存储在目标分区中所需的条件;否则,语句执行失败。要了解这是如何发生的,首先将一行插入e2
这超出了分区定义的边界p0
的表e
.例如,插入一行id
列值太大;然后,尝试再次交换表与分区:
mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;错误1707 (HY000):发现与分区不匹配的行
只有没有验证
选项将允许此操作成功:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION查询OK, 0行受影响(0.02秒)
当分区与包含与分区定义不匹配的行的表交换时,数据库管理员有责任修复不匹配的行,可以使用修理表
或修改表…修复分区
.
不进行逐行验证的分区交换
在与有很多行的表交换分区时,为了避免耗时的验证,可以通过追加跳过逐行验证步骤没有验证
到修改表…交换分区
声明。
下面的示例比较了使用验证和不使用验证交换分区与非分区表时执行时间的差异。分区表(tablee
)包含两个分区,每个分区有100万行。表e的p0中的行被删除,p0与一个100万行的未分区表交换。的与验证
操作耗时0.74秒。相比之下,没有验证
操作耗时0.01秒。
Create table e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)) partition BY RANGE (id) (partition p0 VALUES小于(1000001),partition p1 VALUES小于(2000001),);SELECT COUNT(*) FROM e;| COUNT(*) | +----------+ | 2000000 | +----------+ 1 row in set (0.27 sec) #查看每个分区的行SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA。TABLE_NAME = 'e'的分区;+----------------+-------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+-------------+ | p1 p0 | 1000000 | | | 1000000 | +----------------+-------------+ 2行集(0.00秒)#创建一个分区表相同的结构和填充它与100万行创建表e2 (INT id非空、帧VARCHAR (30), lname VARCHAR (30));SELECT COUNT(*) FROM e2;+----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.24 sec) #创建另一个相同结构的非分区表,并填充100万行Create table e3 (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30));SELECT COUNT(*) FROM e3;+----------+ | 数 (*) | +----------+ | 1000000 | +----------+ 1行集(0.25秒)#行从p0表从e e mysql >删除id < 1000001;mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMATABLE_NAME = 'e'的分区;+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 0 | | | 1000000 | +----------------+------------+ 2行集(0.00秒)#交换分区表e的p0表e2与验证的mysql > ALTER table e与表交换分区p0 e2与验证; Query OK, 0 rows affected (0.74 sec) # Confirm that the partition was exchanged with table e2 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) # Once again, drop the rows from p0 of table e mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) # Confirm that there are no rows in partition p0 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec) # Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION; Query OK, 0 rows affected (0.01 sec) # Confirm that the partition was exchanged with table e3 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ 2 rows in set (0.00 sec)
如果分区与包含与分区定义不匹配的行的表进行交换,则数据库管理员有责任修复不匹配的行,可以使用修理表
或修改表…修复分区
.
用非分区表交换子分区
还可以交换子分区表的子分区(参见第24.2.6节,“分区”的非分区表修改表…交换分区
声明。在下面的示例中,我们首先创建一个表西文
它被范围
再除以关键
,填充这个表,就像我们做表一样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) - b>);查询OK, 0行影响(2.76秒)mysql> INSERT INTO es VALUES ->(1669,“吉姆”,“史密斯”),->(337,“玛丽”,“琼斯”),->(16,“弗兰克”,“白”),->(2005,“琳达”,“黑”);查询OK, 4 rows affected (0.04 sec) Records: 4 duplicate: 0 warning: 0 mysql> CREATE TABLE es2 LIKE es;mysql> ALTER TABLE es2 REMOVE partition查询确定,0行受影响(0.70秒)记录:0重复:0警告:0
尽管我们在创建表时没有显式地命名任何子分区西文
,我们可以通过包含SUBPARTITION_NAME
的列分区
表INFORMATION_SCHEMA
当从表中选择时,如下所示:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMAWHERE 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行集(0.00秒)
以下ALTER TABLE
语句交换子分区p3sp0
在表西文
使用非分区表es2
:
mysql> ALTER TABLE es p3sp0 WITH TABLE es2查询OK, 0行受影响(0.29秒)
您可以通过发出以下查询来验证行是否被交换:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMAWHERE 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行集(0.00秒)从es2 mysql > SELECT *;+------+-------+-------+ | id |帧| lname | +------+-------+-------+ | 玛丽1669年吉姆史密斯| | | | 337 | |琼斯| | 2005 |琳达|黑色 | +------+-------+-------+ 3行集(0.00秒)
如果一个表是子分区的,你只能交换表的一个子分区,而不是整个分区,与一个未分区的表交换,如下所示:
3 .用TABLE es2替换分区错误1704 (HY000): Subpartitioned table,使用subpartition代替partition
表结构的比较是严格的;分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。另外,两个表必须使用相同的存储引擎:
mysql> CREATE TABLE es3 LIKE emysql> ALTER TABLE es3 REMOVE partition查询OK, 0 rows affected(0.53秒)Records: 0 duplicate: 0 warning: 0 mysql> SHOW CREATE TABLE es3\G ***************************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.00 sec) mysql> ALTER Table es3 ENGINE= MyISAM;查询OK, 0 rows affected(0.15秒)Records: 0 duplicate: 0 warning: 0 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;错误1497 (HY000):在这个版本的MySQL中,分区中不允许混合处理程序