【SQL应知应会】表分区（二）• MySQL版

create table user4(sex int(1)) 
 partition by list(sex)    -- 列表分区
 (
     partition p0 values in(1),   -- 第一个分区p0,1是p0分区能插入的值
    partition p1 values in(2)    -- 第一个分区p1,2是p1分区能插入的值
 );

create table user7(id int)
partition by hash(id) partition 3;

create table user6(sex varchar(10),name varchar(10),age varchar(10)) 
partition by range columns(sex,name,age) 
(
  partition p0 values less than('d','f','h'),
    partition p1 values less than('l','n','x')
);

# mysql的伪列
## 方法1
set @rownum := 0;
select t.*,@rownum := @rownum + 1 from emp t,(select @rownum := 0) b;
## 方法2
set @rownum := 0;
select t.*,@rownum := @rownum + 1 from emp t;

### range分区
# 基于属于一个给定连续区间的列值,把多行分配给分区
create table foo_range(
  id int not null atuo_increment,
    created datetime,
    primary key(id,created)   -- 联合主键,任意一个都不能为空
)engine = innodb partition by range(TO_DAYS(created))(
  partition foo_1 values LESS THAN (TO_DAYS('2016-10-18')),
    partition foo_2 values LESS THAN (TO_DAYS('2017-01-01'))
);
-- 新增一个分区
alter table foo_range add partition(
  partition foo_3 values less than (to_days('2017-10-18'))
);
-- 插入数据
insert into foo_range(id,created) values (1,'2016-10-17'),(2,'2016-10-20'),(3,'2016-1-25')
explain
select * from foo_range where created = '2016-10-17'
-- 查看分区foo_1的内容
select * from foo_range partition(foo_1)
-- 清空分区foo_3
alter table foo_range truncate partition foo_3

### list分区
# 类似于按range分区,区别在于list分区是基于列匹配一个离散值集合中的某个值来进行选择
create table foo_list
(empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
-- 以部门号为分区依据,每个部门一个分区
partition by list(deptno)
(
    partition p1 values in (10),  -- Oracle的话,将in去掉
    partition p2 values in (20),
    partition p3 values in (30)
)
-- 插入数据
insert into foo_list select 1,1,10,now(),1 from dual union all select 1,1,20,now(),1 from dual
select * from foo_list 
select * from foo_list partition(p1)