本节讨论 MySQL 8.4 中可用的分区类型。这些包括以下列出的类型：This section discusses the types of partitioning which are available in MySQL 8.4. These include the types listed here:

数据库分区的一种非常常见的用途是按日期分隔数据。一些数据库系统支持显式的日期分区，而 MySQL 在 8.4 版本中未实现。然而，在 MySQL 中根据 DATE、TIME 或 DATETIME 列创建分区方案，或者基于使用这些列的表达式并不困难。A very common use of database partitioning is to segregate data by date. Some database systems support explicit date partitioning, which MySQL does not implement in 8.4. However, it is not difficult in MySQL to create partitioning schemes based on DATE, TIME, or DATETIME columns, or based on expressions making use of such columns.

当按 KEY 或 LINEAR KEY 进行分区时，你可以使用 DATE、TIME 或 DATETIME 列作为分区列，而无需对列值进行任何修改。例如，这条表创建语句在 MySQL 中是完全有效的：When partitioning by KEY or LINEAR KEY, you can use a DATE, TIME, or DATETIME column as the partitioning column without performing any modification of the column value. For example, this table creation statement is perfectly valid in 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.4 中，也可以使用 DATE 或 DATETIME 列作为分区列，使用 RANGE COLUMNS 和 LIST COLUMNS 分区。In MySQL 8.4, it is also possible to use a DATE or DATETIME column as the partitioning column using RANGE COLUMNS and LIST COLUMNS partitioning.

其他分区类型需要一个返回整数值或 NULL 的分区表达式。如果您希望使用基于日期的 RANGE、LIST、HASH 或 LINEAR HASH 分区，您可以简单地使用一个对 DATE、TIME 或 DATETIME 列进行操作并返回该值的函数，如下所示：Other partitioning types require a partitioning expression that yields an integer value or NULL. If you wish to use date-based partitioning by RANGE, LIST, HASH, or LINEAR HASH, you can simply employ a function that operates on a DATE, TIME, or DATETIME column and returns such a value, as shown here:

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
);

关于使用日期进行分区的更多示例，可以在本章的以下部分中找到：Additional examples of partitioning using dates may be found in the following sections of this chapter:

有关基于日期的更复杂的分区示例，请参见以下章节：For more complex examples of date-based partitioning, see the following sections:

MySQL 分区优化用于与 TO_DAYS()、YEAR() 和 TO_SECONDS() 函数一起使用。然而，您也可以使用返回整数或 NULL 的其他日期和时间函数，例如 WEEKDAY()、DAYOFYEAR() 或 MONTH()。有关此类函数的更多信息，请参见 第 14.7 节，“日期和时间函数”。MySQL partitioning is optimized for use with the TO_DAYS(), YEAR(), and TO_SECONDS() functions. However, you can use other date and time functions that return an integer or NULL, such as WEEKDAY(), DAYOFYEAR(), or MONTH(). See Section 14.7, “Date and Time Functions”, for more information about such functions.

重要的是要记住——无论您使用哪种类型的分区——分区在创建时总是会自动按顺序编号，从 0 开始。当向分区表中插入新行时，正是这些分区号用于识别正确的分区。例如，如果您的表使用 4 个分区，这些分区的编号分别是 0、1、2 和 3。对于 RANGE 和 LIST 分区类型，有必要确保每个分区号码都有定义的分区。对于 HASH 分区，用户提供的表达式必须求值为整数值。对于 KEY 分区，这个问题会由 MySQL 服务器内部使用的哈希函数自动处理。It is important to remember—regardless of the type of partitioning that you use—that partitions are always numbered automatically and in sequence when created, starting with 0. When a new row is inserted into a partitioned table, it is these partition numbers that are used in identifying the correct partition. For example, if your table uses 4 partitions, these partitions are numbered 0, 1, 2, and 3. For the RANGE and LIST partitioning types, it is necessary to ensure that there is a partition defined for each partition number. For HASH partitioning, the user-supplied expression must evaluate to an integer value. For KEY partitioning, this issue is taken care of automatically by the hashing function which the MySQL server employs internally.

分区的名称通常遵循其他 MySQL 标识符的规则，例如表和数据库的规则。然而，你应注意分区名称不区分大小写。例如，下面的 CREATE TABLE 语句失败，如下所示：Names of partitions generally follow the rules governing other MySQL identifiers, such as those for tables and databases. However, you should note that partition names are not case-sensitive. For example, the following CREATE TABLE statement fails as shown:

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 没有区别。Failure occurs because MySQL sees no difference between the partition names mypart and MyPart.

当你为表指定分区数时，这必须表示为一个正的非零整数常量，且不能有前导零，也不能是像 0.8E+01 或 6-2 这样的表达式，即使它的计算结果是整数。小数分数是不允许的。When you specify the number of partitions for the table, this must be expressed as a positive, nonzero integer literal with no leading zeros, and may not be an expression such as 0.8E+01 or 6-2, even if it evaluates to an integer value. Decimal fractions are not permitted.

在接下来的部分中，我们不一定提供用于创建每种分区类型的语法的所有可能形式；有关此信息，请参见 第 15.1.20 节，“CREATE TABLE 语句”。In the sections that follow, we do not necessarily provide all possible forms for the syntax that can be used for creating each partition type; for this information, see Section 15.1.20, “CREATE TABLE Statement”.