13.1.8.1 ALTER TABLE 分区操作

ALTER TABLE的与分区相关的子句可与分区 table 一起使用，以进行重新分区，添加，删除，丢弃，导入，合并和拆分分区，以及执行分区维护。

• 只需在分区 table 上使用带有_的* partition_options 子句，即可根据 partition_options 定义的分区方案对 table 进行重新分区。此子句始终以PARTITION BY开头，并遵循与CREATE TABLE的 partition_options *子句相同的语法和其他规则(有关更多详细信息，请参见第 13.1.18 节“ CREATE TABLE 语句”)，还可以用于对尚未分区的现有 table 进行分区。例如，考虑一个定义如下的(未分区)table：

CREATE TABLE t1 (
    id INT,
    year_col INT
);

可以通过HASH使用id列作为分区键将该 table 分区为 8 个分区，具体说明如下：

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

MySQL 支持带有[SUB]PARTITION BY [LINEAR] KEY的ALGORITHM选项。 ALGORITHM=1导致服务器在计算分区中行的位置时使用与 MySQL 5.1 相同的键哈希函数； ALGORITHM=2table 示服务器采用默认在 MySQL 5.5 及更高版本中为新的KEY分区 table 实现和使用的键哈希函数。 (使用 MySQL 5.5 和更高版本中使用的键哈希函数创建的分区 table 不能被 MySQL 5.1 服务器使用.)未指定该选项的效果与使用ALGORITHM=2相同。此选项主要用于在 MySQL 5.1 和更高版本的 MySQL 版本之间升级或降级[LINEAR] KEY分区 table 时，或在 MySQL 5.5 或更高版本的服务器上创建可以由KEY或LINEAR KEY分区的 table 时，该 table 可以在 MySQL 5.1 服务器上使用。

要升级在 MySQL 5.1 中创建的KEY分区 table，请首先执行显示创建 table并注意显示的确切列数和分区数。现在，使用与CREATE TABLE语句完全相同的列列 table 和分区数来执行ALTER TABLE语句，同时在PARTITION BY关键字后立即添加ALGORITHM=2。 (如果原始 table 定义已使用LINEAR关键字，则还应包括该关键字.)此处显示了mysqlClient 端中的会话示例：

mysql> SHOW CREATE TABLE p\G
*************************** 1. row ***************************
       Table: p
Create Table: CREATE TABLE `p` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cd` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LINEAR KEY (id)
PARTITIONS 32 */
1 row in set (0.00 sec)

mysql> ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;
Query OK, 0 rows affected (5.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE p\G
*************************** 1. row ***************************
       Table: p
Create Table: CREATE TABLE `p` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cd` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LINEAR KEY (id)
PARTITIONS 32 */
1 row in set (0.00 sec)

降级使用 MySQL 5.5 和更高版本中使用的默认键哈希创建的 table 以使其可以被 MySQL 5.1 服务器使用的方法类似，不同的是，在这种情况下，您应使用ALGORITHM=1强制使用 MySQL 5.1 键重新构建 table 的分区，哈希函数。建议不要执行此操作，除非为了与 MySQL 5.1 服务器兼容而必要，否则 MySQL 5.5 和更高版本默认情况下会使用改进的KEY哈希函数来解决较早实现中发现的许多问题。

使用ALTER TABLE ... PARTITION BY语句生成的 table 必须遵循与使用CREATE TABLE ... PARTITION BY创建的 table 相同的规则。这包括控制 table 可能具有的任何唯一键(包括任何主键)与分区 table 达式中使用的一个或多个列之间的关系的规则，如第 22.6.1 节“分区键，主键和唯一键”中所述。用于指定分区数的CREATE TABLE ... PARTITION BY规则也适用于ALTER TABLE ... PARTITION BY。

ALTER TABLE ADD PARTITION的* partition_definition *子句支持与CREATE TABLE语句的同名子句相同的选项。 (有关语法和说明，请参见第 13.1.18 节“ CREATE TABLE 语句”。)假设您已创建分区 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)
);

您可以向该 table 添加新分区p3，以存储小于2002的值，如下所示：

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

DROP PARTITION可用于删除一个或多个RANGE或LIST分区。该语句不能与HASH或KEY分区一起使用；而是使用COALESCE PARTITION(请参见下文)。存储在* partition_names *列 table 中命名的已删除分区中的所有数据都将被丢弃。例如，给定先前定义的 tablet1，可以删除名为p0和p1的分区，如下所示：

ALTER TABLE t1 DROP PARTITION p0, p1;

ADD PARTITION和DROP PARTITION当前不支持IF [NOT] EXISTS。

磁盘分区...桌面和导入分区...桌面选项将Transportable Tablespace功能扩展到单个InnoDBtable 分区。每个InnoDBtable 分区都有自己的 table 空间文件(.ibd文件)。 Transportable Tablespace功能使将 table 空间从正在运行的 MySQL 服务器实例复制到另一个正在运行的实例，或在同一实例上执行还原变得容易。这两个选项均采用一个或多个以逗号分隔的分区名称的列 table。例如：

ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;

ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;

在子分区 table 上运行磁盘分区...桌面和导入分区...桌面时，分区名称和子分区名称均被允许。指定分区名称后，将包括该分区的子分区。

Transportable Tablespace功能还支持复制或还原已分区的InnoDBtable。有关更多信息，请参见第 14.6.1.3 节“导入 InnoDBtable”。

支持分区 table 的重命名。您可以使用ALTER TABLE ... REORGANIZE PARTITION间接重命名各个分区；但是，此操作将复制分区的数据。

要从选定分区中删除行，请使用TRUNCATE PARTITION选项。此选项采用一个逗号分隔的一个或多个分区名称的列 table。例如，考虑此处定义的 tablet1：

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;

截断多个分区时，这些分区不必是连续的：这可以大大简化对分区 table 的删除操作，否则，如果使用DELETE语句完成，则它们将需要非常复杂的WHERE条件。例如，此语句从分区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关键字代替分区名称列 table，则该语句将作用于所有 table 分区。

TRUNCATE PARTITION仅删除行；它不会更改 table 本身或其任何分区的定义。

要验证是否删除了行，请使用诸如此类的查询来检查INFORMATION_SCHEMA.PARTITIONStable：

SELECT PARTITION_NAME, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 't1';

仅使用MyISAM，InnoDB或MEMORY存储引擎的分区 table 支持TRUNCATE PARTITION。它也适用于BLACKHOLEtable(但无效)。 ARCHIVEtable 不支持此功能。

COALESCE PARTITION可以与被HASH或KEY分区的 table 一起使用，以减少* number *的分区数。假设您已创建 tablet2，如下所示：

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 的分区)。

要更改分区 table 使用的部分但不是全部分区，可以使用REORGANIZE PARTITION。该语句可以几种方式使用：

• 将一组分区合并为一个分区。这是通过在* partition_names 列 table 中命名几个分区并为 partition_definition *提供单个定义来完成的。

  • 将现有分区拆分为几个分区。通过为* partition_names 命名单个分区并提供多个 partition_definitions *来实现此目的。

  • 更改使用VALUES LESS THAN定义的分区子集的范围或使用VALUES IN定义的分区子集的值列 table。

  • 对于使用HASH分区自动分区以强制重新分配数据的 table，也可以在不使用partition_names INTO (partition_definitions)选项的情况下使用此语句。 (当前，只有NDB个 table 以这种方式自动分区。)这在 NDB Cluster 中非常有用，在该数据库中，将新的 NDB Cluster 数据节点在线添加到现有 NDB Cluster 之后，您希望将现有 NDB Clustertable 数据重新分配给新的 NDB Cluster。数据节点。在这种情况下，应使用ALGORITHM=INPLACE选项调用该语句；换句话说，如下所示：

ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION;

您不能与联机 table 重组同时执行其他 DDL，也就是说，在执行ALTER TABLE ... ALGORITHM=INPLACE, REORGANIZE PARTITION语句时不能发出其他 DDL 语句。有关在线添加 NDB Cluster 数据节点的更多信息，请参阅第 21.5.7 节“在线添加 NDB 群集数据节点”。

尝试在显式分区的 table 上使用不带partition_names INTO (partition_definitions)选项的REORGANIZE PARTITION会导致错误 REORGANIZE PARTITION，而没有参数只能在使用 HASH 分区的自动分区的 table 上使用。

有关ALTER TABLE ... REORGANIZE PARTITION语句的详细信息和示例，请参见第 22.3.1 节“ManagementRANGE 和 LIST 分区”。

• 要与 table 交换 table 分区或子分区，请使用ALTER TABLE ...交换分区语句-即，将分区或子分区中的所有现有行移至未分区 table，并将未分区 table 中的所有现有行移至 table 分区或子分区。

有关用法信息和示例，请参见第 22.3.3 节“用 table 交换分区和子分区”。

• 多个选项提供的分区维护和修复功能类似于通过CHECK TABLE和REPAIR TABLE之类的语句为非分区 table 实现的功能(分区 table 也支持这些功能；有关更多信息，请参见第 13.7.2 节“table 维护声明”)。这些包括ANALYZE PARTITION，CHECK PARTITION，OPTIMIZE PARTITION，REBUILD PARTITION和REPAIR PARTITION。每个选项都带有* partition_names 子句，该子句由一个或多个分区名称组成，以逗号分隔。分区必须已经存在于要更改的 table 中。您也可以使用ALL关键字代替 partition_names *，在这种情况下，该语句将作用于所有 table 分区。有关更多信息和示例，请参见第 22.3.4 节“分区维护”。

某些 MySQL 存储引擎(例如InnoDB)不支持按分区优化。对于使用这种存储引擎的分区 table，ALTER TABLE ... OPTIMIZE PARTITION将导致整个 table 的重建和分析，并发出适当的警告。 (缺陷#11751825，错误#42822)

要变通解决此问题，请改为使用语句ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION。

对于未分区的 table，不允许ANALYZE PARTITION，CHECK PARTITION，OPTIMIZE PARTITION和REPAIR PARTITION选项。

• 在 MySQL 5.7.9 及更高版本中，您可以使用ALTER TABLE ... UPGRADE PARTITIONING将使用旧的通用分区处理程序创建的分区InnoDBtable 升级到 MySQL 5.7.6 及更高版本中使用的InnoDB本机分区。同样从 MySQL 5.7.9 开始，mysql_upgradeUtil 会检查已分区的InnoDBtable，并尝试将其升级到本机分区，作为其正常操作的一部分。

• REMOVE PARTITIONING使您可以删除 table 的分区，而不会影响 table 或其数据。该选项可以与其他ALTER TABLE选项(例如用于添加，删除或重命名列或索引的选项)组合使用。

• 将ENGINE选项与ALTER TABLE一起使用可更改 table 使用的存储引擎，而不会影响分区。

当针对使用MyISAM的分区 table(或使用 table 级锁定的另一个存储引擎)运行ALTER TABLE ... EXCHANGE PARTITION或ALTER TABLE ... TRUNCATE PARTITION时，仅锁定实际读取的那些分区。 (这不适用于使用采用行级锁定的存储引擎的分区 table，例如InnoDB。)请参见第 22.6.4 节“分区和锁定”。

ALTER TABLE语句除其他 alter 规范外还可以包含PARTITION BY或REMOVE PARTITIONING子句，但是PARTITION BY或REMOVE PARTITIONING子句必须在其他任何规范之后最后指定。

ADD PARTITION，DROP PARTITION，COALESCE PARTITION，REORGANIZE PARTITION，ANALYZE PARTITION，CHECK PARTITION和REPAIR PARTITION选项不能与单个ALTER TABLE中的其他更改规范组合，因为刚刚列出的选项作用于各个分区。有关更多信息，请参见第 13.1.8.1 节，“ 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;

在第一种情况下，可以使用带有单个ANALYZE PARTITION选项的单个语句同时分析 tablet1的分区p1和p2，该选项列出了两个要分析的分区，如下所示：

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

在第二种情况下，不可能在同一 table 的不同分区上同时执行ANALYZE和CHECK操作。相反，您必须发出两个单独的语句，如下所示：

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

子分区当前不支持REBUILD操作。 REBUILD关键字明确不允许使用子分区，如果使用ALTER TABLE则会导致ALTER TABLE失败并显示错误。

当要检查或修复的分区包含任何重复的键错误时，CHECK PARTITION和REPAIR PARTITION操作失败。

有关这些语句的更多信息，请参见第 22.3.4 节“分区维护”。