mysql分区测试
CREATE TABLE part_tab
          ( c1 int default NULL,
     c2 varchar(30) default NULL,
     c3 date default NULL
    
          ) engine=myisam
          PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
          PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
          PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
          PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
          PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
          PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
          PARTITION p11 VALUES LESS THAN MAXVALUE );



create table no_part_tab
     (c1 int(11) default NULL,
     c2 varchar(30) default NULL,
     c3 date default NULL) engine=myisam;



CREATE PROCEDURE load_part_tab()
     begin
     declare v int default 0;
              while v < 8000000
     do
     insert into part_tab
     values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
     set v = v + 1;
     end while;
     end;
    

call load_part_tab();

insert into no_part_tab select * from part_tab;

select count(*) from no_part_tab where  c3 > date '1995-01-01' and c3 < date '1995-12-31';

select count(*) from part_tab where  c3 > date '1995-01-01' and c3 < date '1995-12-31';




二、
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
lunzi   2010-12-26 02:09:49 评论:1   阅读:356   引用:0

发表评论>>

署名发表(评论可管理,不必输入下面的姓名)

姓名:

主题:

内容: 最少15个,最长1000个字符

验证码: (如不清楚,请刷新)

Copyright@2004-2010 powered by YuLog