MySQL的SQL语句 - 数据定义语句（6）- ALTER TABLE 语句（5）

原创

数据杂货铺 2020-12-06 14:13:46 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者数据杂货铺的原创作品，请联系作者获取转载授权，否则将追究法律责任

ALTER TABLE 分区操作

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

●只需在分区表上使用带有partition_options子句的ALTER TABLE，就可以根据partition_options定义的分区方案对表进行重新分区。此子句始终以PARTITION BY开头，并遵循适用于CREATE TABLE的partition_options子句的语法和其他规则，还可以用于对尚未分区的现有表进行分区。例如，考虑定义如下的（非分区）表：

这个表可以使用id列作为分区键进行哈希分区，通过下面的语句分为8个分区:

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

使用ALTER TABLE ... PARTITION BY语句得到的表必须遵循与使用CREATE TABLE ... PARTITION BY创建表的相同规则。这包括管理表可能具有的任何唯一键（包括任何主键）与分区表达式中使用的一列或多列之间关系的规则。CREATE TABLE ... PARTITION BY用于指定分区数，也适用于ALTER TABLE ... PARTITION BY。

ALTER TABLE ADD PARTITION的partition_definition子句支持与CREATE TABLE语句同名子句相同的选项。假设创建了如下所示的分区表：

可以将新分区p3添加到此表中，用于存储小于2002的值，如下所示：

DROP PARTITION可用于删除一个或多个RANGE或LIST分区。此语句不能与HASH或者KEY分区一起使用；请使用COALESCE PARTITION（请参阅本节后面的部分）。存储在partition_names列表中的已删除分区中的任何数据都将被丢弃。例如，给定前面定义的表t1，可以删除名为p0和p1的分区，如下所示：

注意

DROP PARTITION不适用于使用NDB存储引擎的表。

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

DISCARD PARTITION ... TABLESPACE 和 IMPORT PARTITION ... TABLESPACE 选项将 Transportable Tablespace特性扩展到各个InnoDB表分区。每个InnoDB表分区都有自己的表空间文件（.ibd文件）。Transportable Tablespace特性使得将表空间从一个正在运行的MySQL服务器实例复制到另一个正在运行的实例，或者在同一个实例上执行恢复非常容易。这两个选项都采用逗号分隔的一个或多个分区名列表。例如：

当在子分区表上运行DISCARD PARTITION ... TABLESPACE和IMPORT PARTITION ... TABLESPACE时，允许使用分区名和子分区名。指定分区名称时，将包括该分区的子分区。

Transportable Tablespace特性还支持复制或恢复分区的InnoDB表。

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

要从选定分区中删除行，请使用TRUNCATE PARTITION选项。此选项接受一个或多个逗号分隔的分区名列表。考虑由以下语句创建的表t1：

要从分区p0中删除所有行，请使用以下语句：

刚才显示的语句与下面的DELETE语句具有相同的效果：

1. DELETE FROM t1 WHERE year_col < 1991;

当截断多个分区时，分区不必是连续的：这可以极大地简化分区表上的删除操作，否则如果使用DELETE语句，则需要非常复杂的WHERE条件。语句从p1和p3删除所有行：

等效的DELETE语句如下所示：

如果使用ALL关键字代替分区名列表，则该语句将作用于所有表分区。

TRUNCATE PARTITION只删除行；它不改变表本身或其任何分区的定义。

要验证是否删除了行，请检查INFORMATION_SCHEMA.PARTITIONS表，使用类似这样的查询：

COALESCE PARTITION可以与按HASH或KEY分区的表一起使用，按指定数量减少分区数。假设已经创建了如下所示的表t2：

要将t2使用的分区数从6减少到4，请使用以下语句：

最后一个编号分区中包含的数据将合并到其余分区中。在本例中，分区4和分区5将合并到前4个分区（编号为0、1、2和3的分区）。

要更改分区表使用的部分分区，但不是所有分区，可以使用REORGANIZE PARTITION。此语句可用于以下几种方式：

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

■ 把一个现有的分区分成几个分区。通过为partition_names命名一个分区并提供多个partition_names来实现这一点。

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

注意

对于没有显式命名的分区，MySQL自动提供默认名称p0、p1、p2等。子分区也是如此。

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

●有几个选项提供了分区维护和修复功能，类似于通过语句（如CHECK TABLE和REPAIR TABLE）实现的分区维护和修复功能（分区表也支持这些功能）。这包含分ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION和REPAIR PARTITION。每个选项都有一个partition_names子句，由一个或多个分区名称组成，用逗号分隔。分区必须已经存在于目标表中。还可以使用ALL关键字代替partition_names，在这种情况下，语句作用于所有表分区。

InnoDB目前不支持逐分区优化；ALTER TABLE ... OPTIMIZE PARTITION子句会导致重建和分析整个表，并发出相应的警告。要解决此问题，请使用ALTER TABLE ... REBUILD PARTITION 和 ALTER TABLE ... ANALYZE PARTITION。

未分区的表不支持ANALYZE PARTITION、CHECK PARTITION、OPTIMIZE PARTITION和REPAIR PARTITION选项。

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

●在ALTER TABLE中使用ENGINE选项可以在不影响分区的情况下更改表使用的存储引擎。目标存储引擎必须提供自己的分区处理程序。只有InnoDB和NDB存储引擎有原生分区处理程序；MySQL 8.0目前不支持NDB。

除了其他修改声明之外，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语句中只能使用以下任一选项的单个实例：PARTITION BY、ADD PARTITION、DROP PARTITION、TRUNCATE PARTITION、EXCHANGE PARTITION、REORGANIZE PARTITION、或者 COALESCE PARTITION、ANALYZE PARTITION、CHECK PARTITION、OPTIMIZE PARTITION、REBUILD PARTITION、REMOVE PARTITIONING。

例如，以下两个语句无效：