ALTER TABLE分区操作
与分区相关的子句ALTER TABLE
可与分区表一起使用,以进行重新分区,添加,删除,丢弃,导入,合并和拆分分区,以及执行分区维护。
只需在分区表上使用带有
partition_options
子句的子句,即可ALTER TABLE
根据定义的分区方案对表进行重新分区partition_options
。这一条款总是开始PARTITION BY
,并遵循相同的语法和其他规则适用于partition_options
为第CREATE TABLE
(更详细的信息,请参见第13.1.20,“CREATE TABLE语句”),也可以用来划分现有的表尚未分区。例如,考虑如下定义的(未分区)表:CREATE TABLE t1 ( id INT, year_col INT );通过
HASH
使用id
以下语句,可以使用将该列作为分区键将该表分区为8个分区:ALTER TABLE t1PARTITION BY HASH (id)PARTITIONS 8;MySQL支持带有的
ALGORITHM
选项[SUB]PARTITION BY[LINEAR] KEY
。ALGORITHM=1
在计算分区中行的位置时,导致服务器使用与MySQL 5.1相同的键哈希函数;ALGORITHM=2
表示服务器使用默认KEY
在MySQL 5.5及更高版本中为新分区表实现和使用的键哈希函数。(使用MySQL 5.5和更高版本中使用的键哈希函数创建的分区表不能被MySQL 5.1服务器使用。)不指定该选项与使用效果相同ALGORITHM=2
。此选项主要用于升级或降级时[LINEAR] KEY
MySQL 5.1和更高版本的MySQL之间的分区表,或用于创建由MySQL 5.5或更高版本的服务器分区KEY
或LINEAR KEY
在MySQL 5.5或更高版本的服务器上分区的表,这些表可以在MySQL 5.1服务器上使用。使用一条
ALTER TABLE ... PARTITION BY
语句产生的表必须遵循与使用一条语句创建的规则相同的规则CREATE TABLE ... PARTITION BY
。这包括控制表可能具有的任何唯一键(包括任何主键)与分区表达式中使用的一个或多个列之间的关系的规则,如“分区键,主键和唯一键”。CREATE TABLE ... PARTITION BY
指定分区数的规则也适用于ALTER TABLE ... PARTITION BY
。partition_definition
子句ALTER TABLE ADD PARTITION
支持与该语句具有相同名称的子句相同的选项CREATE TABLE
。(有关语法和描述,请参见“ CREATE TABLE语句”。)假设您已创建分区表,如下所示:CREATE TABLE t1 ( id INT, year_col INT )PARTITION BY RANGE (year_col) (PARTITION p0VALUES LESS THAN (1991),PARTITION p1VALUES LESS THAN (1995),PARTITION p2VALUES LESS THAN (1999) );您可以
p3
向该表添加新分区,以存储小于以下值的值2002
:ALTER TABLE t1ADD PARTITION (PARTITION p3VALUES LESS THAN (2002));DROP PARTITION
可用于删除一个或多个RANGE
或LIST
分区。该语句不能与HASH
或KEY
分区一起使用;而是使用COALESCE PARTITION
(请参阅本节后面的内容)。partition_names
列表中命名的已删除分区中存储的所有数据都将被丢弃。例如,给定t1
先前定义的表,您可以删除名为p0
和的分区,p1
如下所示:ALTER TABLE t1DROP PARTITION p0, p1;注意
DROP PARTITION
不适用于使用NDB
存储引擎的表。请参见“ RANGE和LIST分区的管理”和“ NDB群集的已知限制”。ADD PARTITION
并且DROP PARTITION
目前不支持IF[NOT] EXISTS
。在
DISCARD PARTITION ... TABLESPACE
和IMPORT PARTITION ... TABLESPACE
选项延长传输表空间功能个别InnoDB
表分区。每个InnoDB
表分区都有其自己的表空间文件(.ibd
file)。可移植表空间功能使将表空间从正在运行的MySQL服务器实例复制到另一个正在运行的实例,或在同一实例上执行还原变得容易。这两个选项均采用逗号分隔的一个或多个分区名称列表。例如:ALTER TABLE t1DISCARD PARTITION p2, p3TABLESPACE ;ALTER TABLE t1IMPORT PARTITION p2, p3TABLESPACE ;在分区表上运行时
DISCARD PARTITION ... TABLESPACE
,IMPORT PARTITION ... TABLESPACE
分区名称和子分区名称均被允许。指定分区名称后,将包括该分区的子分区。可移动表空间功能还支持复制或还原分区
InnoDB
表。有关更多信息,请参见“导入InnoDB表”。支持分区表的重命名。您可以使用间接重命名各个分区
ALTER TABLE ... REORGANIZE PARTITION
。但是,此操作将复制分区的数据。要从选定分区中删除行,请使用
TRUNCATE PARTITION
选项。此选项采用一个或多个以逗号分隔的分区名称的列表。考虑t1
以下语句创建的表:CREATE TABLE t1 ( id INT, year_col INT )PARTITION BY RANGE (year_col) (PARTITION p0VALUES LESS THAN (1991),PARTITION p1VALUES LESS THAN (1995),PARTITION p2VALUES LESS THAN (1999),PARTITION p3VALUES LESS THAN (2003),PARTITION p4VALUES LESS THAN (2007) );要从partition删除所有行
p0
,请使用以下语句:ALTER TABLE t1TRUNCATE PARTITION p0;刚刚显示的语句与以下
DELETE
语句具有相同的作用:DELETE FROM t1WHERE year_col < 1991;截断多个分区时,这些分区不必是连续的:这可以大大简化对分区表的删除操作,否则,
WHERE
如果使用DELETE
语句来完成,则需要非常复杂的条件。例如,此语句删除分区p1
和中的所有行p3
:ALTER TABLE t1TRUNCATE PARTITION p1, p3;等效
DELETE
语句如下所示:DELETE FROM t1WHERE (year_col >= 1991 AND year_col < 1995) OR (year_col >= 2003 AND year_col < 2007);如果使用
ALL
关键字代替分区名称列表,则该语句将作用于所有表分区。TRUNCATE PARTITION
仅删除行;它不会更改表本身或其任何分区的定义。要验证是否删除了行,请
INFORMATION_SCHEMA.PARTITIONS
使用诸如此类的查询来检查表:SELECT PARTITION_NAME, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1';COALESCE PARTITION
可以与被HASH
或KEY
减少分区数的表一起使用number
。假设您已创建表t2
,如下所示:CREATE TABLE t2 (name VARCHAR (30), started DATE )PARTITION BY HASH ( YEAR(started) )PARTITIONS 6;要将使用的分区数
t2
从6个减少到4个,请使用以下语句:ALTER TABLE t2COALESCE PARTITION 2;最后一个
number
分区中包含的数据将合并到其余分区中。在这种情况下,分区4和5将合并为前4个分区(编号分别为0、1、2和3的分区)。要更改分区表使用的部分但不是全部分区,可以使用
REORGANIZE PARTITION
。该语句可以几种方式使用:- 将一组分区合并为一个分区。这是通过在
partition_names
列表中命名几个分区并为提供单个定义来完成的partition_definition
。 - 将现有分区拆分为几个分区。通过命名单个分区
partition_names
并提供多个分区来实现此目的partition_definitions
。 - 更改使用定义的分区子集的范围
VALUES LESS THAN
或使用定义的分区子集的值列表VALUES IN
。
注意
对于没有明确命名的分区,MySQL的自动提供的默认名称
p0
,p1
,p2
,等等。关于子分区也是如此。有关
ALTER TABLE ... REORGANIZE PARTITION
语句的更多详细信息和示例,请参见“RANGE和LIST分区的管理”。- 将一组分区合并为一个分区。这是通过在
要将表分区或子分区与表交换,请使用以下
ALTER TABLE ... EXCHANGE PARTITION
语句-即,将分区或子分区中的所有现有行移至未分区的表,并将未分区表中的所有现有行移至表分区或子分区。有关用法信息和示例,请参见“用表交换分区和子分区”。
多个选项提供的分区维护和修复功能类似于通过诸如
CHECK TABLE
和等语句为非分区表实现的功能REPAIR TABLE
(分区表也支持该功能;有关更多信息,请参见“MySQL命令行工具”)。这些措施包括ANALYZE PARTITION
,CHECK PARTITION
,OPTIMIZE PARTITION
,REBUILD PARTITION
,和REPAIR PARTITION
。这些选项中的每个选项都包含一个partition_names
子句,该子句由一个或多个分区名称组成,并用逗号分隔。分区必须已经存在于目标表中。您也可以使用ALL
关键字代替partition_names
,在这种情况下,该语句将作用于所有表分区。有关更多信息和示例,请参见“分区维护”。InnoDB
当前不支持按分区优化;ALTER TABLE ... OPTIMIZE PARTITION
将导致整个表的重建和分析,并发出适当的警告。(缺陷号11751825,错误号42822)要变通解决此问题,请使用ALTER TABLE ... REBUILD PARTITION
和ALTER TABLE ... ANALYZE PARTITION
。在
ANALYZE PARTITION
,CHECK PARTITION
,OPTIMIZE PARTITION
,和REPAIR PARTITION
选项不支持未分区表。REMOVE PARTITIONING
使您能够删除表的分区,而不会影响表或其数据。该选项可以与其他ALTER TABLE
选项(例如用于添加,删除或重命名列或索引的选项)组合使用。- 将此
ENGINE
选项与一起ALTER TABLE
使用可更改表使用的存储引擎,而不会影响分区。目标存储引擎必须提供其自己的分区处理程序。只有InnoDB
和NDB
存储引擎具有本机分区处理程序;NDB
MySQL 8.0当前不支持。
这是可能的ALTER TABLE
语句包含一个PARTITION BY
或REMOVE PARTITIONING
子句中,除其他更改规格,但PARTITION BY
还是REMOVE PARTITIONING
子句必须最后任何其他规格后指定。
ADD PARTITION
,DROP PARTITION
,COALESCE PARTITION
,REORGANIZE PARTITION
,ANALYZE PARTITION
,CHECK PARTITION
,和REPAIR PARTITION
选项不能与其他单一更改规格组合ALTER TABLE
,因为选择刚上市就单个分区的行为。有关更多信息,请参见“ ALTER TABLE分区操作”。
只有以下任一选项的单个实例可以在给定的使用ALTER TABLE
声明:PARTITION BY
,ADD PARTITION
,DROP PARTITION
,TRUNCATE PARTITION
,EXCHANGE PARTITION
,REORGANIZE PARTITION
,或COALESCE PARTITION
,ANALYZE PARTITION
,CHECK PARTITION
,OPTIMIZE PARTITION
,REBUILD PARTITION
,REMOVE PARTITIONING
。
例如,以下两个语句无效:
ALTER TABLE t1ANALYZE PARTITION p1,ANALYZE PARTITION p2;ALTER TABLE t1ANALYZE PARTITION p1,CHECK PARTITION p2;
在第一种情况下,您可以使用带有单个选项的单个语句同时分析分区p1
和p2
表t1
,该ANALYZE PARTITION
选项列出了两个要分析的分区,如下所示:
ALTER TABLE t1ANALYZE PARTITION p1, p2;
在第二种情况下,不可能同时对同一表的不同分区执行ANALYZE
和CHECK
操作。相反,您必须发出两个单独的语句,如下所示:
ALTER TABLE t1ANALYZE PARTITION p1;ALTER TABLE t1CHECK PARTITION p2;
REBUILD
子分区当前不支持该操作。REBUILD
明确地不允许关键字带有子分区,ALTER TABLE
如果使用该关键字,则会导致失败并显示错误。
CHECK PARTITION
REPAIR PARTITION
当要检查或修复的分区包含任何重复的键错误时,操作将失败。
有关这些语句的更多信息,请参见“分区维护”。