MySql数据分区操作之新增分区操作

2024-04-03 10:03:04 23

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


ERROR1505<HY000>Partitionmanagementonanotpartitionedtableisnotpossible

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

测试创建分区表文件


CREATETABLEtr(idINT,nameVARCHAR(50),purchasedDATE)

PARTITIONBYRANGE(YEAR(purchased))

(

   PARTITIONp0VALUESLESSTHAN(1990),

   PARTITIONp1VALUESLESSTHAN(1995),

   PARTITIONp2VALUESLESSTHAN(2000),

   PARTITIONp3VALUESLESSTHAN(2005)

);

插入测试数据


INSERTINTOtrVALUES

(1,'deskorganiser','2003-10-15′),

(2,'CDplayer','1993-11-05′),

(3,'TVset','1996-03-10′),

(4,'bookcase','1982-01-10′),

(5,'exercisebike','2004-05-09′),

(6,'sofa','1987-06-05′),

(7,'popcornmaker','2001-11-22′),

(8,'aquarium','1992-08-04′),

(9,'studydesk','1984-09-16′),

(10,'lavalamp','1998-12-25′);

查询P2中的数据


select*fromtrwherepurchasedbetween'1995-01-01′and'2004-12-31′;

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


altertabletrdroppartitionp2;

showcreatetabletr;

CREATETABLE`tr`(

 `id`int(11)DEFAULTNULL,

 `name`varchar(50)DEFAULTNULL,

 `purchased`dateDEFAULTNULL

)ENGINE=MyISAMDEFAULTCHARSET=utf8

/*!50100PARTITIONBYRANGE(YEAR(purchased))

(PARTITIONp0VALUESLESSTHAN(1990)ENGINE=MyISAM,

 PARTITIONp1VALUESLESSTHAN(1995)ENGINE=MyISAM,

 PARTITIONp3VALUESLESSTHAN(2005)ENGINE=MyISAM)*/

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


INSERTINTOtrVALUES(11,'pencilholder','1995-07-12′);

ALTERTABLEtrDROPPARTITIONp3;

SELECT*FROMtrWHEREpurchased BETWEEN'1995-01-01′AND'2004-12-31′;

创建一个新的测试表


CREATETABLEmembers(

   idINT,

   fnameVARCHAR(25),

   lnameVARCHAR(25),

   dobDATE

)

PARTITIONBYRANGE(YEAR(dob))(

   PARTITIONp0VALUESLESSTHAN(1970),

   PARTITIONp1VALUESLESSTHAN(1980),

   PARTITIONp2VALUESLESSTHAN(1990)

);

直接用altertabletablenameaddpartition方式再最后面添加分区


ALTERTABLEmembersADDPARTITION(PARTITIONp3VALUESLESSTHAN(2000));


ALTERTABLEmembersreorganizepartitionp0into(

   partitionm0valueslessthan(1960),

   partitionm1valueslessthan(1970)

);

showcreatetablemembers;

CREATETABLE`members`(

 `id`int(11)DEFAULTNULL,

 `fname`varchar(25)DEFAULTNULL,

 `lname`varchar(25)DEFAULTNULL,

 `dob`dateDEFAULTNULL

)ENGINE=MyISAMDEFAULTCHARSET=utf8

/*!50100PARTITIONBYRANGE(YEAR(dob))

(PARTITIONm0VALUESLESSTHAN(1960)ENGINE=MyISAM,

 PARTITIONm1VALUESLESSTHAN(1970)ENGINE=MyISAM,

 PARTITIONp1VALUESLESSTHAN(1980)ENGINE=MyISAM,

 PARTITIONp2VALUESLESSTHAN(1990)ENGINE=MyISAM,

 PARTITIONp3VALUESLESSTHAN(2000)ENGINE=MyISAM)*/

使用REORGANIZEPARTITION进行数据的合并与拆分，数据是没有丢失的。
(详细出处参考：https://www.nhooo.com/article/42544.htm)
如果用此方式在之前添加会报错，只能用另一种合并拆分分区的方式操作。

MySql数据分区操作之新增分区操作

热门推荐

随机推荐