MySQL8 中文参考（一百）

绝不原创的飞龙

于 2024-06-26 19:33:01 发布

阅读量1k

点赞数 9

分类专栏： mysql 文章标签： mysql

License CC BY-NC-SA 4.0 / 自豪地采用谷歌翻译

本文链接：https://blog.csdn.net/wizardforcel/article/details/139996914

版权

mysql 专栏收录该内容

138 篇文章

订阅专栏

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

26.2 分区类型

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-types.html

26.2.1 范围分区

26.2.2 LIST 分区

26.2.3 列分区

26.2.4 HASH 分区

26.2.5 KEY 分区

26.2.6 子分区

26.2.7 MySQL 分区如何处理 NULL 值

本节讨论了 MySQL 8.0 中可用的分区类型。这些包括以下列出的类型：

RANGE 分区。 这种分区类型根据列值落在给定范围内来将行分配到分区。参见 26.2.1 节，“RANGE 分区”。有关此类型的扩展信息，RANGE COLUMNS，请参见 26.2.3.1 节，“RANGE COLUMNS 分区”。
LIST 分区。 类似于RANGE分区，不同之处在于根据与一组离散值匹配的列来选择分区。参见 26.2.2 节，“LIST 分区”。有关此类型的扩展信息，LIST COLUMNS，请参见 26.2.3.2 节，“LIST COLUMNS 分区”。
HASH 分区。 使用这种分区类型，根据用户定义的表达式返回的值来选择一个分区，该表达式在要插入表中的行的列值上操作。该函数可以由 MySQL 中任何产生整数值的有效表达式组成。参见 26.2.4 节，“HASH 分区”。

这种类型的扩展，LINEAR HASH，也是可用的，请参见 26.2.4.1 节，“LINEAR HASH 分区”。
KEY 分区。 这种分区类型类似于HASH分区，不同之处在于只提供要评估的一个或多个列，并且 MySQL 服务器提供自己的哈希函数。这些列可以包含除整数值以外的其他值，因为 MySQL 提供的哈希函数保证无论列数据类型如何，都会得到一个整数结果。这种类型的扩展，LINEAR KEY，也是可用的。参见 26.2.5 节，“KEY 分区”。

数据库分区的一个非常常见的用途是按日期对数据进行分隔。一些数据库系统支持显式日期分区，MySQL 在 8.0 中没有实现。但是，在 MySQL 中，基于DATE、TIME或DATETIME列或利用这些列的表达式创建分区方案并不困难。

当按KEY或LINEAR KEY进行分区时，您可以使用DATE、TIME或DATETIME列作为分区列，而无需对列值进行任何修改。例如，在 MySQL 中，以下表创建语句是完全有效的：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

在 MySQL 8.0 中，还可以使用RANGE COLUMNS和LIST COLUMNS分区使用DATE或DATETIME列作为分区列。

其他分区类型需要产生整数值或NULL的分区表达式。如果您希望通过RANGE、LIST、HASH或LINEAR HASH进行基于日期的分区，您可以简单地使用一个操作DATE、TIME或DATETIME列并返回这样一个值的函数，如下所示：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

本章的以下部分中还可以找到使用日期进行分区的其他示例：

26.2.1 节，“RANGE 分区”
26.2.4 节，“HASH 分区”
26.2.4.1 节，“线性哈希分区”

有关基于日期的分区的更复杂示例，请参阅以下部分：

26.4 节，“分区修剪”
26.2.6 节，“子分区”

MySQL 分区针对与TO_DAYS()、YEAR()和TO_SECONDS()函数一起使用进行了优化。然而，你可以使用其他返回整数或NULL的日期和时间函数，如WEEKDAY()、DAYOFYEAR()或MONTH()。有关这些函数的更多信息，请参见第 14.7 节，“日期和时间函数”。

无论你使用何种分区类型，都要记住，分区在创建时总是自动按顺序编号，从0开始。当向分区表插入新行时，使用这些分区编号来识别正确的分区。例如，如果你的表使用了 4 个分区，这些分区被编号为0、1、2和3。对于RANGE和LIST分区类型，必须确保为每个分区编号定义了一个分区。对于HASH分区，用户提供的表达式必须求值为整数值。对于KEY分区，这个问题由 MySQL 服务器内部使用的哈希函数自动处理。

分区的名称通常遵循其他 MySQL 标识符（如表和数据库）的规则。然而，你应该注意，分区名称不区分大小写。例如，如下所示的CREATE TABLE语句会失败：

mysql> CREATE TABLE t2 (val INT)
 -> PARTITION BY LIST(val)(
 ->     PARTITION mypart VALUES IN (1,3,5),
 ->     PARTITION MyPart VALUES IN (2,4,6)
 -> );
ERROR 1488 (HY000): Duplicate partition name mypart

失败发生是因为 MySQL 在 mypart 和 MyPart 分区名称之间看不到任何区别。

当你为表指定分区数时，这必须表示为一个正的、非零的整数字面值，不能以0.8E+01或6-2这样的表达式表示，即使它求值为整数值也不行。不允许使用小数。

在接下来的章节中，我们并不一定提供用于创建每种分区类型的语法的所有可能形式；有关此信息，请参见第 15.1.20 节，“CREATE TABLE 语句”。

26.2.1 范围分区

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-range.html

通过范围进行分区的表是这样分区的，即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠，并且使用VALUES LESS THAN运算符定义。在接下来的几个示例中，假设你正在创建一个类似以下内容的表，用于保存一家由 1 到 20 号店编号的连锁视频店的人事记录：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

注意

这里使用的employees表没有主键或唯一键。虽然示例在当前讨论的目的上可以正常工作，但你应该记住，在实践中，表极有可能具有主键、唯一键或两者，而用于分区列的可选选择取决于用于这些键的列，如果有的话。有关这些问题的讨论，请参阅 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”。

这个表可以根据你的需求以多种方式进行范围分区。一种方法是使用store_id列。例如，你可以决定通过添加如下所示的PARTITION BY RANGE子句将表分区为 4 部分：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

在这个分区方案中，所有在 1 到 5 号店工作的员工对应的行都存储在p0分区中，而在 6 到 10 号店工作的员工对应的行存储在p1分区中，依此类推。每个分区按顺序定义，从最低到最高。这是PARTITION BY RANGE语法的要求；在这方面，你可以将其类比为 C 或 Java 中一系列if ... elseif ...语句。

很容易确定包含数据(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13)的新行被插入到p2分区中，但当你的连锁店增加到第 21 家店时会发生什么？在这个方案下，没有覆盖store_id大于 20 的行的规则，因此会导致错误，因为服务器不知道将其放在哪里。你可以通过在CREATE TABLE语句中使用“catchall”VALUES LESS THAN子句来避免这种情况，提供所有大于明确命名的最高值的值：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    *PARTITION p3 VALUES LESS THAN MAXVALUE* );

（与本章中的其他示例一样，我们假设默认存储引擎是InnoDB。）

避免找不到匹配值时出现错误的另一种方法是在INSERT语句中使用IGNORE关键字。有关示例，请参见 Section 26.2.2, “LIST Partitioning”。

MAXVALUE表示一个始终大于最大可能整数值的整数值（在数学语言中，它充当最小上界）。现在，任何store_id列值大于或等于 16（定义的最高值）的行都存储在分区p3中。在将来的某个时候——当店铺数量增加到 25、30 或更多时，您可以使用ALTER TABLE语句为 21-25、26-30 等店铺添加新分区（有关如何执行此操作的详细信息，请参见第 26.3 节，“分区管理”）。

以类似的方式，可以根据员工工作代码对表进行分区，即根据job_code列值的范围进行分区。例如——假设两位数的工作代码用于普通（店内）工人，三位数的代码用于办公室和支持人员，四位数的代码用于管理职位——可以使用以下语句创建分区表：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

在这种情况下，所有与店内工人相关的行将存储在分区p0中，与办公室和支持人员相关的行将存储在p1中，与管理人员相关的行将存储在分区p2中。

也可以在VALUES LESS THAN子句中使用表达式。但是，MySQL 必须能够将表达式的返回值作为LESS THAN（<）比较的一部分进行评估。

