Documentation Home

13.1.9.1 ALTER TABLE 分区操作

与分区相关的子句 forALTER TABLE可以与分区表一起使用,用于重新分区、添加、删除、丢弃、导入、合并和拆分分区,以及执行分区维护。

  • 只需在分区表上 使用partition_options with 子句即可根据. 该子句始终以 , 开头,并遵循适用于子句 for 的相同语法和其他规则 (有关更多详细信息,请参阅第 13.1.20 节,“CREATE TABLE 语句”),并且还可以用于对现有表进行分区尚未分区。例如,考虑如下所示定义的(未分区)表: ALTER TABLEpartition_optionsPARTITION BYpartition_optionsCREATE 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 函数,默认情况下用于KEYMySQL 5.5 及更高版本中的新分区表。(使用 MySQL 5.5 及更高版本中采用的键散列函数创建的分区表不能由 MySQL 5.1 服务器使用。)不指定该选项与使用 . 具有相同的效果ALGORITHM=2。此选项主要用于升级或降级时使用[LINEAR] KEYMySQL 5.1 和更高版本的 MySQL 之间的分区表,或者用于创建由 MySQL 5.5 或更高版本服务器分区KEYLINEAR 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_definitionfor 子句支持与语句 的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的分区,如下所示: p0p1

    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 ... TABLESPACEIMPORT PARTITION ... TABLESPACE选项将可 传输表空间功能扩展到单个 InnoDB表分区。每个 InnoDB表分区都有自己的表空间文件(.ibdfile)。Transportable Tablespace特性使得将表空间从一个正在运行的 MySQL 服务器实例复制到另一个正在运行的实例,或者在同一个实例上执行恢复变得容易。 这两个选项都采用一个或多个分区名称的逗号分隔列表。例如:

    ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
    ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;

    当在子分区表上运行 时DISCARD PARTITION ... TABLESPACEIMPORT 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语句完成这些操作将需要非常复杂的条件。例如,此语句从分区中删除所有行 p1p3

    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 自动提供默认名称 p0p1p2等。子分区也是如此。

    ALTER TABLE ... REORGANIZE PARTITION 有关语句 的更多详细信息和示例 ,请参阅第 24.3.1 节,“RANGE 和 LIST 分区的管理”

  • 要与表交换表分区或子分区,请使用ALTER TABLE ... EXCHANGE PARTITION语句——即将分区或子分区中的任何现有行移动到非分区表,并将非分区表中的任何现有行移动到表分区或子分区。

    使用 将一列或多列添加到分区表ALGORITHM=INSTANT后,就无法再与该表交换分区。

    有关使用信息和示例,请参阅 第 24.3.3 节,“使用表交换分区和子分区”

  • 有几个选项提供分区维护和修复功能,类似于通过 and 等语句为非分区表实现的功能 CHECK TABLEREPAIR TABLE分区表也支持这些功能;有关更多信息,请参阅 第 13.7.3 节,“表维护语句”)。这些包括ANALYZE PARTITIONCHECK PARTITIONOPTIMIZE PARTITIONREBUILD PARTITIONREPAIR PARTITION。这些选项中的每一个都采用一个 子句,该partition_names子句由一个或多个分区名称组成,以逗号分隔。分区必须已经存在于目标表中。您也可以使用ALL关键字代替 partition_names,在这种情况下,该语句作用于所有表分区。有关更多信息和示例,请参阅 第 24.3.4 节,“分区的维护”

    InnoDB目前不支持每个分区的优化;ALTER TABLE ... OPTIMIZE PARTITION导致重建和分析整个表,并发出适当的警告。(错误 #11751825,错误 #42822)要解决此问题,请改用ALTER TABLE ... REBUILD PARTITIONand ALTER TABLE ... ANALYZE PARTITION

    未分区的表不支持 ANALYZE PARTITIONCHECK PARTITIONOPTIMIZE PARTITION和选项。REPAIR PARTITION

  • REMOVE PARTITIONING使您能够删除表的分区而不影响表或其数据。此选项可以与其他 ALTER TABLE选项结合使用,例如用于添加、删除或重命名列或索引的选项。

  • 使用ENGINE选项 with ALTER TABLE更改表使用的存储引擎而不影响分区。目标存储引擎必须提供自己的分区处理程序。只有InnoDBNDB存储引擎有本地分区处理程序;NDBMySQL 8.0 当前不支持。

一个ALTER TABLE 语句可以包含一个PARTITION BYor REMOVE PARTITIONING子句作为对其他更改规范的补充,但PARTITION BYorREMOVE PARTITIONING子句必须在任何其他规范之后最后指定。

ADD PARTITIONDROP PARTITIONCOALESCE PARTITIONREORGANIZE PARTITION、和 选项不能与单个 中的其他更改规范组合ANALYZE PARTITION,因为刚刚列出的选项作用于各个分区。有关详细信息,请参阅 第 13.1.9.1 节,“ALTER TABLE 分区操作”CHECK PARTITIONREPAIR PARTITIONALTER 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;

在第一种情况下,您可以 使用带有单个选项的单个语句同时分析分区p1p2表的表 ,该选项列出了要分析的两个分区,如下所示: t1ANALYZE PARTITION

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

在第二种情况下,不可能同时对同一张表的不同分区执行 ANALYZECHECK 操作。相反,您必须发出两个单独的语句,如下所示:

ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 CHECK PARTITION p2;

REBUILD子分区目前不支持操作。该REBUILD关键字在子分区中是明确不允许的, ALTER TABLE如果这样使用会导致失败并出现错误。

CHECK PARTITION REPAIR PARTITION当待检查或修复的分区存在重复键错误时 ,操作失败。

有关这些语句的更多信息,请参阅 第 24.3.4 节,“分区的维护”