mysql简历项目分享_mysql 表分区简历（备忘）

练习且记录

1、准备表，创建一个测试表

create table tabletest(

uno int(5) primary key,

uname varchar(24)

);

2、插入数据

insert into tabletest(uno,uname) values(8003,'测试1');

insert into tabletest(uno,uname) values(8005,'测试2');

insert into tabletest(uno,uname) values(8006,'测试3');

insert into tabletest(uno,uname) values(8007,'测试4');

insert into tabletest(uno,uname) values(8000,'测试5');

insert into tabletest(uno,uname) values(8001,'测试6');

insert into tabletest(uno,uname) values(8002,'测试7');

insert into tabletest(uno,uname) values(8004,'测试8');

3、查询结果

select * from tabletest

4、建立分区，按照uno的值进行设定分区规则如下：

alter table tabletest partition by range(uno)

(

partition part0 values less than (8001),

partition part1 values less than (8003),

partition part2 values less than (8005),

partition part3 values less than maxvalue

);

5、查询结果，可以看到查询结果分布到不同的分区里

select * from tabletest partition (part0);

select * from tabletest partition (part1);

select * from tabletest partition (part2);

select * from tabletest partition (part3);

6、新插入数据

insert into tabletest(uno,uname) values(8011,'测试9');

insert into tabletest(uno,uname) values(8012,'测试10');

insert into tabletest(uno,uname) values(8013,'测试11');

insert into tabletest(uno,uname) values(8014,'测试12');

insert into tabletest(uno,uname) values(8015,'测试13');

insert into tabletest(uno,uname) values(8016,'测试14');

insert into tabletest(uno,uname) values(8017,'测试15');

insert into tabletest(uno,uname) values(8018,'测试16');

7、再次查询分区数据，会看到

第6步插入的数据按照分区规则划分到对应的分区里

select * from tabletest partition (part0);

select * from tabletest partition (part1);

select * from tabletest partition (part2);

select * from tabletest partition (part3);