如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误:
复制代码 代码如下:
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
测试创建分区表文件
复制代码 代码如下:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
复制代码 代码如下:
INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15′),
(2, 'CD player', '1993-11-05′),
(3, 'TV set', '1996-03-10′),
(4, 'bookcase', '1982-01-10′),
(5, 'exercise bike', '2004-05-09′),
(6, 'sofa', '1987-06-05′),
(7, 'popcorn maker', '2001-11-22′),
(8, 'aquarium', '1992-08-04′),
(9, 'study desk', '1984-09-16′),
(10, 'lava lamp', '1998-12-25′);
复制代码 代码如下:
select * from tr where purchased between '1995-01-01′ and '2004-12-31′;
复制代码 代码如下: