columns分区
在mysql5.5之前range分区和list分区只支持整数分区,可以通过额外的函数运算或者额外的转换从而得到一个整数。columns分区分为 range columns 和 list columns 两种,支持整数(tinyint到bigint, 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型。
columns分区支持一个或者多个字段作为分区键,不支持表达式作为分区键,这点区别于range 和 list 分区。需要注意的是range columns 分区键的比较是基于元组的比较,也就是基于字段组的比较,这和range分区有差异。
语法:
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
#方法1:
partition by range columns(expr) (
partition <分区名称> values less than (Value1,Value2),
...
partition <分区名称> values less than (Value3, Value4),
)
#方法2:
partition by list columns(expr) (
partition <分区名称> values in (Value1,Value2),
...
partition <分区名称> values in than (Value3, Value4),
)
expr:支持一个或者多个字段作为分区键
分区名称: 要保证不同,也可以采用 p0、p1、p2 这样的分区名称,
ValueN : 表示某个具体的值
demo1:
该表有40家超市职员记录,假定你需要将超市员工按店铺与入职时期进行分别存储,
CREATE TABLE `employees_range_columns` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`store_id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by range COLUMNS(store_id,create_time)(
partition p10 values less than (21,'2018-09-09 00:00:00'),
partition p11 values less than (21,'2019-09-09 00:00:00'),
partition p12 values less than (21,'2020-09-09 00:00:00'),
partition p20 values less than (31,'2018-09-09 00:00:00'),
partition p21 values less than (31,'2019-09-09 00:00:00'),
partition p22 values less than (31,'2020-09-09 00:00:00'),
partition p30 values less than (41,'2018-09-09 00:00:00'),
partition p31 values less than (41,'2019-09-09 00:00:00'),
partition p32 values less than (41,'2020-09-09 00:00:00')
)
分区文件(截图):
1.新增分区
alter table employees_range_columns add partition (partition xp51 values less than (51,'2020-09-09 00:00:00'));
2.重置分区
alter table employees_range_columns
REORGANIZE PARTITION p10,p11,p12,p20,p21,p22,p30,p31,p32 INTO
(
partition np11 values less than (21,'2019-09-09 00:00:00'),
partition np12 values less than (21,'2020-09-09 00:00:00'),
partition np21 values less than (31,'2019-09-09 00:00:00'),
partition np22 values less than (31,'2020-09-09 00:00:00'),
partition np31 values less than (41,'2019-09-09 00:00:00'),
partition np32 values less than (41,'2020-09-09 00:00:00')
)
3.删除分区
alter table employees_range_columns drop PARTITION p10;
4.移除表的分区
alter table employees_range_columns remove partitioning;
demo2:
该表有40家超市职员记录,假定你需要将超市员工按入职时期进行分别存储,
CREATE TABLE `employees_range_columns_create_time` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by range COLUMNS(create_time)(
partition p10 values less than ('2018-09-09 00:00:00'),
partition p11 values less than ('2019-09-09 00:00:00'),
partition p12 values less than ('2020-09-09 00:00:00'),
partition p32 values less than MAXVALUE
)
demo3:
假定你需要将超市员工划分地区,该表有40家超市职员记录。这40家超市的编号从1到40。而这40家超市分布在5个有经销权的地区,店铺分布如下表示
地区 | 店铺id号 |
---|---|
东区 | 1,2,3,4,5,6,7,8 |
南区 | 17,18,19,20,21,22,23,24 |
西区 | 33,34,35,36,37,38,39,40 |
北区 | 9,10,11,12,13,14,15,16 |
中心区 | 25,26,27,28,29,30,31,32 |
CREATE TABLE `employees_list_columns` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by list columns(store_id)(
partition p0 values in (1,2,3,4,5,6,7,8),
partition p1 values in (17,18,19,20,21,22,23,24),
partition p2 values in (33,34,35,36,37,38,39,40),
partition p3 values in (9,10,11,12,13,14,15,16),
partition p4 values in (25,26,27,28,29,30,31,32)
);