您可以根据两个DATE列中的一个基于表达式来进行分区，而不是根据店铺编号拆分表数据。例如，假设您希望根据每位员工离开公司的年份进行分区；也就是说，基于YEAR(separated)的值。下面显示了实现这种分区方案的CREATE TABLE语句示例：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在此方案中，所有在 1991 年之前离开的员工的行存储在分区p0中；在 1991 年至 1995 年离开的员工中，存储在p1中；在 1996 年至 2000 年离开的员工中，存储在p2中；而在 2000 年之后离开的任何工人中，存储在p3中。

也可以根据TIMESTAMP列的值，使用UNIX_TIMESTAMP()函数，基于RANGE对表进行分区，如下例所示：

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

不允许使用涉及TIMESTAMP值的任何其他表达式（请参见 Bug #42849）。

范围分区在以下情况之一或多个情况为真时特别有用：

您希望或需要删除“旧”数据。如果您正在使用先前显示的用于 employees 表的分区方案，您可以简单地使用 ALTER TABLE employees DROP PARTITION p0; 来删除所有在 1991 年之前停止为公司工作的员工的行。（有关更多信息，请参见第 15.1.9 节“ALTER TABLE 语句” 和第 26.3 节“分区管理”。）对于行数众多的表，这比运行类似于 DELETE FROM employees WHERE YEAR(separated) <= 1990; 的 DELETE 查询要高效得多。
您希望使用包含日期或时间值的列，或包含从其他系列产生的值。
您经常运行依赖于用于对表进行分区的列的查询。例如，当执行类似于 EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id; 的查询时，MySQL 可以快速确定只需要扫描分区 p2，因为其余分区不可能包含任何满足 WHERE 子句的记录。有关如何实现这一点的更多信息，请参见第 26.4 节“分区修剪”。

这种分区的变体是 RANGE COLUMNS 分区。通过 RANGE COLUMNS 分区，可以使用多个列来定义适用于将行放置在分区中以及确定在执行分区修剪时包含或排除特定分区的分区范围。有关更多信息，请参见第 26.2.3.1 节“RANGE COLUMNS 分区”。

基于时间间隔的分区方案。 如果您希望在 MySQL 8.0 中实现基于时间范围或间隔的分区方案，您有两个选项：

通过 RANGE 对表进行分区，并对分区表达式使用在 DATE、TIME 或 DATETIME 列上操作并返回整数值的函数，如下所示：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

在 MySQL 8.0 中，还可以根据 TIMESTAMP 列的值使用 RANGE 对表进行分区，使用 UNIX_TIMESTAMP() 函数，如下例所示：

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

在 MySQL 8.0 中，不允许使用涉及 TIMESTAMP 值的任何其他表达式。（参见 Bug #42849。）

注意

在 MySQL 8.0 中，也可以使用UNIX_TIMESTAMP(timestamp_column)作为按LIST分区的表的分区表达式。然而，通常不太实用。

通过RANGE COLUMNS按DATE或DATETIME列作为分区列对表进行分区。例如，members表可以直接使用joined列定义，如下所示：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

注意

使用日期或时间类型的分区列，而不是DATE或DATETIME，在RANGE COLUMNS中不受支持。

26.2.2 LIST 分区

译文：dev.mysql.com/doc/refman/8.0/en/partitioning-list.html

MySQL 中的列表分区在许多方面类似于范围分区。与按RANGE分区一样，每个分区必须明确定义。两种分区类型之间的主要区别在于，在列表分区中，每个分区是根据列值在一组值列表中的成员资格而定义和选择的，而不是在一组连续值范围中的一个。这是通过使用PARTITION BY LIST(*expr*)来完成的，其中*expr是一个列值或基于列值并返回整数值的表达式，然后通过VALUES IN (*value_list*)来定义每个分区，其中value_list*是一个逗号分隔的整数列表。

注意

在 MySQL 8.0 中，当按LIST分区时，只能匹配一组整数（可能包括NULL—请参见第 26.2.7 节，“MySQL 分区如何处理 NULL”）。

但是，在使用LIST COLUMN分区时，可以在值列表中使用其他列类型，该分区稍后在本节中描述。

与按范围定义的分区不同，列表分区不需要按任何特定顺序声明。有关更详细的语法信息，请参见第 15.1.20 节，“CREATE TABLE Statement”。

对于接下来的示例，我们假设要分区的表的基本定义由此处显示的CREATE TABLE语句提供：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
);

（这是用作第 26.2.1 节，“RANGE 分区”示例基础的相同表。与其他分区示例一样，我们假设default_storage_engine为InnoDB。）

假设有 20 家视频商店分布在 4 个特许经营店中，如下表所示。

地区	商店 ID 号码
北部	3, 5, 6, 9, 17
东部	1, 2, 10, 11, 19, 20
西部	4, 12, 13, 14, 18
中部	7, 8, 15, 16

要将此表分区，使属于同一地区的商店行存储在同一分区中，您可以使用此处显示的CREATE TABLE语句：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

这使得很容易向表中添加或删除与特定区域相关的员工记录。例如，假设西区的所有商店都被卖给另一家公司。在 MySQL 8.0 中，可以使用查询ALTER TABLE employees TRUNCATE PARTITION pWest删除与该区域商店工作的员工相关的所有行，这比等效的DELETE语句DELETE FROM employees WHERE store_id IN (4,12,13,14,18);执行效率更高。（使用ALTER TABLE employees DROP PARTITION pWest也会删除所有这些行，但也会从表的定义中删除分区pWest；您需要使用ALTER TABLE ... ADD PARTITION语句来恢复表的原始分区方案。）

与RANGE分区一样，可以将LIST分区与哈希或键分区组合以生成复合分区（子分区）。请参见第 26.2.6 节，“子分区”。

与RANGE分区不同，没有像MAXVALUE这样的“捕获所有”；分区表达式的所有预期值应该在PARTITION ... VALUES IN (...)子句中涵盖。包含不匹配的分区列值的INSERT语句会失败并显示错误，如下例所示：

mysql> CREATE TABLE h2 (
 ->   c1 INT,
 ->   c2 INT
 -> )
 -> PARTITION BY LIST(c1) (
 ->   PARTITION p0 VALUES IN (1, 4, 7),
 ->   PARTITION p1 VALUES IN (2, 5, 8)
 -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

当使用单个INSERT语句将多行插入单个InnoDB表时，InnoDB将该语句视为单个事务，因此任何不匹配的值的存在都会导致该语句完全失败，因此不会插入任何行。

你可以通过使用IGNORE关键字来忽略这种类型的错误，尽管对于每一行包含不匹配的分区列值的情况会发出警告，如下所示。

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> TABLE h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)

您可以在以下TABLE语句的输出中看到，包含不匹配的分区列值的行被静默拒绝，而不包含不匹配值的行被插入到表中：

mysql> TABLE h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 还支持LIST COLUMNS分区，这是LIST分区的一种变体，允许您使用除整数以外的其他类型的列作为分区列，并使用多个列作为分区键。有关更多信息，请参见第 26.2.3.2 节，“LIST COLUMNS 分区”。

26.2.3 列分区

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-columns.html

26.2.3.1 范围列分区

26.2.3.2 列表列分区

接下来的两节讨论COLUMNS分区，这是RANGE和LIST分区的变体。COLUMNS分区允许在分区键中使用多个列。所有这些列都被考虑用于将行放入分区以及确定哪些分区要检查以匹配行进行分区修剪。

此外，RANGE COLUMNS分区和LIST COLUMNS分区都支持使用非整数列来定义值范围或列表成员。允许的数据类型如下列表所示：

所有整数类型：TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), 和 BIGINT。（这与按RANGE和LIST进行分区相同。）

其他数值数据类型（如DECIMAL或FLOAT 和 DATETIME。

不支持使用其他与日期或时间相关的数据类型作为分区列。
以下字符串类型：CHAR, VARCHAR, BINARY, 和 VARBINARY。

TEXT 和 BLOB 列不支持作为分区列。

下面两节关于RANGE COLUMNS和LIST COLUMNS分区的讨论假定您已经熟悉基于范围和列表的分区，这在 MySQL 5.1 及更高版本中得到支持；有关更多信息，请参见第 26.2.1 节，“范围分区”和第 26.2.2 节，“列表分区”。

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-columns-range.html

26.2.3.1 RANGE COLUMNS 分区

列范围分区类似于范围分区，但允许您根据多个列值的范围定义分区。此外，您可以使用非整数类型的列定义范围。

