1
drop table if exists t1;
2
CREATE TABLE t1 (c1 INT)
5
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
6
ALTER TABLE t1 COALESCE PARTITION 13;
8
CREATE TABLE t1 (c1 INT)
9
PARTITION BY LINEAR HASH (c1)
11
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
12
ALTER TABLE t1 COALESCE PARTITION 3;
14
create table t1 (a int unsigned)
15
partition by hash(a div 2)
17
insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
18
select * from t1 where a < 0;
20
select * from t1 where a is null or (a >= 5 and a <= 7);
26
select * from t1 where a is null;
29
select * from t1 where a is not null;
39
select * from t1 where a >= 1 and a < 3;
43
select * from t1 where a >= 3 and a <= 5;
48
select * from t1 where a > 2 and a < 4;
51
select * from t1 where a > 3 and a <= 6;
56
select * from t1 where a > 5;
60
select * from t1 where a >= 1 and a <= 5;
67
explain partitions select * from t1 where a < 0;
68
id select_type table partitions type possible_keys key key_len ref rows Extra
69
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
70
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
71
id select_type table partitions type possible_keys key key_len ref rows Extra
72
1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 9 Using where
73
explain partitions select * from t1 where a is null;
74
id select_type table partitions type possible_keys key key_len ref rows Extra
75
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 9 Using where
76
explain partitions select * from t1 where a is not null;
77
id select_type table partitions type possible_keys key key_len ref rows Extra
78
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
79
explain partitions select * from t1 where a >= 1 and a < 3;
80
id select_type table partitions type possible_keys key key_len ref rows Extra
81
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 9 Using where
82
explain partitions select * from t1 where a >= 3 and a <= 5;
83
id select_type table partitions type possible_keys key key_len ref rows Extra
84
1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 9 Using where
85
explain partitions select * from t1 where a > 2 and a < 4;
86
id select_type table partitions type possible_keys key key_len ref rows Extra
87
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 9 Using where
88
explain partitions select * from t1 where a > 3 and a <= 6;
89
id select_type table partitions type possible_keys key key_len ref rows Extra
90
1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 9 Using where
91
explain partitions select * from t1 where a > 5;
92
id select_type table partitions type possible_keys key key_len ref rows Extra
93
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
94
explain partitions select * from t1 where a >= 1 and a <= 5;
95
id select_type table partitions type possible_keys key key_len ref rows Extra
96
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
103
partition by hash (a + 2)
105
(partition x1 tablespace ts1,
106
partition x2 tablespace ts2,
107
partition x3 tablespace ts3);
108
insert into t1 values (1,1,1);
109
insert into t1 values (2,1,1);
110
insert into t1 values (3,1,1);
111
insert into t1 values (4,1,1);
112
insert into t1 values (5,1,1);
120
update t1 set c=3 where b=1;
128
select b from t1 where a=3;
131
select b,c from t1 where a=1 AND b=1;
134
delete from t1 where a=1;
135
delete from t1 where c=3;
139
partition by hash (a + 3)
141
(partition x1 tablespace ts1,
142
partition x2 tablespace ts2,
143
partition x3 tablespace ts3);
152
partition by hash (a)
163
CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2;
164
INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######';
169
CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
170
INSERT INTO t1 VALUES (0);
172
create table t1 (c1 int DEFAULT NULL,
173
c2 varchar (30) DEFAULT NULL,
174
c3 date DEFAULT NULL)
176
partition by hash (to_days(c3))
178
insert into t1 values
179
(136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'),
180
(182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22');
189
select * from t1 where c3 between '2002-01-01' and '2002-12-31';
198
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
199
INSERT DELAYED INTO t1 VALUES (1);