在MySQL 8.0中,可以使用表格交换表分区或子组分改变表
,在那里Pt.
交换分区P.
与表nt
Pt.
分区表和P.
是分区或分区Pt.
与未分区表交换nt
,惟下列陈述属实:
表格
nt
本身并不是分区。表格
nt
不是临时表。表格的结构
Pt.
和nt
否则是相同的。表格
nt
不包含外键引用,并且其他表没有引用任何外键nt
.没有行
nt
躺在分区定义的边界之外P.
.此条件不适用于if没有验证
使用。为了
Innodb.
表格,两个表都使用相同的行格式。确定行格式的Innodb.
表,查询INFORMATION_SCHEMA。INNODB_TABLES
.nt
没有任何分区使用数据目录
选择。这项限制取消了Innodb.
MySQL 8.0.14及以后版本中的表。
除了改变
那插入
,创造
通常需要的特权改变表
声明,你必须有下降
特权表现ALTER TABLE……交换分区
.
您还应该注意。的以下影响ALTER TABLE……交换分区
:
执行
ALTER TABLE……交换分区
不会在分区表或要交换的表格上调用任何触发器。任何
AUTO_INCREMENT
复位交换表中的列。这
忽略
关键字与时不起作用ALTER TABLE……交换分区
.
语法ALTER TABLE……交换分区
显示在这里,在哪里Pt.
是已分区的表,P.
要交换的分区(或子分区)和nt
是否要交换的非分区表P.
:
改变表Pt.交换分区P.与表nt;
可选地,您可以附加与验证
或没有验证
.什么时候没有验证
是指定的,ALTER TABLE……交换分区
当将分区交换为非分区表时,操作不执行任何逐行验证,这允许数据库管理员负责确保行在分区定义的边界内。与验证
是默认的。
单个分区中只能有一个分区或子分区与一个非分区表交换Alter table exchange partition
声明。若要交换多个分区或子分区,请使用多个Alter table exchange partition
语句。交换分区
可不可以与其他结合改变表
选项。分区表使用的划分和(如果适用)子分组可以是MySQL 8.0中支持的任何类型或类型。
与非分区表交换分区
假设一个分区表E.
已使用以下SQL语句创建并填充:
2、创建一个分区(PARTITION p0 VALUES < 50, PARTITION p1 VALUES < 100, PARTITION p2 VALUES < 150, PARTITION p3 VALUES < MAXVALUE);插入e值(1669年,“吉姆”,“史密斯”),(337年,“玛丽”,“琼斯”),(16,“弗兰克”,“白”),(2005年,“琳达”,“黑”);
现在我们创建了一个非标准的副本E.
命名E2.
.这可以使用mysql.客户端如下图所示:
mysql> CREATE TABLE e2 LIKE e;mysql> ALTER TABLE e2 REMOVE PARTITIONING;查询OK, 0行影响(0.07秒)记录:0重复:0警告:0
您可以看到表中的哪些分区E.
通过查询INFORMATION_SCHEMA。分区
表,如下:
mysql>选择partition_name,from Information_schema.partition的table_rows.partition,其中table_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.
, 您可以使用改变表
,如下所示:
MySQL>更改表E Exchange分区P0,具有表E2;查询OK,影响0行(0.04秒)
更准确地说,刚刚发出的语句将导致在分区中找到的任何行与在表中找到的行交换。通过查询INFORMATION_SCHEMA。分区
表,像以前一样。以前在分区中找到的表行p0
不再存在:
mysql>选择partition_name,from Information_schema.partition的table_rows.partition,其中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 |帧| 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_SCHEMA。分区表中的TABLE_NAME = 'e';+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
现在我们再次交换分区p0
与表E2.
使用相同的改变表
原句如前所述:
MySQL>更改表E Exchange分区P0,具有表E2;查询OK,影响0行(0.28秒)
以下查询的输出显示了存储在分区中的表行p0
和存储在表中的表行E2.
,方可签发改变表
声明,现在调换了位置:
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行
您应该记住,在发出之前在非分区表中找到的任何行ALTER TABLE……交换分区
语句必须满足将它们存储在目标分区中的条件;否则,语句将失败。要了解这是如何发生的,首先插入一行到E2.
这超出了分区定义的界限p0
的表E.
.例如,插入一行id
列值过大;然后,尝试再次与分区交换表:
MySQL>插入E2值(51,“Ellen”,“McDonald”);查询OK,1行受影响(0.08秒)MySQL> Alter表E Exchange分区P0与表E2;错误1707(hy000):找到与分区不匹配的行
只有没有验证
选项将允许这一操作成功:
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;查询OK,受影响0行(0.02秒)
当与包含与分区定义不匹配的行的表交换分区时,数据库管理员有责任修复无匹配的行,可以使用修复表
或ALTER TABLE……修复分区
.
没有逐行验证就交换分区
为避免使用具有许多行的表交换分区时耗时验证,可以通过附加跳过行逐行验证步骤没有验证
到ALTER TABLE……交换分区
声明。
以下示例比较使用非分子表交换分区时的执行时间之间的差异,其中没有验证。分区表(表E.
)包含两个分区,每个分区有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 LESS THAN (1000001), partition p1 VALUES LESS THAN (2000001),);mysql> SELECT COUNT(*) FROM e;| COUNT(*) | +----------+ | 2000000 | +----------+ 1 row in set (0.27 sec) #查看每个分区的行分区表中的TABLE_NAME = 'e';+----------------+-------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+-------------+ | p1 p0 | 1000000 | | | 1000000 | +----------------+-------------+ 2行集(0.00秒)#创建一个分区表相同的结构和填充它100万行创建表e2 (INT id非空、帧VARCHAR (30),lname VARCHAR (30));mysql> 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));mysql> SELECT COUNT(*) FROM e3;+----------+ | 数 (*) | +----------+ | 1000000 | +----------+ 1行集(0.25秒)#行从p0表从e e mysql >删除id < 1000001;mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA. db . db . db . db . db . db . db . db . db . db . db。分区表中的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 e2 'WITH VALIDATION' mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION; 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)
如果一个分区与包含不匹配分区定义的行的表交换,则数据库管理员有责任修复不匹配的行,可以使用修复表
或ALTER TABLE……修复分区
.
与非分区表交换子分区
您还可以交换一个子分区表的子分区(参见部分24.2.6,“Subpartitioning”),使用ALTER TABLE……交换分区
声明。在下面的例子中,我们首先创建一个表es.
除以范围
并坐下来钥匙
当我们做的表时,填充此表E.
,然后创建一个空的、无分区的副本es2
桌面,如下所示:
mysql >创建表es (- > id INT NOT NULL, >帧VARCHAR (30) - - - > lname VARCHAR (30 ) -> ) -> 分区范围(id) - > SUBPARTITION通过键(lname) - > SUBPARTITIONS 2(- >分区p0值小于(50),- - - - - - >分区p1值小于(100)- >分区p2值小于(150)- >分区p3值小于(执行)- >);mysql> INSERT INTO es VALUES -> (1669, "Jim", "Smith"), -> (337, "Mary", "Jones"), -> (16, "Frank", "White"), -> (2005, "Linda", "Black");查询OK, 4行影响(0.04 sec)记录:4重复:0警告:0 mysql> CREATE TABLE es2 LIKE es;mysql> ALTER TABLE es2 REMOVE PARTITIONING;查询OK, 0行影响(0.70秒)记录:0重复:0警告:0
尽管我们在创建表时没有显式地命名任何子分区es.
,我们可以通过包括包括这些所生成的名称SUBPARTITION_NAME
栏目分区
表从INFORMATION_SCHEMA
当从该表中进行选择时,如下所示:
mysql> select partition_name,子partition_name,table_rows - > from Information_schema.partitions - >其中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秒)
以下改变表
语句交流subpartitionP3SP0.
在表格中es.
对于非分区表es2
:
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;查询OK,受影响0行(0.29秒)
您可以通过发出以下查询来验证行是否交换:
mysql> select partition_name,子partition_name,table_rows - > from Information_schema.partitions - >其中table_name ='es';+ --------------- + -----------------------------+ |partition_name |子partition_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)
如果一个表被分区了,那么您只能交换这个表的一个子分区-不是整个分区-和一个未分区的表,如下所示:
ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition
表结构以严格的方式进行比较;已分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。另外,两个表必须使用相同的存储引擎:
mysql> CREATE TABLE es3 LIKE e;mysql> ALTER TABLE es3 REMOVE PARTITIONING;查询OK, 0行影响(0.53秒)记录:0重复:0警告:0 mysql> SHOW CREATE TABLE es3\G *************************** 1。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;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