mysql 分区

-- 分区的类型：
-- range 分区，id 小于10 p0 分区，id 大于10 小于20 p1 分区
create table r1(
id int
) engine = innodb
partition by range (id)(
partition p0 values less than (10),
partition p1 values less than (20)
);

insert into r1 values (9);
insert into r1 values (10);
insert into r1 values (15);

select * from r1 r;

select * from information_schema.PARTITIONS P where TABLE_NAME = 'r1';
insert into r1 values (21); -- 没有分区位置，不能插入

alter table r1 add partition (partition p2 values less than maxvalue );

create table sales(
money int unsigned not null ,
date datetime
) engine=innodb
partition by range (year(date))(
partition p2008 values less than (2009),
partition p2009 values less than (2010),
partition p2010 values less than (2011)
);

insert into sales select 100,'2008-01-01';
insert into sales select 100,'2008-02-01';
insert into sales select 200,'2008-01-02';

insert into sales select 100,'2009-03-01';
insert into sales select 200,'2003-03-01';

select * from information_schema.PARTITIONS P where TABLE_NAME = 'sales';

explain select * from sales s where date >= '2008-01-01' and date <= '2008-12-31' ;

create table l1(
a int,
b int
)engine = innodb
partition by list (b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);

insert into l1 select 1,0 union select 1,1 union select 1,2 union select 1,3 ;

select * from information_schema.PARTITIONS P where TABLE_NAME = 'l1';

create table l2(
a int,
b int
)engine = MyISAM
partition by list (b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);
-- MyISAM 不支持分区

select * from l2;

create table h1(
a int,
b datetime
)engine = innodb
partition by hash ( YEAR(b) )
partitions 4;

select * from information_schema.PARTITIONS P where TABLE_NAME = 'h1' and TABLE_ROWS > 1;

insert into h1 values (1,'2018-01-01');
insert into h1 values (2,'2019-01-01');
insert into h1 values (3,'2010-01-01');
insert into h1 values (4,'2020-01-01');
insert into h1 values (5,'2022-01-01');

select * from h1 h ;

select mod (2010,4);

show create table h1 ;

create table hl1(
a int,
b datetime
)engine = innodb
partition by linear hash ( YEAR(b) )
partitions 4;

create table k1(
a int,
b datetime
)engine = innodb
partition by key(b)
partitions 4;

create table c1(
a int,
b datetime
)engine = innodb
partition by range columns (b)(
partition p0 values less than ('2009-01-01'),
partition p1 values less than ('2010-01-01')
);

create table s1(a int,b datetime) engine = innodb
partition by range (YEAR(b))
subpartition by hash ( to_days(b) )(
partition p0 values less than (1990)(
subpartition s0,
subpartition s1

    ),
partition p1 values less than (2000)(
    subpartition s3,
    subpartition s4
    ),
partition p2 values less than maxvalue(
    subpartition s5,
    subpartition s6
    )
);

select * from s1 s;
insert into s1 values (1,null);

select * from information_schema.PARTITIONS P where TABLE_NAME = 's1';

alter table s1 drop partition p0;

show create table s1 ;

alter table s1 partition by range (YEAR(b))
subpartition by hash ( to_days(b) )(
partition p0 values less than (1990)(
subpartition s0,
subpartition s1

),
partition p1 values less than (2000)(
subpartition s3,
subpartition s4
),
partition p2 values less than maxvalue(
subpartition s5,
subpartition s6
)
);