在MySQL 5.7中,可以用using交换表分区或子分区ALTER TABLE
,在那里pt
交换分区p
与表nt
pt
分区表和p
是分区还是子分区pt
与未分区表交换nt
,但必须符合下列陈述:
表格
nt
本身没有分区。表格
nt
不是临时表。表的结构
pt
而且nt
在其他方面是相同的。表格
nt
不包含外键引用,且其他表也没有引用的任何外键nt
.里面没有排
nt
它在分区定义的边界之外p
.此条件不适用于没有验证
选项。的({|不}验证)
选项在MySQL 5.7.5中添加。两个表必须使用相同的字符集和排序规则。
为
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
没有任何分区使用数据目录
选择。取消此限制InnoDB
MySQL 5.7.25及以后版本的表。索引目录
表和要与其交换的分区之间不能有差异。没有表或分区
表空间
选项可以在任意一个表中使用。
除了改变
,插入
,创建
通常需要的特权ALTER TABLE
语句,你必须有下降
表演的特权修改表…交换分区
.
您还应该注意以下影响修改表…交换分区
:
执行
修改表…交换分区
不调用分区表或要交换的表上的任何触发器。任何
AUTO_INCREMENT
交换表中的列被重置。的
忽略
关键字在与修改表…交换分区
.
的语法修改表…交换分区
语句显示在这里,其中pt
是分区表,p
是分区或子分区要交换,和nt
是否要与非分区表进行交换p
:
ALTER TABLEpt交换分区p与表nt;
可选地,您可以添加与验证
或没有验证
条款。当没有验证
,则修改表…交换分区
在将分区与非分区表交换时,操作不执行逐行验证,从而允许数据库管理员负责确保行在分区定义的边界内。与验证
是默认行为,不需要显式指定。的({|不}验证)
选项在MySQL 5.7.5中添加。
一个且仅一个分区或子分区可以与单个分区中的一个且仅一个非分区表交换更改表交换分区
声明。若要交换多个分区或子分区,请使用multiple更改表交换分区
语句。交换分区
可不可以和其他结合ALTER TABLE
选项。分区表使用的分区和(如果适用)子分区可以是MySQL 5.7中支持的任何类型。
用非分区表交换分区
假设有一个分区表e
已经使用以下SQL语句创建并填充:
CREATE TABLE e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30))分区范围(id)(分区p0值小于(50),分区p1值小于(100),分区p2值小于(150),分区p3值小于(MAXVALUE));插入e值(1669年,“吉姆”,“史密斯”),(337年,“玛丽”,“琼斯”),(16,“弗兰克”,“白”),(2005年,“琳达”,“黑”);
的非分区副本e
命名e2
.可以使用mysql客户端如下所示:
CREATE TABLE 2 LIKE e;查询OK, 0 rows affected (1.34 sec) mysql> ALTER TABLE e2 REMOVE partition;查询OK, 0 rows affected (0.90 sec) Records: 0 duplicate: 0 warning: 0
您可以看到表中的分区e
控件包含的行INFORMATION_SCHEMA。分区
表,像这样:
mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA。WHERE TABLE_NAME = 'e';+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
为分区InnoDB
表中给出的行数TABLE_ROWS
的列INFORMATION_SCHEMA。分区
表只是SQL优化中使用的估计值,并不总是准确的。
交换分区p0
在表e
与表e2
,你可以使用ALTER TABLE
声明如下:
修改表p2的分区查询OK, 0行受影响(0.28秒)
更准确地说,刚刚发出的语句将导致在分区中找到的任何行与在表中找到的行交换。方法可以观察这是如何发生的INFORMATION_SCHEMA。分区
表,和前面一样。以前在分区中找到的表行p0
不再存在的:
mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA。WHERE TABLE_NAME = 'e';+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 0 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
如果你有查询表e2
,你可以看到”失踪”Row现在可以在那里找到:
SELECT * FROM e2;+----+-------+-------+ | id |帧| lname | +----+-------+-------+ | 弗兰克16 | |白色 | +----+-------+-------+ 1行集(0.00秒)
要与分区交换的表不一定是空的。为了演示这一点,我们首先向表中插入一个新行e
,确保这一行存储在分区中p0
通过选择id
列值小于50,然后通过查询分区
表:
mysql> INSERT INTO e VALUES (41, "Michael", "Green");查询OK, 1 row affected (0.05 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA。WHERE TABLE_NAME = 'e';+----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p1 p0 | 1 | | | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4行集(0.00秒)
现在我们再一次交换分区p0
与表e2
使用相同的ALTER TABLE
如上所述:
修改表p2的分区查询OK, 0行受影响(0.28秒)
以下查询的输出显示了存储在分区中的表行p0
和存储在table中的表行e2
,在发出ALTER TABLE
语句,现在已经交换了位置:
mysql> SELECT * FROM;+------+-------+-------+ | id |帧| lname | +------+-------+-------+ | 16 |弗兰克。怀特| | 1669 | |吉姆史密斯| | 337 | |玛丽琼斯| | 2005 | |琳达|黑色 | +------+-------+-------+ 4行集(0.00秒)mysql >选择PARTITION_NAME,从INFORMATION_SCHEMA TABLE_ROWS - >。WHERE 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");查询OK, 1 row affected (0.08 sec) mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;错误1707 (HY000):发现与分区不匹配的行
只有没有验证
选项将允许此操作成功:
修改表2的分区为p0,不需要验证。查询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)(分区p0值小于(1000001),分区p1值小于(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;查询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。分区TABLE_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)
如果一个分区与一个包含与分区定义不匹配的行的表交换,则数据库管理员有责任修复不匹配的行,可以使用修理表
或修改表…修复分区
.
用非分区表交换子分区
还可以交换子分区表的子分区(参见第22.2.6节,“子分区”的非分区表修改表…交换分区
声明。在下面的例子中,我们首先创建一个表西文
除以范围
再除以关键
,像我们做表一样填充这个表e
,然后创建一个空的、未分区的副本es2
表的,如下所示:
mysql> CREATE TABLE es (-> id INT NOT NULL, -> fname 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) ->);查询OK, 0行影响(2.76秒)mysql> INSERT INTO es VALUES -> (1669, "Jim", "Smith"), -> (337, "Mary", "Jones"), -> (16, "Frank", "White"), -> (2005, "Linda", "Black");查询OK, 4 rows affected (0.04 sec) Records: 4 duplicate: 0 Warnings: 0 mysql> CREATE TABLE es2 LIKE es;查询OK, 0 rows affected (1.27 sec) mysql> ALTER TABLE es2 REMOVE partition;查询OK, 0 rows affected (0.70 sec) Records: 0 duplicate: 0 warning: 0
尽管我们在创建表时没有显式地命名任何子分区西文
,我们可以通过包含SUBPARTITION_NAME
的分区
表INFORMATION_SCHEMA
当从表中进行选择时,如下所示:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA。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行集(0.00秒)
以下ALTER TABLE
语句交换子分区p3sp0
表格西文
使用非分区表es2
:
修改表分区p3sp0和表es2;查询OK, 0行受影响(0.29秒)
您可以通过发出以下查询来验证行是否交换了:
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA。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行集(0.00秒)从es2 mysql > SELECT *;+------+-------+-------+ | id |帧| lname | +------+-------+-------+ | 玛丽1669年吉姆史密斯| | | | 337 | |琼斯| | 2005 |琳达|黑色 | +------+-------+-------+ 3行集(0.00秒)
如果一个表是子分区的,你只能用一个未分区的表交换该表的一个子分区,而不是整个分区,如下所示:
修改表3的分区错误1704 (HY000): Subpartitioned table,使用subpartition代替partition
MySQL使用的表结构的比较是非常严格的。分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。另外,两个表必须使用相同的存储引擎:
CREATE TABLE es3 LIKE e;查询OK, 0 rows affected (1.31 sec) mysql> ALTER TABLE es3 REMOVE partition;查询OK, 0 rows affected (0.53 sec) Records: 0 duplicate: 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;查询OK, 0 rows affected (0.15 sec) Records: 0 duplicate: 0 Warnings: 0 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;错误1497 (HY000):在这个版本的MySQL中,不允许在分区中混合处理程序