partition.result 7.57 KB
Newer Older
1 2 3 4 5 6 7
drop table if exists t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by key (a);
unknown's avatar
unknown committed
8 9 10
select count(*) from t1;
count(*)
0
11 12 13 14 15 16 17 18
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY KEY (a) 
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
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, b);
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)
partitions 3
(partition x1, partition x2, partition x3);
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)
partitions 3
(partition x1 nodegroup 0,
partition x2 nodegroup 1,
partition x3 nodegroup 2);
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)
partitions 3
(partition x1 engine myisam,
partition x2 engine myisam,
partition x3 engine myisam);
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)
partitions 3
(partition x1 tablespace ts1,
partition x2 tablespace ts2,
partition x3 tablespace ts3);
unknown's avatar
unknown committed
68 69
CREATE TABLE t2 LIKE t1;
drop table t2;
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by list (a)
partitions 3
(partition x1 values in (1,2,9,4) tablespace ts1,
partition x2 values in (3, 11, 5, 7) tablespace ts2,
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by list (b*a)
partitions 3
(partition x1 values in (1,2,9,4) tablespace ts1,
partition x2 values in (3, 11, 5, 7) tablespace ts2,
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by list (b*a)
(partition x1 values in (1) tablespace ts1,
partition x2 values in (3, 11, 5, 7) tablespace ts2,
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
drop table t1;
unknown's avatar
unknown committed
103 104 105 106 107 108 109 110 111 112
CREATE TABLE t1 (
a int not null)
partition by key(a);
LOCK TABLES t1 WRITE;
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
UNLOCK TABLES;
drop table t1;
unknown's avatar
unknown committed
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11));
INSERT INTO t1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'acquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
SELECT * from t1 ORDER BY a;
a	name	purchased
1	desk organiser	2003-10-15
2	CD player	1993-11-05
3	TV set	1996-03-10
4	bookcase	1982-01-10
5	exercise bike	2004-05-09
6	sofa	1987-06-05
7	popcorn maker	2001-11-22
8	acquarium	1992-08-04
9	study desk	1984-09-16
10	lava lamp	1998-12-25
ALTER TABLE t1 DROP PARTITION p0;
SELECT * from t1 ORDER BY a;
a	name	purchased
3	TV set	1996-03-10
4	bookcase	1982-01-10
5	exercise bike	2004-05-09
6	sofa	1987-06-05
7	popcorn maker	2001-11-22
8	acquarium	1992-08-04
9	study desk	1984-09-16
10	lava lamp	1998-12-25
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6));
insert into t1 values (1),(2),(3),(4),(5),(6);
select * from t1;
a
1
2
3
4
5
6
truncate t1;
select * from t1;
a
truncate t1;
select * from t1;
a
drop table t1;
CREATE TABLE t1 (a int, b int, primary key(a,b))
PARTITION BY KEY(b,a) PARTITIONS 4;
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
select * from t1 where a = 4;
a	b
4	4
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY LIST (a)
PARTITIONS 1
(PARTITION x1 VALUES IN (1) ENGINE=MEMORY);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) default NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 PARTITION BY LIST (a) (PARTITION x1 VALUES IN (1) ENGINE = MEMORY)
drop table t1;
CREATE TABLE t1 (a int, unique(a))
PARTITION BY LIST (a)
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
REPLACE t1 SET a = 4;
ERROR HY000: Table has no partition for value 4
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY LIST (a)
(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3));
insert into t1 values (2), (3);
insert into t1 values (4);
ERROR HY000: Table has no partition for value 4
insert into t1 values (1);
ERROR HY000: Table has no partition for value 1
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY HASH(a)
PARTITIONS 5;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (a) PARTITIONS 5 
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY RANGE (a)
(PARTITION x1 VALUES LESS THAN (2));
insert into t1 values (1);
update t1 set a = 5;
ERROR HY000: Table has no partition for value 5
drop table t1;
CREATE TABLE t1 (a int)
PARTITION BY LIST (a)
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
drop table t1;
CREATE TABLE `t1` (
`id` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
SELECT * FROM t1;
id
drop table t1;
unknown's avatar
unknown committed
234 235 236 237 238
CREATE TABLE `t1` (
`id` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
SELECT * FROM t1;
id
unknown's avatar
unknown committed
239
drop table t1;
240 241 242 243 244
create table t1
(a int)
partition by range (a)
( partition p0 values less than(10),
partition p1 values less than (20),
unknown's avatar
unknown committed
245 246
partition p2 values less than (25));
alter table t1 reorganize partition p2 into (partition p2 values less than (30));
247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM)
drop table t1;
CREATE TABLE t1 (a int, b int)
PARTITION BY RANGE (a)
(PARTITION x0 VALUES LESS THAN (2),
PARTITION x1 VALUES LESS THAN (4),
PARTITION x2 VALUES LESS THAN (6),
PARTITION x3 VALUES LESS THAN (8),
PARTITION x4 VALUES LESS THAN (10),
PARTITION x5 VALUES LESS THAN (12),
PARTITION x6 VALUES LESS THAN (14),
PARTITION x7 VALUES LESS THAN (16),
PARTITION x8 VALUES LESS THAN (18),
PARTITION x9 VALUES LESS THAN (20));
unknown's avatar
unknown committed
265
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
266 267 268 269 270 271 272 273
(PARTITION x1 VALUES LESS THAN (6));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) default NULL,
  `b` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (a) (PARTITION x1 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION x3 VALUES LESS THAN (8) ENGINE = MyISAM, PARTITION x4 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION x5 VALUES LESS THAN (12) ENGINE = MyISAM, PARTITION x6 VALUES LESS THAN (14) ENGINE = MyISAM, PARTITION x7 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION x8 VALUES LESS THAN (18) ENGINE = MyISAM, PARTITION x9 VALUES LESS THAN (20) ENGINE = MyISAM)
drop table t1;
274 275 276 277 278 279 280
create table t1 (a int not null, b int not null) partition by LIST (a+b) (
partition p0 values in (12),
partition p1 values in (14)
);
insert into t1 values (10,1);
ERROR HY000: Table has no partition for value 11
drop table t1;