与分区相关的子句 forALTER
TABLE
可以与分区表一起使用,用于重新分区、添加、删除、丢弃、导入、合并和拆分分区,以及执行分区维护。
只需在分区表上 使用
partition_options
with 子句即可根据. 该子句始终以 , 开头,并遵循适用于子句 for 的相同语法和其他规则 (有关更多详细信息,请参阅第 13.1.20 节,“CREATE TABLE 语句”),并且还可以用于对现有表进行分区尚未分区。例如,考虑如下所示定义的(未分区)表:ALTER TABLE
partition_options
PARTITION BY
partition_options
CREATE TABLE
CREATE TABLE t1 ( id INT, year_col INT );
这个表可以被分区
HASH
,使用id
列作为分区键,通过这个语句分成8个分区:ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8;
MySQL 支持
ALGORITHM
带有[SUB]PARTITION BY [LINEAR] KEY
.ALGORITHM=1
使服务器在计算行在分区中的位置时使用与 MySQL 5.1 相同的密钥散列函数;ALGORITHM=2
意味着服务器采用 key-hashing 函数,默认情况下用于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
。这包括管理表可能具有的任何唯一键(包括任何主键)与分区表达式中使用的一个或多个列之间关系的规则,如 第 24.6.1 节“分区键、主键和唯一键”。指定分区数的CREATE TABLE ... PARTITION BY
规则也适用于ALTER TABLE ... PARTITION BY
.partition_definition
for 子句支持与语句 的ALTER TABLE ADD PARTITION
同名子句相同的选项CREATE TABLE
。(有关语法和描述,请参阅 第 13.1.20 节,“CREATE TABLE 语句”。)假设您创建了如下所示的分区表:CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) );
您可以向该表添加一个新分区
p3
,用于存储小于以下值的值2002
:ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
DROP PARTITION
可用于删除一个或多个RANGE
分区LIST
。该语句不能与HASH
分区KEY
一起使用;相反,请使用COALESCE PARTITION
(请参阅本节后面部分)。存储在partition_names
列表中命名的已删除分区中的所有数据都将被丢弃。例如,给定之前定义的表 ,您可以删除名为和t1
的分区,如下所示:p0
p1
ALTER TABLE t1 DROP PARTITION p0, p1;
笔记DROP PARTITION
不适用于使用NDB
存储引擎的表。请参阅 第 24.3.1 节,“RANGE 和 LIST 分区的管理”和 第 23.2.7 节,“NDB Cluster 的已知限制”。ADD PARTITION
目前DROP PARTITION
不支持IF [NOT] EXISTS
.DISCARD PARTITION ... TABLESPACE
和IMPORT PARTITION ... TABLESPACE
选项将可 传输表空间功能扩展到单个InnoDB
表分区。每个InnoDB
表分区都有自己的表空间文件(.ibd
file)。Transportable Tablespace特性使得将表空间从一个正在运行的 MySQL 服务器实例复制到另一个正在运行的实例,或者在同一个实例上执行恢复变得容易。 这两个选项都采用一个或多个分区名称的逗号分隔列表。例如:ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
当在子分区表上运行 时
DISCARD PARTITION ... TABLESPACE
,IMPORT PARTITION ... TABLESPACE
分区和子分区名称都是允许的。指定分区名称时,将包括该分区的子分区。可 传输表空间功能还支持复制或恢复分区
InnoDB
表。有关详细信息,请参阅第 15.6.1.3 节,“导入 InnoDB 表”。支持分区表的重命名。您可以使用间接重命名单个分区
ALTER TABLE ... REORGANIZE PARTITION
;但是,此操作会复制分区的数据。要从选定分区中删除行,请使用该
TRUNCATE PARTITION
选项。此选项采用一个或多个以逗号分隔的分区名称的列表。考虑t1
此语句创建的表:CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2003), PARTITION p4 VALUES LESS THAN (2007) );
要从分区中删除所有行
p0
,请使用以下语句:ALTER TABLE t1 TRUNCATE PARTITION p0;
刚刚显示的语句与以下语句具有相同的效果
DELETE
:DELETE FROM t1 WHERE year_col < 1991;
截断多个分区时,分区不必是连续的:这可以大大简化分区表上的删除操作,否则
WHERE
如果使用DELETE
语句完成这些操作将需要非常复杂的条件。例如,此语句从分区中删除所有行p1
并p3
:ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
此处显示等效
DELETE
语句:DELETE FROM t1 WHERE (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_ROWS FROM 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 t2 COALESCE 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
有关语句 的更多详细信息和示例 ,请参阅第 24.3.1 节,“RANGE 和 LIST 分区的管理”。要与表交换表分区或子分区,请使用
ALTER TABLE ... EXCHANGE PARTITION
语句——即将分区或子分区中的任何现有行移动到非分区表,并将非分区表中的任何现有行移动到表分区或子分区。使用 将一列或多列添加到分区表
ALGORITHM=INSTANT
后,就无法再与该表交换分区。有关使用信息和示例,请参阅 第 24.3.3 节,“使用表交换分区和子分区”。
有几个选项提供分区维护和修复功能,类似于通过 and 等语句为非分区表实现的功能
CHECK TABLE
(REPAIR TABLE
分区表也支持这些功能;有关更多信息,请参阅 第 13.7.3 节,“表维护语句”)。这些包括ANALYZE PARTITION
、CHECK PARTITION
、OPTIMIZE PARTITION
、REBUILD PARTITION
和REPAIR PARTITION
。这些选项中的每一个都采用一个 子句,该partition_names
子句由一个或多个分区名称组成,以逗号分隔。分区必须已经存在于目标表中。您也可以使用ALL
关键字代替partition_names
,在这种情况下,该语句作用于所有表分区。有关更多信息和示例,请参阅 第 24.3.4 节,“分区的维护”。InnoDB
目前不支持每个分区的优化;ALTER TABLE ... OPTIMIZE PARTITION
导致重建和分析整个表,并发出适当的警告。(错误 #11751825,错误 #42822)要解决此问题,请改用ALTER TABLE ... REBUILD PARTITION
andALTER TABLE ... ANALYZE PARTITION
。未分区的表不支持
ANALYZE PARTITION
、CHECK PARTITION
、OPTIMIZE PARTITION
和选项。REPAIR PARTITION
REMOVE PARTITIONING
使您能够删除表的分区而不影响表或其数据。此选项可以与其他ALTER TABLE
选项结合使用,例如用于添加、删除或重命名列或索引的选项。使用
ENGINE
选项 withALTER TABLE
更改表使用的存储引擎而不影响分区。目标存储引擎必须提供自己的分区处理程序。只有InnoDB
和NDB
存储引擎有本地分区处理程序;NDB
MySQL 8.0 当前不支持。
一个ALTER TABLE
语句可以包含一个PARTITION BY
or
REMOVE PARTITIONING
子句作为对其他更改规范的补充,但PARTITION
BY
orREMOVE PARTITIONING
子句必须在任何其他规范之后最后指定。
、ADD PARTITION
、DROP
PARTITION
、COALESCE PARTITION
、
REORGANIZE PARTITION
、和
选项不能与单个 中的其他更改规范组合ANALYZE
PARTITION
,因为刚刚列出的选项作用于各个分区。有关详细信息,请参阅
第 13.1.9.1 节,“ALTER TABLE 分区操作”。
CHECK PARTITION
REPAIR PARTITION
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 t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;
在第一种情况下,您可以
使用带有单个选项的单个语句同时分析分区p1
和p2
表的表
,该选项列出了要分析的两个分区,如下所示:
t1
ANALYZE PARTITION
ALTER TABLE t1 ANALYZE PARTITION p1, p2;
在第二种情况下,不可能同时对同一张表的不同分区执行
ANALYZE
和CHECK
操作。相反,您必须发出两个单独的语句,如下所示:
ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 CHECK PARTITION p2;
REBUILD
子分区目前不支持操作。该REBUILD
关键字在子分区中是明确不允许的,
ALTER TABLE
如果这样使用会导致失败并出现错误。
CHECK PARTITION
REPAIR
PARTITION
当待检查或修复的分区存在重复键错误时
,操作失败。
有关这些语句的更多信息,请参阅 第 24.3.4 节,“分区的维护”。