1
drop table if exists t1;
3
create table t1 (a bit not null) partition by key (a);
4
insert into t1 values (b'1');
5
select hex(a) from t1 where a = b'1';
9
create table t1 (a tinyint not null) partition by key (a);
10
insert into t1 values (2);
11
select * from t1 where a = 2;
15
create table t1 (a smallint not null) partition by key (a);
16
insert into t1 values (2);
17
select * from t1 where a = 2;
21
create table t1 (a mediumint not null) partition by key (a);
22
insert into t1 values (2);
23
select * from t1 where a = 2;
27
create table t1 (a int not null) partition by key (a);
28
insert into t1 values (2);
29
select * from t1 where a = 2;
33
create table t1 (a bigint not null) partition by key (a);
34
insert into t1 values (2);
35
select * from t1 where a = 2;
39
create table t1 (a float not null) partition by key (a);
40
insert into t1 values (0.5);
41
select * from t1 where a = 0.5;
45
create table t1 (a double not null) partition by key (a);
46
insert into t1 values (0.5);
47
select * from t1 where a = 0.5;
51
create table t1 (a decimal(4,2) not null) partition by key (a);
52
insert into t1 values (2.1);
53
select * from t1 where a = 2.1;
57
create table t1 (a date not null) partition by key (a);
58
insert into t1 values ('2001-01-01');
59
select * from t1 where a = '2001-01-01';
63
create table t1 (a datetime not null) partition by key (a);
64
insert into t1 values ('2001-01-01 01:02:03');
65
select * from t1 where a = '2001-01-01 01:02:03';
69
create table t1 (a timestamp not null) partition by key (a);
70
insert into t1 values ('2001-01-01 01:02:03');
71
select * from t1 where a = '2001-01-01 01:02:03';
75
create table t1 (a time not null) partition by key (a);
76
insert into t1 values ('01:02:03');
77
select * from t1 where a = '01:02:03';
81
create table t1 (a year not null) partition by key (a);
82
insert into t1 values ('2001');
83
select * from t1 where a = '2001';
87
create table t1 (a varchar(10) character set utf8 not null) partition by key (a);
88
insert into t1 values ('abc');
89
select * from t1 where a = 'abc';
93
create table t1 (a varchar(300) character set utf8 not null) partition by key (a);
94
insert into t1 values ('abc');
95
select * from t1 where a = 'abc';
99
create table t1 (a varchar(10) character set latin1 not null) partition by key (a);
100
insert into t1 values ('abc');
101
select * from t1 where a = 'abc';
105
create table t1 (a varchar(300) character set latin1 not null) partition by key (a);
106
insert into t1 values ('abc');
107
select * from t1 where a = 'abc';
111
create table t1 (a char(10) character set utf8 not null) partition by key (a);
112
insert into t1 values ('abc');
113
select * from t1 where a = 'abc';
117
create table t1 (a char(10) character set latin1 not null) partition by key (a);
118
insert into t1 values ('abc');
119
select * from t1 where a = 'abc';
123
create table t1 (a enum('y','n') not null) partition by key (a);
124
insert into t1 values ('y');
125
select * from t1 where a = 'y';
129
create table t1 (a set('y','n') not null) partition by key (a);
130
insert into t1 values ('y');
131
select * from t1 where a = 'y';
135
# test with null allowed
136
create table t1 (a bit) partition by key (a);
137
insert into t1 values (b'1');
138
insert into t1 values (NULL);
139
select hex(a) from t1 where a = b'1';
142
select hex(a) from t1 where a is NULL;
145
select hex(a) from t1 order by a;
150
create table t1 (a tinyint) partition by key (a);
151
insert into t1 values (2);
152
select * from t1 where a = 2;
156
create table t1 (a smallint) partition by key (a);
157
insert into t1 values (2);
158
select * from t1 where a = 2;
162
create table t1 (a mediumint) partition by key (a);
163
insert into t1 values (2);
164
select * from t1 where a = 2;
168
create table t1 (a int) partition by key (a);
169
insert into t1 values (2);
170
select * from t1 where a = 2;
174
create table t1 (a bigint) partition by key (a);
175
insert into t1 values (2);
176
select * from t1 where a = 2;
180
create table t1 (a float) partition by key (a);
181
insert into t1 values (0.5);
182
select * from t1 where a = 0.5;
186
create table t1 (a double) partition by key (a);
187
insert into t1 values (0.5);
188
select * from t1 where a = 0.5;
192
create table t1 (a decimal(4,2)) partition by key (a);
193
insert into t1 values (2.1);
194
select * from t1 where a = 2.1;
198
create table t1 (a date) partition by key (a);
199
insert into t1 values ('2001-01-01');
200
select * from t1 where a = '2001-01-01';
204
create table t1 (a datetime) partition by key (a);
205
insert into t1 values ('2001-01-01 01:02:03');
206
select * from t1 where a = '2001-01-01 01:02:03';
210
create table t1 (a timestamp null) partition by key (a);
211
insert into t1 values ('2001-01-01 01:02:03');
212
select * from t1 where a = '2001-01-01 01:02:03';
216
create table t1 (a time) partition by key (a);
217
insert into t1 values ('01:02:03');
218
select * from t1 where a = '01:02:03';
222
create table t1 (a year) partition by key (a);
223
insert into t1 values ('2001');
224
select * from t1 where a = '2001';
228
create table t1 (a varchar(10) character set utf8) partition by key (a);
229
insert into t1 values ('abc');
230
select * from t1 where a = 'abc';
234
create table t1 (a varchar(300) character set utf8) partition by key (a);
235
insert into t1 values ('abc');
236
select * from t1 where a = 'abc';
240
create table t1 (a varchar(10) character set latin1) partition by key (a);
241
insert into t1 values ('abc');
242
select * from t1 where a = 'abc';
246
create table t1 (a varchar(300) character set latin1) partition by key (a);
247
insert into t1 values ('abc');
248
select * from t1 where a = 'abc';
252
create table t1 (a char(10) character set utf8) partition by key (a);
253
insert into t1 values ('abc');
254
select * from t1 where a = 'abc';
258
create table t1 (a char(10) character set latin1) partition by key (a);
259
insert into t1 values ('abc');
260
select * from t1 where a = 'abc';
264
create table t1 (a enum('y','n')) partition by key (a);
265
insert into t1 values ('y');
266
select * from t1 where a = 'y';
270
create table t1 (a set('y','n')) partition by key (a);
271
insert into t1 values ('y');
272
select * from t1 where a = 'y';
276
create table t1 (a varchar(65531)) partition by key (a);
277
insert into t1 values ('bbbb');
278
insert into t1 values ('aaaa');
279
select * from t1 where a = 'aaaa';
282
select * from t1 where a like 'aaa%';
285
select * from t1 where a = 'bbbb';
289
create table t1 (a varchar(65532)) partition by key (a);
290
insert into t1 values ('bbbb');
291
insert into t1 values ('aaaa');
292
select * from t1 where a = 'aaaa';
295
select * from t1 where a like 'aaa%';
298
select * from t1 where a = 'bbbb';
302
create table t1 (a varchar(65533) not null) partition by key (a);
303
insert into t1 values ('bbbb');
304
insert into t1 values ('aaaa');
305
select * from t1 where a = 'aaaa';
308
select * from t1 where a like 'aaa%';
311
select * from t1 where a = 'bbbb';
315
create table t1 (a varchar(65533)) partition by key (a);
316
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
317
create table t1 (a varchar(65534) not null) partition by key (a);
318
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
319
create table t1 (a varchar(65535)) partition by key (a);
320
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
321
create table t1 (a bit(27), primary key (a)) engine=myisam
322
partition by hash (a)
323
(partition p0, partition p1, partition p2);
324
show create table t1;
326
t1 CREATE TABLE `t1` (
327
`a` bit(27) NOT NULL DEFAULT b'0',
329
) ENGINE=MyISAM DEFAULT CHARSET=latin1
330
/*!50100 PARTITION BY HASH (a)
331
(PARTITION p0 ENGINE = MyISAM,
332
PARTITION p1 ENGINE = MyISAM,
333
PARTITION p2 ENGINE = MyISAM) */
334
insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34);
335
select hex(a) from t1 where a = 7;