MySQL存储分区重复_mysql 分区

mysql分区

前提条件

mysql版本 5.1以上

查看是否装载分区插件

show plugins;

+------------+--------+----------------+---------+---------+

| Name       | Status | Type           | Library | License |

+------------+--------+----------------+---------+---------+

| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

+------------+--------+----------------+---------+---------+

有partition就行

按照区域分区range**************

这种分区时,分区字段要是递增的一种顺序排列,这个字段可以间隔,但不能重复,否则按分区查询的时候只会显示一条,类似groupby

创建分区表

create table demo(id int,name char(20))

partition by range(id)(

partition p0 values less than(1000),

partition p1 values less than(2000),

partition p2 values less than maxvalue

);

\d $

创建存储过程

create procedure insert_data()

begin

declare i int default 5000;

while i>0 do

insert into demo(id,name) values (i,left(md5(i),5));

set i=i-1;

end while;

end

$

create procedure insert_da()

begin

declare i int default 5000;

while i>0 do

insert into testhash(id,name) values (i,left(md5(i),5));

set i=i-5;

end while;

end

$

call insert_data()$

查询数据看走什么分区

explain partitions select * from demo where id=100\G

清空表

truncate demo;

create table demo(id int,name char(20))

partitions by range(id)(

partition p1 values less than(100),

partition p2 values less than(200),

partition p3 values less than maxvalue

);

list分组