各位用户为了找寻关于MySql数据分区操作之新增分区操作的资料费劲了很多周折。这里教程网为您整理了关于MySql数据分区操作之新增分区操作的相关资料，仅供查阅，以下为您介绍关于MySql数据分区操作之新增分区操作的详细内容
如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：
代码如下: ERROR 1505 <HY000> Partition management on a not partitioned table is not possible正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;
测试创建分区表文件
代码如下: 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′);
查询P2中的数据
代码如下: select * from tr where purchased between '1995-01-01′ and '2004-12-31′;
如果删除P2，在删除P2分区的同时，也会将其下的所有数据删除
代码如下: alter table tr drop partition p2; show create table tr; CREATE TABLE `tr` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM) */
再次插入数据时，会将原P2的数据插入至P3中
代码如下: INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12′); ALTER TABLE tr DROP PARTITION p3; SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01′ AND '2004-12-31′;
创建一个新的测试表
代码如下: CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE ) PARTITION BY RANGE(YEAR(dob)) ( PARTITION p0 VALUES LESS THAN (1970), PARTITION p1 VALUES LESS THAN (1980), PARTITION p2 VALUES LESS THAN (1990) );
直接用alter table tablename add partition 方式再最后面添加分区
代码如下: ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
代码如下: ALTER TABLE members reorganize partition p0 into ( partition m0 values less than (1960), partition m1 values less than (1970) ); show create table members; CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) DEFAULT NULL, `lname` varchar(25) DEFAULT NULL, `dob` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (YEAR(dob)) (PARTITION m0 VALUES LESS THAN (1960) ENGINE = MyISAM, PARTITION m1 VALUES LESS THAN (1970) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM) */
使用 REORGANIZE PARTITION进行数据的合并与拆分，数据是没有丢失的。 (详细出处参考：http://www.zzvips.com/article/47629.html) 如果用此方式在之前添加会报错，只能用另一种合并拆分分区的方式操作。