按范围分区的表是这样分区的：每个分区包含分区表达式值在给定范围内的行。范围应当是连续的但不重叠，并使用 VALUES LESS THAN 运算符来定义。在接下来的几个示例中，假设您创建如下表格，以保存编号为 1 到 20 的二十家视频商店的人员记录:A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. For the next few examples, suppose that you are creating a table such as the following to hold personnel records for a chain of 20 video stores, numbered 1 through 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
);

这个表可以根据需求通过范围进行多种方式的分区。一种方式是使用 store_id 列。例如，你可以决定通过添加如下所示的 PARTITION BY RANGE 子句将表分为四个分区：This table can be partitioned by range in a number of ways, depending on your needs. One way would be to use the store_id column. For instance, you might decide to partition the table 4 ways by adding a PARTITION BY RANGE clause as shown here:

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 ... 语句。In this partitioning scheme, all rows corresponding to employees working at stores 1 through 5 are stored in partition p0, to those employed at stores 6 through 10 are stored in partition p1, and so on. Each partition is defined in order, from lowest to highest. This is a requirement of the PARTITION BY RANGE syntax; you can think of it as being analogous to a series of if ... elseif ... statements in C or Java in this regard.

It is easy to determine that a new row containing the data (72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13) is inserted into partition p2, but what happens when your chain adds a 21^st store? Under this scheme, there is no rule that covers a row whose store_id is greater than 20, so an error results because the server does not know where to place it. You can keep this from occurring by using a “catchall” VALUES LESS THAN clause in the CREATE TABLE statement that provides for all values greater than the highest value explicitly named:

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。）(As with the other examples in this chapter, we assume that the default storage engine is InnoDB.)

在未找到匹配值时避免错误的另一种方法是在INSERT语句中使用IGNORE关键字。有关示例，请参见第 26.2.2 节，“LIST 分区”。Another way to avoid an error when no matching value is found is to use the IGNORE keyword as part of the INSERT statement. For an example, see Section 26.2.2, “LIST Partitioning”.

MAXVALUE 表示一个总是大于可能出现的最大整数值的整数（在数学术语中，它作为一个 上确界）。现在，任何 store_id 列值大于或等于 16（定义的最大值）的行都存储在分区 p3 中。在未来的某个时候——当商店数量增加到 25、30 或更多时——您可以使用 ALTER TABLE 语句为商店 21-25、26-30 等添加新分区（有关如何操作的详细信息，请参见 第 26.3 节，“分区管理”）。MAXVALUE represents an integer value that is always greater than the largest possible integer value (in mathematical language, it serves as a least upper bound). Now, any rows whose store_id column value is greater than or equal to 16 (the highest value defined) are stored in partition p3. At some point in the future—when the number of stores has increased to 25, 30, or more—you can use an ALTER TABLE statement to add new partitions for stores 21-25, 26-30, and so on (see Section 26.3, “Partition Management”, for details of how to do this).

以类似的方式，你可以根据员工的职位代码对表进行分区——也就是基于 job_code 列值的范围。例如——假设两位数的职位代码用于常规（店内）员工，三位数代码用于办公室和支持人员，四位数代码用于管理职位——你可以使用以下语句创建分区表：In much the same fashion, you could partition the table based on employee job codes—that is, based on ranges of job_code column values. For example—assuming that two-digit job codes are used for regular (in-store) workers, three-digit codes are used for office and support personnel, and four-digit codes are used for management positions—you could create the partitioned table using the following statement:

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 中。In this instance, all rows relating to in-store workers would be stored in partition p0, those relating to office and support staff in p1, and those relating to managers in partition p2.

在 VALUES LESS THAN 子句中也可以使用表达式。然而，MySQL 必须能够将该表达式的返回值作为 LESS THAN（<）比较的一部分来评估。It is also possible to use an expression in VALUES LESS THAN clauses. However, MySQL must be able to evaluate the expression's return value as part of a LESS THAN (<) comparison.

与其根据商店编号拆分表数据，您可以改为使用基于两个 DATE 列之一的表达式。例如，假设您希望根据每位员工离职的年份进行分区；也就是说，YEAR(separated) 的值。下面是一个实现此类分区方案的 CREATE TABLE 语句示例：Rather than splitting up the table data according to store number, you can use an expression based on one of the two DATE columns instead. For example, let us suppose that you wish to partition based on the year that each employee left the company; that is, the value of YEAR(separated). An example of a CREATE TABLE statement that implements such a partitioning scheme is shown here:

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。In this scheme, for all employees who left before 1991, the rows are stored in partition p0; for those who left in the years 1991 through 1995, in p1; for those who left in the years 1996 through 2000, in p2; and for any workers who left after the year 2000, in p3.

也可以根据 TIMESTAMP 列的值，使用 UNIX_TIMESTAMP() 函数来按 RANGE 分区表，如以下示例所示：It is also possible to partition a table by RANGE, based on the value of a TIMESTAMP column, using the UNIX_TIMESTAMP() function, as shown in this example:

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。)Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)

当以下一个或多个条件为真时，范围分区特别有用：Range partitioning is particularly useful when one or more of the following conditions is true:

这种分区类型的一种变体是RANGE COLUMNS分区。通过RANGE COLUMNS进行分区可以使用多列来定义分区范围，这些范围既适用于行在分区中的放置，也适用于在执行分区裁剪时确定特定分区的包含或排除。更多信息请参见第 26.2.3.1 节，“RANGE COLUMNS 分区”。A variant on this type of partitioning is RANGE COLUMNS partitioning. Partitioning by RANGE COLUMNS makes it possible to employ multiple columns for defining partitioning ranges that apply both to placement of rows in partitions and for determining the inclusion or exclusion of specific partitions when performing partition pruning. See Section 26.2.3.1, “RANGE COLUMNS partitioning”, for more information.

基于时间间隔的分区方案。 如果您希望在 MySQL 8.4 中实现基于时间范围或间隔的分区方案，您有两种选择：Partitioning schemes based on time intervals.  If you wish to implement a partitioning scheme based on ranges or intervals of time in MySQL 8.4, you have two options: