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 t1
      PARTITION 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] KEYMySQL 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 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存储引擎的表。请参见“ RANGE和LIST分区的管理”和“ NDB群集的已知限制”。

  ADD PARTITION并且DROP PARTITION目前不支持IF[NOT] EXISTS。

  在DISCARD PARTITION ... TABLESPACE和IMPORT PARTITION ... TABLESPACE选项延长传输表空间功能个别InnoDB表分区。每个InnoDB表分区都有其自己的表空间文件（.ibdfile）。可移植表空间功能使将表空间从正在运行的MySQL服务器实例复制到另一个正在运行的实例，或在同一实例上执行还原变得容易。这两个选项均采用逗号分隔的一个或多个分区名称列表。例如：

  ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
  

  ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
  

  在分区表上运行时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 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)
  );
  

  要从partition删除所有行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语句的更多详细信息和示例，请参见“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存储引擎具有本机分区处理程序；NDBMySQL 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 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当要检查或修复的分区包含任何重复的键错误时，操作将失败。

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