按范围分区的表的分区方式是,每个分区都包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠的,并且使用值小于
操作符。在接下来的几个例子中,假设您正在创建一个如下所示的表,用于保存20家音像店(编号从1到20)的人员记录:
创建表雇员(id INT不空,fname VARCHAR(30), lname VARCHAR(30),雇用日期不空默认'1970-01-01',分离日期不空默认' 99999-12-31 ',job_code INT不空,store_id INT不空);
的员工
这里使用的表没有主键或唯一键。虽然为了当前讨论的目的,这些示例可以正常工作,但您应该记住,在实践中,表极有可能具有主键、唯一键或两者都有,分区列的允许选择取决于用于这些键的列(如果存在)。有关这些问题的讨论,请参见第24.6.1节“分区键、主键和唯一键”.
根据您的需要,可以通过多种方式对这个表进行范围分区。一种方法是使用可以
列。例如,您可能决定通过添加a来按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 ' 99999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES小于(6),PARTITION p1 VALUES小于(11),PARTITION p2 VALUES小于(16),PARTITION p3 VALUES小于(21));
在这种分区方案中,在存储1到存储5中工作的员工对应的所有行都存储在分区中p0
,在第6至10号店工作的员工,都存储在分区中p1
等等。每个分区按从低到高的顺序定义。这是一个要求按范围划分
语法;你可以把它想象成一系列如果……elseif……
在这方面,C或Java语句。
很容易确定包含数据的新行(72年,“米歇尔”,“威尔逊”,“1998-06-25”,NULL, 13)
被插入分区p2
,但是当链上加21时会发生什么圣商店?在这个方案中,没有规则覆盖一行可以
大于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 ' 99999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES小于(6),PARTITION p1 VALUES小于(11),PARTITION p2 VALUES小于(16),分区p3的值小于MAXVALUE);
(与本章中的其他例子一样,我们假设默认存储引擎是InnoDB
.)
在没有找到匹配值时避免错误的另一种方法是使用忽略
关键字的一部分插入
声明。有关示例,请参见第24.2.2节,“LIST分区”.也看到第13.2.6节,“INSERT语句”,以了解有关忽略
.
MAXVALUE
表示始终大于可能的最大整数值的整数值(在数学语言中,它用作最小上界).现在,任何行可以
列值大于或等于16(定义的最高值)存储在分区中p3
.在将来的某个时候——当商店的数量增加到25、30或更多时,您可以使用ALTER TABLE
语句为存储区21-25、26-30添加新分区,等等(请参见第24.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 ' 99999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (job_code) (PARTITION p0 VALUES小于(100),PARTITION p1 VALUES小于(1000),PARTITION p2 VALUES小于(10000));
在这种情况下,与存储中的工人相关的所有行都将存储在分区中p0
,与办公室及支援人员有关的p1
,以及与分区中的管理人员有关的内容p2
.
也可以使用in表达式值小于
条款。但是,MySQL必须能够计算表达式的返回值作为不到
(<
)比较。
您可以使用基于两者之一的表达式,而不是根据存储号分割表数据日期
列。例如,让我们假设您希望根据每个员工离开公司的年份进行划分;的值(分离)
.一个例子创建表
语句实现了这样一个分区方案,如下所示:
CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30),雇用日期非空默认'1970-01-01',分离日期非空默认' 99999-12-31 ',job_code INT, store_id INT)分区范围(年(分离))(分区p0值小于(1991),分区p1值小于(1996),分区p2值小于(2001),分区p3值小于MAXVALUE);
在这个方案中,对于1991年之前离职的所有员工,行都存储在分区中p0
;对于1991年至1995年离开的人,在p1
;1996年到2000年离开的人p2
;对于2000年以后离开的工人,在p3
.
也可以对表进行分区范围
,基于a的值时间戳
列,使用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小于(UNIX_TIMESTAMP('2008-01-01 00:00:00')), PARTITION p1 VALUES小于(UNIX_TIMESTAMP('2008-04-01 00:00:00')), PARTITION p2 VALUES小于(UNIX_TIMESTAMP('2008-07-01 00:00:00')),分区p3值小于(UNIX_TIMESTAMP(' 2008-01-01 00:00:00')),分区p4值小于(UNIX_TIMESTAMP('2009-01-01 00:00:00')),分区p5值小于(UNIX_TIMESTAMP('2009-04-01 00:00:00')),分区p6值小于(UNIX_TIMESTAMP('2009-07-01 00:00:00')),分区p7值小于(UNIX_TIMESTAMP('2009-07-01 00:00:00')),分区p8值小于(UNIX_TIMESTAMP('2010-01-01 00:00:00')),分区p9值小于(MAXVALUE));
任何其他涉及到时间戳
不允许使用值。(参见Bug #42849。)
当以下一个或多个条件为真时,范围分区特别有用:
你想要或需要删除”老”数据。的分区方案
员工
表,您可以简单使用删除分区
删除与1991年以前停止为公司工作的员工有关的所有行。(见第13.1.9节“ALTER TABLE语句”,第24.3节“分区管理”查阅更多资料。)对于有很多行的表,这可能比运行删除
查询,例如DELETE FROM employees WHERE YEAR(separated) <= 1990;
.您希望使用包含日期或时间值,或包含来自其他系列的值的列。
您经常运行直接依赖于用于对表进行分区的列的查询。例如,在执行查询时
说明SELECT COUNT(*) FROM '2000-01-01' AND '2000-12-31' ' GROUP BY store_id;
, MySQL可以快速确定唯一的分区p2
需要扫描,因为其余分区不能包含满足在哪里
条款。看到第24.4节,“分区修剪”,以了解更多关于如何实现这一功能的信息。
这种类型的分区的变体是范围列
分区。分区的范围列
可以使用多个列来定义分区范围,这些分区范围既适用于分区中的行放置,也适用于在执行分区修剪时确定包含或排除特定分区。看到第24.2.3.1节,“RANGE列分区”,以获取更多资料。
基于时间间隔的分区方案。如果你想在MySQL 8.0中实现一个基于范围或时间间隔的分区方案,你有两个选择:
对表进行分区
范围
,对于分区表达式,使用对日期
,时间
,或DATETIME
列并返回一个整数值,如下所示:CREATE TABLE member (firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), join DATE NOT NULL) PARTITION BY RANGE(YEAR(joined)) (PARTITION p0 VALUES小于(1960),PARTITION p1 VALUES小于(1970),PARTITION p2 VALUES小于(1980),PARTITION p3 VALUES小于(1990),PARTITION p4 VALUES小于MAXVALUE);
在MySQL 8.0中,也可以对表进行分区
范围
基于a的值时间戳
列,使用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小于(UNIX_TIMESTAMP('2008-01-01 00:00:00')), PARTITION p1 VALUES小于(UNIX_TIMESTAMP('2008-04-01 00:00:00')), PARTITION p2 VALUES小于(UNIX_TIMESTAMP('2008-07-01 00:00:00')),分区p3值小于(UNIX_TIMESTAMP(' 2008-01-01 00:00:00')),分区p4值小于(UNIX_TIMESTAMP('2009-01-01 00:00:00')),分区p5值小于(UNIX_TIMESTAMP('2009-04-01 00:00:00')),分区p6值小于(UNIX_TIMESTAMP('2009-07-01 00:00:00')),分区p7值小于(UNIX_TIMESTAMP('2009-07-01 00:00:00')),分区p8值小于(UNIX_TIMESTAMP('2010-01-01 00:00:00')),分区p9值小于(MAXVALUE));
在MySQL 8.0中,任何涉及到
时间戳
不允许使用值。(参见Bug #42849。)请注意它也可以在MySQL 8.0中使用
UNIX_TIMESTAMP (timestamp_column)
作为分区表的分区表达式列表
.然而,这样做通常是不实际的。对表进行分区
范围列
,使用日期
或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 COLUMNS(joined) (PARTITION p0 VALUES小于('1960-01-01'),PARTITION p1 VALUES小于('1970-01-01'),PARTITION p2 VALUES小于('1980-01-01'),PARTITION p3 VALUES小于('1990-01-01'),PARTITION p4 VALUES小于MAXVALUE);