22.2.1 RANGE 分区

按范围分区的 table 的分区方式是，每个分区都包含行，分区 table 达式的值在给定范围内。范围应该是连续的，但不能重叠，并使用VALUES LESS THAN运算符定义。对于接下来的几个示例，假设您正在创建一个诸如以下的 table 格来保存 20 个视频 Store(编号为 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
);

Note

此处使用的employeestable 没有主键或唯一键。尽管出于本讨论的目的而显示了示例，但您应记住，table 在实践中极有可能具有主键，唯一键或同时具有这两者，并且分区列的允许选择取决于用于这些列的列键(如果有)。有关这些问题的讨论，请参见第 22.6.1 节“分区键，主键和唯一键”。

可以根据需要以多种方式对该 table 进行分区。一种方法是使用store_id列。例如，您可能决定通过添加PARTITION BY RANGE子句来对 table 进行 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)
);

在此分区方案中，与在 Store1 到 5 上工作的雇员相对应的所有行都存储在分区p0中，而在 Store6 到 10 处所用的那些行则存储在分区p1中，依此类推。请注意，每个分区的定义 Sequences 是从最低到最高。这是PARTITION BY RANGE语法的要求；在这方面，您可以认为它类似于 C 或 Java 中的if ... elseif ...语句系列。

很容易确定将包含数据(72, 'Mitchell', 'Wilson', '1998-06-25', NULL, 13)的新行插入到分区p2中，但是当您的链添加第 21 家 Store 时会发生什么？在这种方案下，没有规则覆盖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
);

Note

当找不到匹配值时，避免错误的另一种方法是使用IGNORE关键字作为INSERT语句的一部分。有关示例，请参见第 22.2.2 节“列 table 分区”。有关IGNORE的一般信息，另请参见第 13.2.5 节“ INSERT 语句”。

MAXVALUEtable 示一个整数值，该值始终大于可能的最大整数值(在 math 语言中，它用作最小上限)。现在，store_id列值大于或等于 16(定义的最大值)的任何行都存储在分区p3中。在将来的某个时候(Store 数量增加到 25、30 或更多)，您可以使用ALTER TABLE语句为 21-25、26-30 等 Store 添加新分区(请参见第 22.3 节“分区 Management”，有关有关如何执行此操作的详细信息)。

您可以按照几乎相同的方式根据员工的工作代码(即基于job_code列值的范围)对 table 进行分区。例如，假设常规(店内)Worker 使用两位数字的工作代码，办公室和支持人员使用三位数字的代码，Management 职位使用四位数字的代码，则可以创建分区 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 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中，而与 Managers 相关的行将存储在p2分区中。

也可以在VALUES LESS THAN子句中使用 table 达式。但是，MySQL 必须能够在LESS THAN(<)比较中评估 table 达式的返回值。

您可以使用基于两个DATE列之一的 table 达式，而不是根据 Store 编号拆分 table 数据。例如，让我们假设您希望根据每个员工离开公司的年份进行划分；即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 年以后离开的任何 Worker，p3。

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

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值的 table 达式。 (请参见 Bug#42849.)

当满足以下一个或多个条件时，范围分区特别有用：

您想要或需要删除“旧”数据。如果使用的是前面针对employeestable 显示的分区方案，则只需使用ALTER TABLE employees DROP PARTITION p0;删除与 1991 年之前停止在公司工作的员工有关的所有行。(有关详细信息，请参阅第 13.1.8 节“ ALTER TABLE 语句”和第 22.3 节“分区 Management”。)具有很多行的 table，这比运行DELETE查询(例如DELETE FROM employees WHERE YEAR(separated) <= 1990;)要高效得多。
您要使用包含日期或时间值或包含其他系列值的列。
您经常运行直接取决于用于分区 table 的列的查询。例如，当执行诸如从在'2000-01-01'和'2000-12-31'GROUP BY store_id 之间分开的员工中解释选择计数(*)；之类的查询时，MySQL 可以快速确定仅需要扫描分区p2，因为其余分区不能包含满足WHERE子句的任何记录。有关如何完成此操作的更多信息，请参见第 22.4 节“分区修剪”。

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

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

用RANGE对 table 进行分区，对于分区 table 达式，采用对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 5.7 中，还可以使用UNIX_TIMESTAMP()函数根据TIMESTAMP列的值通过RANGE对 table 进行分区，如下例所示：

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 5.7 中，不允许使用其他任何包含TIMESTAMP值的 table 达式。 (请参见 Bug#42849.)

Note

在 MySQL 5.7 中，也可以将UNIX_TIMESTAMP(timestamp_column)用作由LIST分区的 table 的分区 table 达式。但是，这样做通常是不实际的。

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

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

Note

RANGE COLUMNS不支持使用除DATE或DATETIME以外的日期或时间类型的分区列。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35