RANGE COLUMNS分区与RANGE分区在以下方面有显著不同：

RANGE COLUMNS不接受表达式，只接受列名。
RANGE COLUMNS接受一个或多个列的列表。

RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。将行放置在RANGE COLUMNS分区中也是基于元组之间的比较；这将在本节后面进一步讨论。
RANGE COLUMNS分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列。（详细信息请参阅第 26.2.3 节，“COLUMNS Partitioning”。）

创建由RANGE COLUMNS分区的基本语法如下所示：

CREATE TABLE *table_name*
PARTITION BY RANGE COLUMNS(*column_list*) (
    PARTITION *partition_name* VALUES LESS THAN (*value_list*)[,
    PARTITION *partition_name* VALUES LESS THAN (*value_list*)][,
    ...]
)

*column_list*:
    *column_name*[, *column_name*][, ...]

*value_list*:
    *value*[, *value*][, ...]

注意

在创建分区表时可以使用的并非所有CREATE TABLE选项都在此处展示。有关完整信息，请参阅第 15.1.20 节，“CREATE TABLE Statement”。

在刚刚展示的语法中，column_list是一个或多个列的列表（有时称为分区列列表），value_list是一个值列表（即，它是一个分区定义值列表）。必须为每个分区定义提供一个value_list，并且每个*value_list必须具有与column_list中列数相同的值。一般来说，如果在COLUMNS子句中使用了N列，则每个VALUES LESS THAN子句也必须提供一个包含N*个值的列表。

分区列列表中的元素和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。然而，分区列列表和值列表中列名的顺序不必与CREATE TABLE语句的主体部分中表列定义的顺序相同。与通过RANGE分区的表一样，您可以使用MAXVALUE来表示一个值，使得插入到给定列中的任何合法值始终小于此值。以下是一个CREATE TABLE语句的示例，可帮助说明所有这些要点：

mysql> CREATE TABLE rcx (
 ->     a INT,
 ->     b INT,
 ->     c CHAR(3),
 ->     d INT
 -> )
 -> PARTITION BY RANGE COLUMNS(a,d,c) (
 ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
 ->     PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
 ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
 ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 -> );
Query OK, 0 rows affected (0.15 sec)

表rcx包含列a、b、c、d。提供给COLUMNS子句的分区列列表使用了这些列中的 3 列，顺序为a、d、c。用于定义分区的每个值列表包含 3 个值，顺序相同；也就是说，每个值列表元组的形式为（INT、INT、CHAR(3)），这对应于列a、d和c使用的数据类型（按顺序）。

将行放入分区是通过比较要插入的行中与COLUMNS子句中匹配的元组与用于定义表分区的VALUES LESS THAN子句中使用的元组来确定的。因为我们比较的是元组（即值的列表或集合），而不是标量值，所以在与简单的RANGE分区不同的情况下，与RANGE COLUMNS分区一起使用的VALUES LESS THAN的语义有所不同。在RANGE分区中，生成与VALUES LESS THAN中的限制值相等的表达式值的行永远不会放入相应的分区；然而，在使用RANGE COLUMNS分区时，有时可能会将分区列列表的第一个元素的值与VALUES LESS THAN值列表中第一个元素的值相等的行放入相应的分区。

考虑通过以下语句创建的RANGE分区表：

