mysql 分区-columns分区（六）

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)
);