MYSQL数据库MySQL数据库分区功能的使用教程

官方网站说不同分区内的子分区可以有相同的名字,但是mysql5.1.50却不行会提示以下错误
ERROR 1517 (HY000): Duplicate partition name s1
7,Columns分区
Columns分区是在MySQL 5.5引入的分区类型,引入Columns分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区,从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题. Columns分区可以细分为RANGE Columns分区和LIST Columns分区,RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型.
应用场景：
商品销售的日报表,年报表等
每天分一张表,表名用年月日每张表分24个分区,每个小时的数据分1个区.MYSQL实例

CREATE TABLE `year_log` ( 
 `id` int(11) DEFAULT NULL,`money` int(11) unsigned NOT NULL,`date` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE (year(date)) 
( 
PARTITION p2007 VALUES LESS THAN (2008),PARTITION p2008 VALUES LESS THAN (2009),PARTITION p2009 VALUES LESS THAN MAXVALUE 
); 
 
CREATE TABLE `daily_log` ( 
`id` int(11) NOT NULL,`sid` char(36) NOT NULL,`sname` char(20) DEFAULT NULL,`date` datetime NOT NULL,PRIMARY KEY (`id`,`date`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE COLUMNS(`date`) 
(PARTITION p1 VALUES LESS THAN ('2000-01-02'),PARTITION p2 VALUES LESS THAN ('2000-01-03'),PARTITION p3 VALUES LESS THAN ('2000-01-04'),PARTITION p4 VALUES LESS THAN ('2000-01-05'),PARTITION p5 VALUES LESS THAN ('2000-01-06'),PARTITION p6 VALUES LESS THAN ('2000-01-07'),PARTITION p7 VALUES LESS THAN ('2000-01-08'),PARTITION p367 VALUES LESS THAN (MAXVALUE)); 

三,分区管理
1,删除分区MYSQL实例

mysql> alter table user drop partition p4;  

2,新增分区MYSQL实例

range添加新分区 
mysql> alter table user add partition(partition p4 values less than MAXVALUE); 
Query OK,0 rows affected (0.06 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

?
list添加新分区?
mysql> alter table list_part add partition(partition p4 values in (25,26,28));?
Query OK,0 rows affected (0.01 sec)?
Records: 0? Duplicates: 0? Warnings: 0??
?
hash重新分区??
MYSQL实例

mysql> alter table hash_part add partition partitions 4; 
Query OK,0 rows affected (0.12 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

?
key重新分区??
MYSQL实例

mysql> alter table key_part add partition partitions 4; 
Query OK,1 row affected (0.06 sec)  //有数据也会被重新分配 
Records: 1 Duplicates: 0 Warnings: 0 

?
子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的??
MYSQL实例

mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); 
Query OK,0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

?
MYSQL实例

mysql> show create table sub1_partG; 
*************************** 1. row *************************** 
 Table: sub1_part 
Create Table: CREATE TABLE `sub1_part` ( 
 `news_id` int(11) NOT NULL COMMENT '新闻ID',`content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',`u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',`create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
!50100 PARTITION BY RANGE (YEAR(create_time)) 
SUBPARTITION BY HASH (TO_DAYS(create_time)) 
(PARTITION p0 VALUES LESS THAN (1990) 
 (SUBPARTITION s0 ENGINE = InnoDB,SUBPARTITION s1 ENGINE = InnoDB,SUBPARTITION s2 ENGINE = InnoDB),PARTITION p1 VALUES LESS THAN (2000) 
 (SUBPARTITION s3 ENGINE = InnoDB,SUBPARTITION s4 ENGINE = InnoDB,SUBPARTITION good ENGINE = InnoDB),PARTITION p2 VALUES LESS THAN (3000) 
 (SUBPARTITION tank0 ENGINE = InnoDB,SUBPARTITION tank1 ENGINE = InnoDB,SUBPARTITION tank3 ENGINE = InnoDB),PARTITION p3 VALUES LESS THAN MAXVALUE 
 (SUBPARTITION p3sp0 ENGINE = InnoDB,//子分区的名子是自动生成的 
 SUBPARTITION p3sp1 ENGINE = InnoDB,SUBPARTITION p3sp2 ENGINE = InnoDB)) 
1 row in set (0.00 sec) 

3,重新分区MYSQL实例

range重新分区?
MYSQL实例

mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); 
Query OK,11 rows affected (0.08 sec) 
Records: 11 Duplicates: 0 Warnings: 0 
 

list重新分区??
MYSQL实例

mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p4 INTO (PARTITION p0 VALUES in (1,5)); 
Query OK,0 rows affected (0.28 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!