按范围分区的表的分区方式是,每个分区都包含分区表达式值位于给定范围内的行。类定义的范围应该是连续的但不重叠的小于
操作符。对于接下来的几个例子,假设您正在创建一个如下所示的表来保存一个由20个音像店组成的连锁店的人员记录,编号从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 ' 99999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL);
的员工
此处使用的表没有主键或唯一键。虽然这些示例是为了实现本文的目的而使用的,但您应该记住,在实际操作中,表极有可能具有主键、惟一键或两者都有,并且分区列的允许选择取决于用于这些键的列(如果存在的话)。有关这些问题的讨论,请参见第24.6.1节“分区键、主键和唯一键”.
根据您的需要,可以通过多种方式对这个表进行分区。一种方法是使用可以
列。例如,您可能决定通过添加按范围划分
条款如下:
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(分区p0值小于(6),分区p1值小于(11),分区p2值小于(16),分区p3值小于(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(分区p0值小于(6),分区p1值小于(11),分区p2值小于(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)(分区p0值小于(100),分区p1值小于(1000),分区p2值小于(10000));
在此实例中,与店内工作人员相关的所有行都将存储在分区中p0
的办公室及支援人员p1
,以及与分区管理人员有关的文件p2
.
中也可以使用表达式小于
条款。然而,MySQL必须能够计算表达式的返回值作为函数的一部分不到
(<
)比较。
您可以使用基于两者之一的表达式,而不是根据存储号拆分表数据日期
列。例如,假设您希望根据每个员工离开公司的年份进行分区;的价值(分离)
.一个关于创建表
实现这样一个分区方案的语句如下所示:
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, store_id INT) PARTITION BY RANGE (YEAR(separated))(分区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('2008-01-01 00:00:00'))(分区p0值小于(UNIX_TIMESTAMP('2008-01-01 00:00:00')),分区p1值小于(UNIX_TIMESTAMP('2008-04-01 00:00:00')),分区p2值小于(UNIX_TIMESTAMP('2008-07-01 00:00:00')),分区p3值小于(UNIX_TIMESTAMP('2009-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节“分区管理”,以查阅更多资料。)对于具有大量行的表,这比运行删除
查询,例如删除离职年份<= 1990年的员工
.您希望使用包含日期或时间值的列,或者包含来自其他系列的值。
经常运行直接依赖于划分表的列的查询。例如,当执行诸如
说明SELECT COUNT(*) FROM '2000-01-01' AND '2000-12-31' GROUP之间的雇员
, MySQL可以快速确定唯一分区p2
需要扫描,因为其余分区不能包含满足在哪里
条款。看到第24.4节,“分区修剪”,以了解有关如何完成的更多信息。
这种类型的分区的变体是范围列
分区。分区的范围列
允许使用多个列来定义分区范围,这些分区范围既适用于在分区中放置行,也适用于在执行分区修剪时确定包含或排除特定分区。看到第24.2.3.1节,“RANGE COLUMNS分区”,以查询更多资料。
基于时间间隔的分区方案。如果你想在MySQL 8.0中实现一个基于时间范围或间隔的分区方案,你有两个选择:
将表划分为
范围
,对于分区表达式,使用操作于的函数日期
,时间
,或DATETIME
列并返回一个整数值,如下所示:CREATE TABLE成员(名VARCHAR(25) NOT NULL,名VARCHAR(25) NOT NULL,用户名VARCHAR(16) NOT NULL,电子邮件VARCHAR(35), join DATE NOT NULL) PARTITION BY RANGE(YEAR(joined))(分区p0值小于(1960),分区p1值小于(1970),分区p2值小于(1980),分区p3值小于(1990),分区p4值小于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('2008-01-01 00:00:00'))(分区p0值小于(UNIX_TIMESTAMP('2008-01-01 00:00:00')),分区p1值小于(UNIX_TIMESTAMP('2008-04-01 00:00:00')),分区p2值小于(UNIX_TIMESTAMP('2008-07-01 00:00:00')),分区p3值小于(UNIX_TIMESTAMP('2009-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 member (name 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)(分区p0值小于('1960-01-01'),分区p1值小于('1970-01-01'),分区p2值小于('1980-01-01'),分区p3值小于('1990-01-01'),分区p4值小于MAXVALUE);