mysql数据添加分区_MySql数据分区操作,新增分区

环境： centOS6.4 32位 mysql5.1 分区实验 myisam 一、查看当前mysql是否支持分区 mysql>show plugins; +------------+--------+----------------+---------+---------+| Name | Status | Type | Library | License |+------------+-------

如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：

ERROR 1505 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) */

一，什么是数据库分区 前段时间写过一篇关于mysql分表的 的文章，下面来说一下什么是数据库分区，以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面 (可以通过my.cnf中的datadir来查看)，一张表主要对应着三个文件，一 再次插入数据时，会将原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.jb51.net/article/42544.htm)

如果用此方式在之前添加会报错，只能用另一种合并拆分分区的方式操作。