RANGE分区通常用于在DATE类型的列上按时间间隔组织数据。
举个例子:
CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` decimal(10,2) DEFAULT NULL,
`o_orderDATE` date NOT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
KEY `o_orderkey` (`o_orderkey`),
KEY `i_o_custkey` (`o_custkey`),
KEY `i_o_orderdate` (`o_orderDATE`))
ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(
PARTITION item1 VALUES LESS THAN ('1992-01-01'),
PARTITION item2 VALUES LESS THAN ('1993-01-01'),
PARTITION item3 VALUES LESS THAN ('1994-01-01'),
PARTITION item4 VALUES LESS THAN ('1995-01-01'),
PARTITION item5 VALUES LESS THAN ('1996-01-01'),
PARTITION item6 VALUES LESS THAN ('1997-01-01'),
PARTITION item7 VALUES LESS THAN ('1998-01-01'),
PARTITION item8 VALUES LESS THAN ('1999-01-01'),
PARTITION item9 VALUES LESS THAN (MAXVALUE)
);