MySql数据分区操作

来源：互联网发布：手机淘宝店招750x254 编辑：程序博客网时间：2024/05/28 05:15

如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误： ERROR 1505 <HY000> Partition management on a notpartitionedtable is not possible

正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;

测试创建分区表文件

Mysql代码

1. CREATETABLE tr (id INT, name VARCHAR(50),purchased DATE)

2. PARTITIONBY RANGE(YEAR(purchased))

3. (

4. PARTITIONp0 VALUES LESS THAN (1990),

5. PARTITIONp1 VALUES LESS THAN (1995),

6. PARTITIONp2 VALUES LESS THAN (2000),

7. PARTITIONp3 VALUES LESS THAN (2005)

8. );

插入测试数据

1. INSERTINTO tr VALUES

2. (1, 'deskorganiser', '2003-10-15'),

3. (2, 'CDplayer', '1993-11-05'),

4. (3, 'TVset', '1996-03-10'),

5. (4,'bookcase', '1982-01-10'),

6. (5,'exercise bike', '2004-05-09'),

7. (6,'sofa', '1987-06-05'),

8. (7,'popcorn maker', '2001-11-22'),

9. (8,'aquarium', '1992-08-04'),

10. (9,'study desk', '1984-09-16'),

11. (10,'lava lamp', '1998-12-25');

查询P2中的数据

1. select *from tr where purchased between'1995-01-01' and '2004-12-31';

如果删除P2，在删除P2分区的同时，也会将其下的所有数据删除

1. altertable tr drop partition p2;

3. showcreate table tr;

5. CREATETABLE `tr` (

6.`id`int(11) DEFAULT NULL,

7. `name`varchar(50) DEFAULT NULL,

8.`purchased` date DEFAULT NULL

9. )ENGINE=MyISAM DEFAULT CHARSET=utf8

10. /*!50100PARTITION BY RANGE(YEAR(purchased))

11.(PARTITION p0 VALUES LESS THAN (1990) ENGINE =MyISAM,

12. PARTITIONp1 VALUES LESS THAN (1995) ENGINE =MyISAM,

13. PARTITIONp3 VALUES LESS THAN (2005) ENGINE =MyISAM) */

再次插入数据时，会将原P2的数据插入至P3中

1. INSERTINTO tr VALUES (11, 'pencil holder','1995-07-12');

3. ALTERTABLE tr DROP PARTITION p3;

5. SELECT *FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';

创建一个新的测试表

1. CREATETABLE members (

2. idINT,

3. fnameVARCHAR(25),

4. lnameVARCHAR(25),

5. dob DATE

6. )

7. PARTITIONBY RANGE(YEAR(dob)) (

8. PARTITIONp0 VALUES LESS THAN (1970),

9. PARTITIONp1 VALUES LESS THAN (1980),

10. PARTITIONp2 VALUES LESS THAN (1990)

11. );

直接用alter table tablename addpartition 方式再最后面添加分区

1. ALTERTABLE members ADD PARTITION (PARTITIONp3 VALUES LESS THAN (2000));

1. ALTERTABLE members reorganize partition p0into (

2. partitionm0 values less than (1960),

3. partitionm1 values less than (1970)

4. );

6. showcreate table members;

8. CREATETABLE `members` (

9.`id`int(11) DEFAULT NULL,

10. `fname`varchar(25) DEFAULT NULL,

11. `lname`varchar(25) DEFAULT NULL,

12. `dob`dateDEFAULT NULL

13. )ENGINE=MyISAM DEFAULT CHARSET=utf8

14. /*!50100PARTITION BY RANGE (YEAR(dob))

15.(PARTITION m0 VALUES LESS THAN (1960) ENGINE =MyISAM,

16. PARTITIONm1 VALUES LESS THAN (1970) ENGINE =MyISAM,

17. PARTITIONp1 VALUES LESS THAN (1980) ENGINE =MyISAM,

18. PARTITIONp2 VALUES LESS THAN (1990) ENGINE =MyISAM,

19. PARTITIONp3 VALUES LESS THAN (2000) ENGINE =MyISAM) */

转自：http://yuelangyc.iteye.com/blog/2006880

0 0