Edit File: partition_hash.result
drop table if exists t1; CREATE TABLE t1 (c1 INT) PARTITION BY HASH (c1) PARTITIONS 15; INSERT INTO t1 VALUES (1), (2), (3), (4), (5); ALTER TABLE t1 COALESCE PARTITION 13; DROP TABLE t1; CREATE TABLE t1 (c1 INT) PARTITION BY LINEAR HASH (c1) PARTITIONS 5; INSERT INTO t1 VALUES (1), (2), (3), (4), (5); ALTER TABLE t1 COALESCE PARTITION 3; DROP TABLE t1; create table t1 (a int unsigned) partition by hash(a div 2) partitions 4; insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7); select * from t1 where a < 0; a select * from t1 where a is null or (a >= 5 and a <= 7); a NULL 5 6 7 select * from t1 where a is null; a NULL select * from t1 where a is not null; a 0 1 2 3 4 5 6 7 select * from t1 where a >= 1 and a < 3; a 1 2 select * from t1 where a >= 3 and a <= 5; a 3 4 5 select * from t1 where a > 2 and a < 4; a 3 select * from t1 where a > 3 and a <= 6; a 4 5 6 select * from t1 where a > 5; a 6 7 select * from t1 where a >= 1 and a <= 5; a 1 2 3 4 5 explain partitions select * from t1 where a < 0; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where explain partitions select * from t1 where a is null or (a >= 5 and a <= 7); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where explain partitions select * from t1 where a is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t1 where a is not null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where explain partitions select * from t1 where a >= 1 and a < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where explain partitions select * from t1 where a >= 3 and a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t1 where a > 2 and a < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a > 3 and a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t1 where a > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where explain partitions select * from t1 where a >= 1 and a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by hash (a + 2) partitions 3 (partition x1 tablespace ts1, partition x2 tablespace ts2, partition x3 tablespace ts3); insert into t1 values (1,1,1); insert into t1 values (2,1,1); insert into t1 values (3,1,1); insert into t1 values (4,1,1); insert into t1 values (5,1,1); select * from t1; a b c 1 1 1 4 1 1 2 1 1 5 1 1 3 1 1 update t1 set c=3 where b=1; select * from t1; a b c 1 1 3 4 1 3 2 1 3 5 1 3 3 1 3 select b from t1 where a=3; b 1 select b,c from t1 where a=1 AND b=1; b c 1 3 delete from t1 where a=1; delete from t1 where c=3; select * from t1; a b c ALTER TABLE t1 partition by hash (a + 3) partitions 3 (partition x1 tablespace ts1, partition x2 tablespace ts2, partition x3 tablespace ts3); select * from t1; a b c drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by hash (a) (partition x1); drop table t1; CREATE TABLE t1 ( a int not null, b int not null, c int not null, primary key(a,b)) partition by key (a) (partition x1); drop table t1; CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2; INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######'; select * from t1; f1 f2 -1 ####### drop table t1; CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1); INSERT INTO t1 VALUES (0); DROP TABLE t1; create table t1 (c1 int DEFAULT NULL, c2 varchar (30) DEFAULT NULL, c3 date DEFAULT NULL) engine = myisam partition by hash (to_days(c3)) partitions 12; insert into t1 values (136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'), (182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22'); select * from t1; c1 c2 c3 136 abc 2002-01-05 158 abc 2002-06-01 142 abc 2002-02-14 162 abc 2002-06-28 182 abc 2002-11-09 184 abc 2002-11-22 select * from t1 where c3 between '2002-01-01' and '2002-12-31'; c1 c2 c3 136 abc 2002-01-05 158 abc 2002-06-01 142 abc 2002-02-14 162 abc 2002-06-28 182 abc 2002-11-09 184 abc 2002-11-22 drop table t1; CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1; INSERT DELAYED INTO t1 VALUES (1); DROP TABLE t1;
Back to File Manager