CREATE TABLE r1 (
    a INT,
    b INT
)
PARTITION BY RANGE (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们向该表中插入 3 行，使得每行的a列值均为5，则所有 3 行都存储在分区p1中，因为在每种情况下，a列值均不小于 5，我们可以通过针对信息模式PARTITIONS表执行适当的查询来查看：

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS
 ->     FROM INFORMATION_SCHEMA.PARTITIONS
 ->     WHERE TABLE_NAME = 'r1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

现在考虑一个类似的表rc1，它使用了RANGE COLUMNS分区，COLUMNS子句中引用了列a和b，如下所示创建：

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

如果我们将刚刚插入r1的相同行插入rc1，则行的分布会有所不同：

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS
 ->     FROM INFORMATION_SCHEMA.PARTITIONS
 ->     WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
2 rows in set (0.00 sec)

这是因为我们比较的是行而不是标量值。我们可以将插入的行值与用于在表rc1中定义分区p0的VALUES THAN LESS THAN子句中的限制行值进行比较，如下所示：

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

2 元组(5,10)和(5,11)被认为小于(5,12)，因此它们被存储在分区p0中。由于 5 不小于 5，12 不小于 12，(5,12)被认为不小于(5,12)，并存储在分区p1中。

在前面的示例中，SELECT语句也可以使用显式行构造函数编写，如下所示：

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

有关在 MySQL 中使用行构造函数的更多信息，请参阅第 15.2.15.5 节，“行子查询”。

对于只使用单个分区列进行RANGE COLUMNS分区的表，行存储在分区中的方式与通过RANGE分区的等效表相同。以下CREATE TABLE语句创建了一个使用 1 个分区列进行RANGE COLUMNS分区的表：

CREATE TABLE rx (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们将行(5,10)、(5,11)和(5,12)插入到这个表中，我们可以看到它们的放置方式与我们之前创建和填充的表r相同：

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
 ->     FROM INFORMATION_SCHEMA.PARTITIONS
 ->     WHERE TABLE_NAME = 'rx';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

也可以创建按RANGE COLUMNS分区的表，其中一个或多个列的限制值在连续的分区定义中重复。只要用于定义分区的列值元组严格递增，就可以这样做。例如，以下每个CREATE TABLE语句都是有效的：

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

即使乍看之下可能不会成功，以下语句也会成功，因为列b的限制值对于分区p0为 25，对于分区p1为 20，列c的限制值对于分区p1为 100，对于分区p2为 50：

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

在设计按RANGE COLUMNS分区的表时，您可以通过使用mysql客户端对所需元组进行比较来测试连续的分区定义，如下所示：

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

如果CREATE TABLE语句包含不严格递增顺序的分区定义，它将失败并显示错误，如下例所示：

mysql> CREATE TABLE rcf (
 ->     a INT,
 ->     b INT,
 ->     c INT
 -> )
 -> PARTITION BY RANGE COLUMNS(a,b,c) (
 ->     PARTITION p0 VALUES LESS THAN (0,25,50),
 ->     PARTITION p1 VALUES LESS THAN (20,20,100),
 ->     PARTITION p2 VALUES LESS THAN (10,30,50),
 ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

当你遇到这样的错误时，可以通过对它们的列列表进行“小于”比较来推断哪些分区定义是无效的。在这种情况下，问题出在分区p2的定义上，因为用于定义它的元组不小于用于定义分区p3的元组，如下所示：

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

当使用RANGE COLUMNS时，同一列中的MAXVALUE可能出现在多个VALUES LESS THAN子句中。但是，连续分区定义中各列的限制值应该是递增的，不应该定义超过一个分区，其中MAXVALUE用作所有列值的上限，并且此分区定义应该出现在PARTITION ... VALUES LESS THAN子句列表的最后。此外，您不能将MAXVALUE用作连续分区定义中第一列的限制值。

如前所述，使用RANGE COLUMNS分区还可以使用非整数列作为分区列。（有关这些列的完整列表，请参阅第 26.2.3 节，“列分区”。）考虑一个名为employees的表（未分区），使用以下语句创建：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

使用RANGE COLUMNS分区，您可以创建这个表的一个版本，根据员工的姓氏将每一行存储在四个分区中的一个，就像这样：

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

或者，您可以使用以下ALTER TABLE语句使之前创建的employees表按照这种方案进行分区。

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

注意

因为不同的字符集和校对规则具有不同的排序顺序，所以在使用字符串列作为分区列进行分区时，正在使用的字符集和校对规则可能会影响表按RANGE COLUMNS分区的哪个分区存储给定行。此外，在创建这样一个表之后更改给定数据库、表或列的字符集或校对规则可能会导致行分布方式的变化。例如，在使用区分大小写的校对规则时，'and'在'Andersen'之前排序，但在使用不区分大小写的校对规则时，情况则相反。

有关 MySQL 如何处理字符集和校对规则的信息，请参阅第十二章，字符集、校对规则、Unicode。

类似地，您可以使用此处显示的ALTER TABLE语句使employees表按照雇佣员工的年代进行分区，使每一行存储在几个分区中的一个。

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

有关PARTITION BY RANGE COLUMNS语法的更多信息，请参阅第 15.1.20 节，“CREATE TABLE 语句”。

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-columns-list.html

26.2.3.2 列表列分区

MySQL 8.0 支持列表列分区。这是列表分区的一种变体，允许将多个列用作分区键，并且可以使用数据类型为整数类型以外的列作为分区列；您可以使用字符串类型、DATE和DATETIME列。（有关COLUMNS分区列允许的数据类型的更多信息，请参见第 26.2.3 节，“列分区”。）

假设您有一个业务，客户分布在 12 个城市，为了销售和营销目的，您将这些城市组织成了每个包含 3 个城市的 4 个地区，如下表所示：

地区	城市
1	奥斯卡沙姆，赫格斯比，蒙斯特罗斯
2	温默比，胡尔特斯弗雷德，韦斯特维克
3	尼舍，艾克绍，维特兰达
4	乌普维丁厄，阿尔韦斯塔，韦克西厄

使用列表列分区，您可以创建一个客户数据表，根据客户所居住城市的名称将行分配给这些地区中的任何一个分区，如下所示：

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

与范围列分区一样，您不需要在COLUMNS()子句中使用表达式将列值转换为整数。（实际上，除了列名之外，不允许在COLUMNS()中使用其他表达式。）

也可以使用DATE和DATETIME列，如下例所示，使用与之前customers_1表相同的名称和列，但根据renewal列使用列表列分区，根据 2010 年 2 月的某周，将客户账户计划续订的情况存储在 4 个分区中的一个中：

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

这种方法有效，但如果涉及的日期数量非常大，则定义和维护会变得繁琐；在这种情况下，通常更实用的是使用范围或范围列分区。在这种情况下，由于我们希望用作分区键的列是一个DATE列，我们使用范围列分区，如下所示：

CREATE TABLE customers_3 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY RANGE COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
    PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
    PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
    PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);

更多信息请参见第 26.2.3.1 节，“范围列分区”。

此外（与范围列分区一样），您可以在COLUMNS()子句中使用多个列。

有关PARTITION BY LIST COLUMNS()语法的更多信息，请参见第 15.1.20 节，“CREATE TABLE 语句”。

26.2.4 哈希分区

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-hash.html

26.2.4.1 线性哈希分区

主要使用HASH分区来确保数据在预定数量的分区之间均匀分布。使用范围或列表分区，您必须明确指定给定列值或一组列值应存储在哪个分区；使用哈希分区，这个决定已经为您处理，您只需指定一个基于列值的列值或表达式进行哈希处理，以及要将分区表分成的分区数。

要使用HASH分区对表进行分区，必须在CREATE TABLE语句中追加一个PARTITION BY HASH (*expr*)子句，其中*expr是返回整数的表达式。这可以简单地是 MySQL 整数类型之一的列名。此外，您很可能希望在此之后跟上PARTITIONS *num*，其中num*是表示要将表分成的分区数的正整数。

注意

为简单起见，接下来的示例中的表不使用任何键。您应该知道，如果表具有任何唯一键，那么用于此表的分区表达式中的每一列都必须是每个唯一键的一部分，包括主键。有关更多信息，请参见第 26.6.1 节“分区键、主键和唯一键”。

以下语句创建了一个表，该表在store_id列上使用哈希分区，并分为 4 个分区：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

如果不包括PARTITIONS子句，则分区数默认为1；在PARTITIONS关键字后没有跟随数字会导致语法错误。

您还可以使用返回整数的 SQL 表达式作为*expr*。例如，您可能希望根据雇员入职的年份进行分区。可以按照这里所示进行操作：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

*expr*必须返回一个非常量、非随机的整数值（换句话说，它应该是变化的但确定的），并且不得包含任何禁止的构造，如第 26.6 节“分区的限制和限制”中所述。您还应该记住，每次插入、更新（或可能删除）一行时，都会评估此表达式；这意味着非常复杂的表达式可能会导致性能问题，特别是在执行影响大量行的操作（如批量插入）时。

最有效的哈希函数是一个仅作用于单个表列的函数，其值随着列值的增加或减少而一致变化，因为这允许在分区范围上进行“修剪”。也就是说，表达式与其基于的列值的变化越密切，MySQL 就能更有效地使用该表达式进行哈希分区。

例如，当date_col是DATE类型的列时，表达式TO_DAYS(date_col)被认为与date_col的值直接变化，因为对于date_col值的每一次变化，表达式的值都以一种一致的方式变化。与date_col相关的表达式YEAR(date_col)的变化与date_col不像TO_DAYS(date_col)那么直接，因为并非每一次date_col的变化都会产生与YEAR(date_col)等效的变化。即便如此，YEAR(date_col)是一个很好的哈希函数候选，因为它与date_col的一部分直接变化，并且没有任何可能的date_col变化会导致YEAR(date_col)的不成比例变化。

相比之下，假设你有一个名为int_col的列，其类型是INT。现在考虑表达式POW(5-int_col,3) + 6。这将是一个糟糕的哈希函数选择，因为int_col值的变化不能保证产生表达式值的成比例变化。通过给定量改变int_col的值可能会导致表达式值的差异很大。例如，将int_col从5更改为6会导致表达式值减少-1，但将int_col的值从6更改为7会导致表达式值减少-7。

换句话说，列值与表达式值之间的图形越接近由方程y=*c*x跟踪的直线，其中*c*是某个非零常数，表达式越适合用于哈希。这与表达式越非线性，数据在分区之间分布越不均匀有关。

理论上，对涉及多个列值的表达式也可以进行修剪，但确定哪些表达式适合可能会非常困难和耗时。因此，不建议特别使用涉及多个列的哈希表达式。

当使用PARTITION BY HASH时，存储引擎根据表达式的结果的模数确定使用哪个*num分区。换句话说，对于给定的表达式expr，记录存储在分区号N*中，其中*N* = MOD(*expr*, *num*)。假设表t1定义如下，因此有 4 个分区：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

如果你向t1插入一个col3值为'2005-09-15'的记录，则存储它的分区如下确定：

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

MySQL 8.0 还支持一种称为线性哈希的HASH分区的变体，它采用更复杂的算法来确定插入到分区表中的新行的位置。请参阅 Section 26.2.4.1, “线性哈希分区”，了解此算法的描述。

用户提供的表达式在每次插入或更新记录时进行评估。根据情况，当记录被删除时也可能进行评估。

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html

26.2.4.1 线性哈希分区

MySQL 还支持线性哈希，与常规哈希不同之处在于线性哈希利用线性二次幂算法，而常规哈希使用哈希函数值的模。

在语法上，线性哈希分区和常规哈希之间唯一的区别是在PARTITION BY子句中添加LINEAR关键字，如下所示：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

给定表达式*expr，当使用线性哈希时存储记录的分区是从num个分区中的第N个分区，其中N*根据以下算法派生：

找到大于*num的下一个 2 的幂。我们称这个值为V*；可以计算如下：
```
*V* = POWER(2, CEILING(LOG(2, *num*)))
```
(假设*num为 13。那么LOG(2,13)为 3.7004397181411。CEILING(3.7004397181411)为 4，V* = POWER(2,4)，即 16。)
设置*N* = F(column_list) & (V - 1)。
当*N* >= *num*时：
- 设置*V* = V / 2。
- 设置*N* = N & (V - 1)。

假设使用线性哈希分区并具有 6 个分区的表t1是使用以下语句创建的：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

现在假设你想要将两条记录插入到具有col3列值'2003-04-14'和'1998-10-19'的t1中。第一条记录的分区号计算如下：

*V* = POWER(2, CEILING( LOG(2,6) )) = 8
*N* = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(*3 >= 6 is FALSE: record stored in partition #3*)

第二条记录存储在的分区号计算如下：

*V* = 8
*N* = YEAR('1998-10-19') & (8 - 1)
  = 1998 & 7
  = 6

(*6 >= 6 is TRUE: additional step required*)

*N* = 6 & ((8 / 2) - 1)
  = 6 & 3
  = 2

(*2 >= 6 is FALSE: record stored in partition #2*)

通过线性哈希分区的优势在于添加、删除、合并和分割分区变得更快，这在处理包含极大量（TB 级）数据的表时可能会有益。缺点是与使用常规哈希分区获得的分布相比，数据在分区之间的均匀分布性较低。

26.2.5 键分区

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-key.html

按键分区类似于按哈希分区，不同之处在于哈希分区使用用户定义的表达式，而键分区的哈希函数由 MySQL 服务器提供。NDB Cluster 使用MD5()来实现这一目的；对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。

CREATE TABLE ... PARTITION BY KEY的语法规则与创建哈希分区的表的规则类似。主要区别如下：

使用KEY而不是HASH。
KEY只接受零个或多个列名的列表。用作分区键的列必须包含表的主键的一部分或全部，如果表有主键的话。如果未指定列名作为分区键，则使用表的主键，如果有的话。例如，以下CREATE TABLE语句在 MySQL 8.0 中是有效的：
```
CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
```
如果没有主键但有唯一键，则唯一键将用作分区键：
```
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
```
但是，如果唯一键列未定义为NOT NULL，则前面的语句将失败。

在这两种情况下，分区键是id列，即使在SHOW CREATE TABLE的输出中或者在信息模式PARTITIONS表的PARTITION_EXPRESSION列中没有显示。

与其他分区类型不同，KEY分区的列不限于整数或NULL值。例如，以下CREATE TABLE语句是有效的：
```
CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
```
如果指定了不同的分区类型，则前面的语句将无效。（在这种情况下，仅使用PARTITION BY KEY()也是有效的，并且与PARTITION BY KEY(s1)具有相同的效果，因为s1是表的主键。）

关于这个问题的更多信息，请参见第 26.6 节，“分区的限制和限制”。

不支持具有索引前缀的列用作分区键。这意味着CHAR、VARCHAR、BINARY和VARBINARY列可以用作分区键，只要它们不使用前缀；因为在索引定义中必须指定前缀，所以无法在分区键中使用BLOB和TEXT列。在 MySQL 8.0.21 之前，创建、修改或升级分区表时允许使用前缀的列，即使它们未包含在表的分区键中；在 MySQL 8.0.21 及更高版本中，这种宽容行为已被弃用，并且当使用一个或多个这样的列时，服务器会显示适当的警告或错误。有关更多信息和示例，请参见不支持键分区的列索引前缀。

注意

使用NDB存储引擎的表隐式地通过KEY进行分区，使用表的主键作为分区键（与其他 MySQL 存储引擎一样）。如果 NDB Cluster 表没有显式主键，则由NDB存储引擎为每个 NDB Cluster 表生成的“隐藏”主键将用作分区键。

如果为NDB表定义了显式分区方案，则表必须具有显式主键，并且分区表达式中使用的任何列必须是该键的一部分。但是，如果表使用“空”分区表达式——即PARTITION BY KEY()而没有列引用，则不需要显式主键。

您可以使用ndb_desc实用程序（使用-p选项）观察到这种分区。

重要

对于使用键分区的表，您不能执行ALTER TABLE DROP PRIMARY KEY，因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table。对于使用KEY进行分区的 NDB Cluster 表，这不是问题；在这种情况下，表将使用“隐藏”的主键重新组织为表的新分区键。参见第二十五章，MySQL NDB Cluster 8.0。

也可以通过线性键对表进行分区。这里是一个简单的例子：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

LINEAR 关键字在 KEY 分区上具有与 HASH 分区相同的效果，分区号是使用二的幂算法而不是模算术推导出来的。参见第 26.2.4.1 节，“线性哈希分区”，了解该算法及其影响的描述。

26.2.6 子分区化

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html

子分区化，也称为复合分区化，是对分区表中每个分区的进一步划分。考虑以下CREATE TABLE语句：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

表ts有 3 个RANGE分区。这些分区——p0、p1和p2——进一步划分为 2 个子分区。实际上，整个表被划分为3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，前两个仅存储purchased列中值小于 1990 的记录。

可以对按RANGE或LIST分区的表进行子分区化。子分区可以使用HASH或KEY分区。这也称为复合分区化。

注意

SUBPARTITION BY HASH和SUBPARTITION BY KEY通常遵循与PARTITION BY HASH和PARTITION BY KEY相同的语法规则。一个例外是，SUBPARTITION BY KEY（不像PARTITION BY KEY）目前不支持默认列，因此必须指定用于此目的的列，即使表具有显式主键。这是一个我们正在努力解决的已知问题；有关更多信息和示例，请参见子分区的问题。

还可以使用SUBPARTITION子句明确定义子分区，以指定各个子分区的选项。例如，以更冗长的方式创建与前面示例中所示的相同表ts的方法如下：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

这里列出了一些需要注意的语法项：

每个分区必须具有相同数量的子分区。

如果您在分区表的任何分区上明确定义了任何子分区，必须定义它们全部。换句话说，以下语句将失败：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    );

即使使用SUBPARTITIONS 2，此语句仍将失败。

每个SUBPARTITION子句必须至少包括一个子分区的名称。否则，您可以为子分区设置任何所需选项，或允许其假定该选项的默认设置。

子分区名称必须在整个表中是唯一的。例如，以下CREATE TABLE语句是有效的：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

26.2.7 MySQL 分区如何处理 NULL

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-handling-nulls.html

MySQL 中的分区对于将NULL作为分区表达式的值（无论是列值还是用户提供的表达式的值）并不做任何限制。即使允许将NULL用作必须产生整数的表达式的值，但重要的是要记住NULL不是一个数字。MySQL 的分区实现将NULL视为小于任何非NULL值，就像ORDER BY一样。

这意味着NULL的处理在不同类型的分区之间有所不同，并且如果您没有为此做好准备，可能会产生您意想不到的行为。在这种情况下，我们在本节中讨论了每种 MySQL 分区类型在确定应将行存储在哪个分区时如何处理NULL值，并为每种情况提供了示例。

使用 RANGE 分区处理 NULL。 如果您向由RANGE分区的表插入一行，使得用于确定分区的列值为NULL，则该行将插入到最低的分区中。考虑以下在名为p的数据库中创建的两个表：

mysql> CREATE TABLE t1 (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY RANGE(c1) (
 ->     PARTITION p0 VALUES LESS THAN (0),
 ->     PARTITION p1 VALUES LESS THAN (10),
 ->     PARTITION p2 VALUES LESS THAN MAXVALUE
 -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY RANGE(c1) (
 ->     PARTITION p0 VALUES LESS THAN (-5),
 ->     PARTITION p1 VALUES LESS THAN (0),
 ->     PARTITION p2 VALUES LESS THAN (10),
 ->     PARTITION p3 VALUES LESS THAN MAXVALUE
 -> );
Query OK, 0 rows affected (0.09 sec)

通过以下查询INFORMATION_SCHEMA数据库中的PARTITIONS表，您可以看到这两个CREATE TABLE语句创建的分区：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |           0 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          0 |              0 |           0 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

（有关此表的更多信息，请参见第 28.3.21 节，“The INFORMATION_SCHEMA PARTITIONS Table”。）现在让我们用包含在用作分区键的列中的NULL的单行填充这些表，并验证使用一对SELECT语句插入了这些行：

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

您可以通过重新运行针对INFORMATION_SCHEMA.PARTITIONS的上一个查询并检查输出来查看用于存储插入行的分区：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| t1         | p0             |          1 |             20 |          20 |*
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
*| t2         | p0             |          1 |             20 |          20 |*
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

您还可以通过删除这些分区，然后重新运行SELECT语句来演示这些行存储在每个表的最低编号分区中：

mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

（有关ALTER TABLE ... DROP PARTITION的更多信息，请参见第 15.1.9 节，“ALTER TABLE Statement”。）

对于使用 SQL 函数的分区表达式，NULL也以这种方式处理。假设我们使用类似于以下的CREATE TABLE语句定义一个表：

CREATE TABLE tndate (
    id INT,
    dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

与其他 MySQL 函数一样，YEAR(NULL)返回NULL。具有NULL值的dt列行被视为分区表达式评估为低于任何其他值的值，因此被插入到分区p0中。

使用 LIST 分区处理 NULL 值。 通过LIST分区的表仅在其中一个分区使用包含NULL的值列表定义时才允许NULL值。相反，通过LIST分区的表如果在值列表中没有明确使用NULL，则拒绝导致分区表达式产生NULL值的行，如下例所示：

mysql> CREATE TABLE ts1 (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY LIST(c1) (
 ->     PARTITION p0 VALUES IN (0, 3, 6),
 ->     PARTITION p1 VALUES IN (1, 4, 7),
 ->     PARTITION p2 VALUES IN (2, 5, 8)
 -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9 
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

只有c1值在0和8之间（包括 0 和 8）的行才能插入到ts1中。NULL不在此范围内，就像数字9一样。我们可以创建包含NULL值列表的ts2和ts3表，如下所示：

mysql> CREATE TABLE ts2 (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY LIST(c1) (
 ->     PARTITION p0 VALUES IN (0, 3, 6),
 ->     PARTITION p1 VALUES IN (1, 4, 7),
 ->     PARTITION p2 VALUES IN (2, 5, 8),
 ->     PARTITION p3 VALUES IN (NULL)
 -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts3 (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY LIST(c1) (
 ->     PARTITION p0 VALUES IN (0, 3, 6),
 ->     PARTITION p1 VALUES IN (1, 4, 7, NULL),
 ->     PARTITION p2 VALUES IN (2, 5, 8)
 -> );
Query OK, 0 rows affected (0.01 sec)

在为分区定义值列表时，您可以（也应该）将NULL视为任何其他值一样对待。例如，VALUES IN (NULL)和VALUES IN (1, 4, 7, NULL)都是有效的，就像VALUES IN (1, NULL, 4, 7)，VALUES IN (NULL, 1, 4, 7)等一样。您可以将具有c1列为NULL的行插入到ts2和ts3中：

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

通过针对INFORMATION_SCHEMA.PARTITIONS发出适当的查询，您可以确定刚刚插入的行使用了哪些分区进行存储（我们假设，与前面的示例一样，分区表是在p数据库中创建的）：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2        | p0             |          0 |              0 |           0 |
| ts2        | p1             |          0 |              0 |           0 |
| ts2        | p2             |          0 |              0 |           0 |
*| ts2        | p3             |          1 |             20 |          20 |*
| ts3        | p0             |          0 |              0 |           0 |
*| ts3        | p1             |          1 |             20 |          20 |*
| ts3        | p2             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

正如本节前面所示，您还可以通过删除这些分区并执行SELECT来验证用于存储行的分区。

使用 HASH 和 KEY 分区处理 NULL 值。 对于使用HASH或KEY分区的表，NULL的处理略有不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零。我们可以通过创建一个使用适当值的记录的HASH分区表并查看其对文件系统的影响来验证这种行为。假设您使用以下语句创建了一个名为th的表（也在p数据库中）：

mysql> CREATE TABLE th (
 ->     c1 INT,
 ->     c2 VARCHAR(20)
 -> )
 -> PARTITION BY HASH(c1)
 -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

可以使用以下查询查看属于该表的分区：

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |           0 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

每个分区的TABLE_ROWS为 0。现在向th插入两行，这两行的c1列值分别为NULL和 0，并验证这些行是否已插入，如下所示：

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

对于任意整数*N*，NULL MOD *N*的值始终为NULL。对于按HASH或KEY分区的表，此结果被视为确定正确分区的0。再次检查信息模式PARTITIONS表，我们可以看到两行都被插入到分区p0中：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| th         | p0             |          2 |             20 |          20 |*
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

通过在表的定义中使用PARTITION BY KEY替换PARTITION BY HASH来重复上一个示例，您可以验证对于这种类型的分区，NULL也被视为 0。

26.3 分区管理

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-management.html

26.3.1 RANGE 和 LIST 分区管理

26.3.2 HASH 和 KEY 分区管理

26.3.3 与表交换分区和子分区

26.3.4 分区维护

26.3.5 获取分区信息

有多种使用 SQL 语句修改分区表的方法；可以通过使用分区扩展来添加、删除、重新定义、合并或拆分现有分区来修改分区表，这些操作都是通过 ALTER TABLE 语句完成的。还有一些方法可以获取关于分区表和分区的信息。我们将在接下来的章节中讨论这些主题。

有关在按 RANGE 或 LIST 分区的表中进行分区管理的信息，请参见第 26.3.1 节，“RANGE 和 LIST 分区的管理”。
有关管理 HASH 和 KEY 分区的讨论，请参见第 26.3.2 节，“HASH 和 KEY 分区的管理”。
请参见第 26.3.5 节，“获取分区信息”，了解 MySQL 8.0 提供的用于获取关于分区表和分区信息的机制。
有关对分区执行维护操作的讨论，请参见第 26.3.4 节，“分区维护”。

注意

所有分区表的分区必须具有相同数量的子分区；一旦表被创建，就无法更改子分区。

要更改表的分区方案，只需使用带有 partition_options 选项的 ALTER TABLE 语句，其语法与用于创建分区表的 CREATE TABLE 相同；这个选项总是以关键字 PARTITION BY 开头。假设以下 CREATE TABLE 语句用于创建一个按范围分区的表：

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    );

要将此表重新分区，使其按键分为两个分区，使用 id 列值作为键的基础，可以使用以下语句：

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

这对表的结构具有与删除表并使用 CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; 重新创建相同的效果。

ALTER TABLE ... ENGINE = ...仅更改表使用的存储引擎，并保留表的分区方案不变。该语句仅在目标存储引擎提供分区支持时成功。您可以使用ALTER TABLE ... REMOVE PARTITIONING来移除表的分区；参见 Section 15.1.9, “ALTER TABLE Statement”。

重要提示

在给定的ALTER TABLE语句中只能使用单个PARTITION BY、ADD PARTITION、DROP PARTITION、REORGANIZE PARTITION或COALESCE PARTITION子句。如果您（例如）希望删除一个分区并重新组织表的其余分区，您必须在两个单独的ALTER TABLE语句中执行此操作（一个使用DROP PARTITION，然后第二个使用REORGANIZE PARTITION）。

你可以使用ALTER TABLE ... TRUNCATE PARTITION从一个或多个选定的分区中删除所有行。

26.3.1 范围和列表分区的管理

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html

添加和删除范围和列表分区的操作方式类似，因此我们在本节讨论了这两种分区管理方式。有关处理按哈希或键分区的表的信息，请参阅第 26.3.2 节，“HASH 和 KEY 分区的管理”。

通过使用带有 DROP PARTITION 选项的 ALTER TABLE 语句，可以删除按 RANGE 或 LIST 分区的表中的分区。假设您已创建了一个按范围分区的表，然后使用以下 CREATE TABLE 和 INSERT 语句插入了 10 条记录：

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
 ->     PARTITION BY RANGE( YEAR(purchased) ) (
 ->         PARTITION p0 VALUES LESS THAN (1990),
 ->         PARTITION p1 VALUES LESS THAN (1995),
 ->         PARTITION p2 VALUES LESS THAN (2000),
 ->         PARTITION p3 VALUES LESS THAN (2005),
 ->         PARTITION p4 VALUES LESS THAN (2010),
 ->         PARTITION p5 VALUES LESS THAN (2015)
 ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
 ->     (1, 'desk organiser', '2003-10-15'),
 ->     (2, 'alarm clock', '1997-11-05'),
 ->     (3, 'chair', '2009-03-10'),
 ->     (4, 'bookcase', '1989-01-10'),
 ->     (5, 'exercise bike', '2014-05-09'),
 ->     (6, 'sofa', '1987-06-05'),
 ->     (7, 'espresso maker', '2011-11-22'),
 ->     (8, 'aquarium', '1992-08-04'),
 ->     (9, 'study desk', '2006-09-16'),
 ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以查看应该插入到分区 p2 中的项目，如下所示：

mysql> SELECT * FROM tr
 ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

您还可以使用分区选择获取此信息，如下所示：

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

更多信息请参见第 26.5 节，“分区选择”。

要删除名为 p2 的分区，请执行以下命令：

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

注意

NDBCLUSTER 存储引擎不支持 ALTER TABLE ... DROP PARTITION。但是，它支持本章中描述的与分区相关的其他 ALTER TABLE 扩展。

非常重要的一点是，当您删除一个分区时，也会删除存储在该分区中的所有数据。通过重新运行先前的 SELECT 查询，您可以看到这一点：

mysql> SELECT * FROM tr WHERE purchased
 -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

注意

DROP PARTITION 受本地分区就地 API 支持，可与 ALGORITHM={COPY|INPLACE} 一起使用。使用 ALGORITHM=INPLACE 的 DROP PARTITION 删除存储在分区中的数据并删除该分区。但是，使用 ALGORITHM=COPY 或 old_alter_table=ON 的 DROP PARTITION 会重建分区表，并尝试将无法移动到另一个具有兼容 PARTITION ... VALUES 定义的分区的数据移动到另一个分区。无法移动到另一个分区的数据将被删除。

因此，在执行 ALTER TABLE ... DROP PARTITION 之前，您必须具有表的 DROP 权限。

如果您希望删除所有分区中的所有数据，同时保留表定义及其分区方案，请使用TRUNCATE TABLE语句。（请参阅第 15.1.37 节，“TRUNCATE TABLE Statement”。）

如果您打算更改表的分区而不丢失数据，请使用ALTER TABLE ... REORGANIZE PARTITION。有关REORGANIZE PARTITION的信息，请参见下文或第 15.1.9 节，“ALTER TABLE Statement”。

如果现在执行SHOW CREATE TABLE语句，您可以看到表的分区结构已经发生了变化：

mysql> SHOW CREATE TABLE tr\G
*************************** 1\. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

当你在更改后的表中插入具有purchased列值在'1995-01-01'和'2004-12-31'之间（包括这两个日期）的新行时，这些行将存储在分区p3中。您可以按照以下步骤验证这一点：

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
 -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased
 -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

由于服务器不会像等效的DELETE查询那样报告由于ALTER TABLE ... DROP PARTITION而从表中删除的行数。

删除LIST分区与删除RANGE分区使用完全相同的ALTER TABLE ... DROP PARTITION语法。然而，这对之后对表的使用有一个重要的区别：您不能再向表中插入具有被删除分区定义的值列表中的任何值的行。（请参阅第 26.2.2 节，“LIST 分区”，以获取示例。）

要向先前分区的表添加新的范围或列表分区，请使用ALTER TABLE ... ADD PARTITION语句。对于按RANGE分区的表，这可以用于在现有分区列表的末尾添加新的范围。假设您有一个包含组织成员数据的分区表，其定义如下：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

进一步假设成员的最小年龄为 16 岁。随着日历接近 2015 年底，您意识到必须很快准备好接纳 2000 年（及以后）出生的成员。您可以修改members表以适应在 2000 年至 2010 年出生的新成员，如下所示：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

对于按范围分区的表，您可以使用ADD PARTITION仅向分区列表的高端添加新分区。尝试以这种方式在现有分区之间或之前添加新分区会导致错误，如下所示：

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

您可以通过将第一个分区重新组织为两个新分区，将它们之间的范围分割，来解决这个问题，就像这样：

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

使用SHOW CREATE TABLE，您可以看到ALTER TABLE语句已经产生了预期的效果：

mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

另请参阅 Section 15.1.9.1, “ALTER TABLE Partition Operations”。

您还可以使用ALTER TABLE ... ADD PARTITION来向由LIST分区的表中添加新分区。假设一个表tt是使用以下CREATE TABLE语句定义的：

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

您可以添加一个新分区，用于存储具有data列值7、14和21的行，如下所示：

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

请记住，不能添加一个新的LIST分区，其中包含已经包含在现有分区值列表中的任何值。如果尝试这样做，将导致错误：

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

因为具有data列值12的任何行已经分配给了分区p1，所以无法在表tt上创建一个包含12在其值列表中的新分区。为了实现这一点，您可以删除p1，然后添加np，然后一个新的p1，并修改定义。然而，正如前面讨论的，这将导致所有存储在p1中的数据丢失，而且通常情况下这并不是您真正想要做的。另一个解决方案可能是制作一个具有新分区的表的副本，并使用CREATE TABLE ... SELECT ...将数据复制到其中，然后删除旧表并重命名新表，但是在处理大量数据时可能非常耗时。在需要高可用性的情况下，这也可能不可行。

您可以在单个ALTER TABLE ... ADD PARTITION语句中添加多个分区，如下所示：

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸运的是，MySQL 的分区实现提供了重新定义分区而不丢失数据的方法。让我们首先看一下涉及RANGE分区的几个简单示例。回想一下现在定义如下的members表：

mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

假设您想将所有出生在 1960 年之前的会员行移动到一个单独的分区中。正如我们已经看到的，这不能通过ALTER TABLE ... ADD PARTITION来实现。然而，您可以使用另一个与分区相关的扩展来ALTER TABLE来完成这个任务：

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

实际上，这个命令将分区p0分割成两个新分区s0和s1。它还根据两个PARTITION ... VALUES ...子句中体现的规则，将存储在p0中的数据移动到新分区中，因此s0只包含那些YEAR(dob)小于 1960 的记录，而s1包含那些YEAR(dob)大于或等于 1960 但小于 1970 的行。

REORGANIZE PARTITION子句也可用于合并相邻分区。您可以撤销对members表的上一个语句的影响，如下所示：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

使用REORGANIZE PARTITION拆分或合并分区时不会丢失任何数据。在执行上述语句时，MySQL 将所有存储在分区s0和s1中的记录移动到分区p0中。

REORGANIZE PARTITION的一般语法如下所示：

ALTER TABLE *tbl_name*
    REORGANIZE PARTITION *partition_list*
    INTO (*partition_definitions*);

这里，*tbl_name是分区表的名称，partition_list是要更改的一个或多个现有分区的名称的逗号分隔列表。partition_definitions是一个逗号分隔的新分区定义列表，遵循与CREATE TABLE中使用的partition_definitions*列表相同的规则。在使用REORGANIZE PARTITION时，您不限于将多个分区合并为一个，或将一个分区分割为多个。例如，您可以将members表的四个分区重新组织为两个，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

您还可以在按LIST进行分区的表上使用REORGANIZE PARTITION。让我们回到向列表分区的tt表添加新分区的问题，并因为新分区的值已经存在于现有分区的值列表中而失败。我们可以通过添加一个仅包含非冲突值的分区，然后重新组织新分区和现有分区，使存储在现有分区中的值现在移动到新分区来处理这个问题：

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

在使用ALTER TABLE ... REORGANIZE PARTITION重新分区按RANGE或LIST进行分区的表时，请记住以下要点：

用于确定新分区方案的PARTITION选项受与CREATE TABLE语句相同的规则约束。

新的RANGE分区方案不能有任何重叠的范围；新的LIST分区方案不能有任何重叠的值集。
*partition_definitions列表中的分区组合应该总体上与partition_list*中命名的组合分区涵盖相同的范围或值集。

例如，在本节示例中使用的members表中，分区p1和p2一起涵盖 1980 年至 1999 年的年份。对这两个分区的任何重新组织应该总体上涵盖相同的年份范围。
对于按RANGE进行分区的表，您只能重新组织相邻分区；您不能跳过范围分区。

例如，您不能使用以ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...开头的语句重新组织示例members表，因为p0涵盖 1970 年之前的年份，而p2涵盖 1990 年至 1999 年的年份，因此这些不是相邻的分区。（在这种情况下，您不能跳过分区p1。）
你不能使用REORGANIZE PARTITION来改变表使用的分区类型（例如，你不能将RANGE分区更改为HASH分区或反之）。你也不能使用这个语句来更改分区表达式或列。要完成这两项任务而不必删除和重新创建表，你可以使用ALTER TABLE ... PARTITION BY ...，如下所示：
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```

26.3.2 哈希和键分区的管理

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-management-hash-key.html

使用哈希或键分区的表在修改分区设置方面非常相似，与按范围或列表分区的表在许多方面不同。因此，本节仅讨论了对使用哈希或键分区的表进行修改。有关对按范围或列表分区的表添加和删除分区的讨论，请参见第 26.3.1 节，“范围和列表分区的管理”。

与可以从按RANGE或LIST分区的表中删除分区的方式不同，您无法像从中删除分区一样从按HASH或KEY分区的表中删除分区。但是，您可以使用ALTER TABLE ... COALESCE PARTITION合并HASH或KEY分区。假设一个包含有关客户数据的clients表被分成了 12 个分区，如下所示创建：

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

要将分区数从 12 减少到 8，请执行以下ALTER TABLE语句：

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)

COALESCE在使用HASH、KEY、LINEAR HASH或LINEAR KEY分区的表上同样有效。以下是一个类似于前一个示例的示例，唯一不同之处在于表是通过LINEAR KEY分区的：

mysql> CREATE TABLE clients_lk (
 ->     id INT,
 ->     fname VARCHAR(30),
 ->     lname VARCHAR(30),
 ->     signed DATE
 -> )
 -> PARTITION BY LINEAR KEY(signed)
 -> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

COALESCE PARTITION后面的数字是要合并到剩余部分中的分区数，换句话说，要从表中删除的分区数。

尝试删除比表中存在的分区更多的分区会导致如下错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

要将clients表的分区数从 12 增加到 18，请使用如下ALTER TABLE ... ADD PARTITION：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

26.3.3 与表交换分区和子分区

原文：dev.mysql.com/doc/refman/8.0/en/partitioning-management-exchange.html

在 MySQL 8.0 中，可以使用ALTER TABLE *pt* EXCHANGE PARTITION *p* WITH TABLE *nt*来交换表分区或子分区与未分区表*nt，其中pt是分区表，p是要与未分区表nt交换的pt*的分区或子分区，前提是以下陈述为真：

表*nt*本身没有分区。
表*nt*不是临时表。
表*pt和nt*的结构在其他方面是相同的。
表nt不包含外键引用，也没有其他表有任何外键引用指向nt。
*nt中没有位于p*的分区定义边界之外的行。如果使用WITHOUT VALIDATION，则不适用此条件。
两个表必须使用相同的字符集和校对规则。
对于InnoDB表，两个表必须使用相同的行格式。要确定InnoDB表的行格式，请查询INFORMATION_SCHEMA.INNODB_TABLES。
任何分区级别的MAX_ROWS设置对于p必须与为nt设置的表级别MAX_ROWS值相同。对于p的任何分区级别的MIN_ROWS设置也必须与为nt设置的表级别MIN_ROWS值相同。

无论pt是否具有显式的表级别MAX_ROWS或MIN_ROWS选项生效，这在任何情况下都是正确的。
AVG_ROW_LENGTH在表pt和表nt之间不能有差异。
表pt不能有任何使用DATA DIRECTORY选项的分区。这个限制在 MySQL 8.0.14 及更高版本中对InnoDB表解除。
INDEX DIRECTORY在表和要与之交换的分区之间不能有差异。
任何表或分区TABLESPACE选项都不能在任何表中使用。

除了通常需要的ALTER、INSERT和CREATE权限外，您必须具有DROP权限才能执行ALTER TABLE ... EXCHANGE PARTITION。

您还应该了解ALTER TABLE ... EXCHANGE PARTITION的以下影响：

执行ALTER TABLE ... EXCHANGE PARTITION不会触发分区表或要交换表上的任何触发器。
交换表中的任何AUTO_INCREMENT列都会被重置。
使用ALTER TABLE ... EXCHANGE PARTITION时，IGNORE关键字不起作用。

ALTER TABLE ... EXCHANGE PARTITION的语法如下，其中*pt是分区表，p是要交换的分区（或子分区），nt是要与p*交换的非分区表：

ALTER TABLE *pt*
    EXCHANGE PARTITION *p*
    WITH TABLE *nt*;

可选地，你可以附加WITH VALIDATION或WITHOUT VALIDATION。当指定WITHOUT VALIDATION时，ALTER TABLE ... EXCHANGE PARTITION 操作在交换分区到非分区表时不执行逐行验证，允许数据库管理员承担确保行在分区定义边界内的责任。WITH VALIDATION是默认选项。

在单个ALTER TABLE EXCHANGE PARTITION语句中，只能将一个分区或子分区与一个非分区表交换。要交换多个分区或子分区，请使用多个ALTER TABLE EXCHANGE PARTITION语句。EXCHANGE PARTITION不能与其他ALTER TABLE选项结合使用。分区表使用的分区和（如果适用）子分区可以是 MySQL 8.0 支持的任何类型。

与非分区表交换分区

假设已经使用以下 SQL 语句创建和填充了分区表e：

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

现在我们创建一个名为e2的非分区副本e。可以使用mysql客户端来完成，如下所示：

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

通过查询信息模式PARTITIONS表，你可以看到表e中包含行的分区，就像这样：

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

注意

对于分区InnoDB表，信息模式PARTITIONS表中TABLE_ROWS列中给出的行数仅是 SQL 优化中使用的估计值，并不总是准确的。

要交换表e中的分区p0与表e2，可以使用ALTER TABLE，如下所示：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)

更准确地说，刚刚执行的语句导致在分区中找到的任何行与表中找到的行交换。你可以通过再次查询信息模式PARTITIONS表来观察这是如何发生的。之前在分区p0中找到的表行不再存在：

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

如果查询表e2，你会发现“缺失”的行现在可以在那里找到：

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

与分区交换的表不一定要为空。为了演示这一点，我们首先向表e插入一行新数据，确保这行数据存储在分区p0中，方法是选择一个小于 50 的id列值，并在之后通过查询PARTITIONS表进行验证：

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';            
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

现在我们再次使用与之前相同的ALTER TABLE语句交换分区p0与表e2：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

以下查询的输出显示，在发出ALTER TABLE语句之前存储在分区p0中的表行和存储在表e2中的表行现在已经交换位置：

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

不匹配的行

请记住，在发出ALTER TABLE ... EXCHANGE PARTITION语句之前，在非分区表中找到的任何行必须满足存储在目标分区中的条件；否则，该语句将失败。为了看到这是如何发生的，首先向e2插入一行数据，该行数据超出了表e的分区p0的定义范围。例如，插入一个id列值过大的行；然后，再次尝试与分区交换表：

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

只有WITHOUT VALIDATION选项才能使此操作成功：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

当将一个分区与包含不符合分区定义的行的表交换时，由数据库管理员负责修复不匹配的行，可以使用REPAIR TABLE或ALTER TABLE ... REPAIR PARTITION来执行。

不需要逐行验证即可交换分区

当将一个分区与包含许多行的表交换时，为了避免耗时的验证，可以在ALTER TABLE ... EXCHANGE PARTITION语句中添加WITHOUT VALIDATION来跳过逐行验证步骤。

以下示例比较了与和不带验证时交换分区与非分区表的执行时间差异。分区表（表e）包含两个各有 100 万行的分区。表e的 p0 中的行被移除，并且 p0 与一个有 100 万行的非分区表交换。WITH VALIDATION操作耗时 0.74 秒。相比之下，WITHOUT VALIDATION操作只需 0.01 秒。

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)

# Confirm that the partition was exchanged with table e2

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

如果将一个分区与包含不符合分区定义的行的表进行交换，数据库管理员有责任修复不匹配的行，可以使用REPAIR TABLE或ALTER TABLE ... REPAIR PARTITION来执行此操作。

用非分区表交换子分区

你也可以使用ALTER TABLE ... EXCHANGE PARTITION语句，将分区表的一个子分区（参见第 26.2.6 节，“子分区”）与非分区表进行交换。在下面的示例中，我们首先创建一个按RANGE分区并按KEY子分区的表es，像我们创建表e一样填充这个表，然后创建一个空的、非分区的副本es2，如下所示：

mysql> CREATE TABLE es (
 ->     id INT NOT NULL,
 ->     fname VARCHAR(30),
 ->     lname VARCHAR(30)
 -> )
 ->     PARTITION BY RANGE (id)
 ->     SUBPARTITION BY KEY (lname)
 ->     SUBPARTITIONS 2 (
 ->         PARTITION p0 VALUES LESS THAN (50),
 ->         PARTITION p1 VALUES LESS THAN (100),
 ->         PARTITION p2 VALUES LESS THAN (150),
 ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
 ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
 ->     (1669, "Jim", "Smith"),
 ->     (337, "Mary", "Jones"),
 ->     (16, "Frank", "White"),
 ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

虽然我们在创建表es时没有明确命名任何子分区，但我们可以通过在从INFORMATION_SCHEMA中的PARTITIONS表中选择时包含SUBPARTITION_NAME列来获取这些子分区的生成名称，如下所示：

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
 ->     FROM INFORMATION_SCHEMA.PARTITIONS
 ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

以下ALTER TABLE语句将表es中的子分区p3sp0与非分区表es2进行交换：

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

你可以通过发出以下查询来验证行是否已经交换：

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
 ->     FROM INFORMATION_SCHEMA.PARTITIONS
 ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

如果表被子分区，你只能交换表的一个子分区，而不是整个分区，如下所示：

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

表结构严格比较；分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。此外，两个表必须使用相同的存储引擎：

mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1\. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL