2
drop table if exists t1;
3
create table t1 (a bit(65), primary key (a)) engine='INNODB' partition by key (a);
4
ERROR 42000: Display width out of range for column 'a' (max = 64)
5
create table t1 (a bit(0), primary key (a)) engine='INNODB' partition by key (a);
9
`a` bit(1) NOT NULL DEFAULT b'0',
11
) ENGINE=InnoDB DEFAULT CHARSET=latin1
12
/*!50100 PARTITION BY KEY (a) */
14
create table t1 (a bit(0), primary key (a)) engine='INNODB'
15
partition by key (a) (
20
t1 CREATE TABLE `t1` (
21
`a` bit(1) NOT NULL DEFAULT b'0',
23
) ENGINE=InnoDB DEFAULT CHARSET=latin1
24
/*!50100 PARTITION BY KEY (a)
25
(PARTITION pa1 ENGINE = InnoDB,
26
PARTITION pa2 ENGINE = InnoDB) */
28
create table t1 (a bit(64), primary key (a)) engine='INNODB'
29
partition by key (a) partitions 2;
32
t1 CREATE TABLE `t1` (
33
`a` bit(64) NOT NULL DEFAULT b'0',
35
) ENGINE=InnoDB DEFAULT CHARSET=latin1
36
/*!50100 PARTITION BY KEY (a)
39
(b'1111111111111111111111111111111111111111111111111111111111111111'),
40
(b'1000000000000000000000000000000000000000000000000000000000000000'),
41
(b'0000000000000000000000000000000000000000000000000000000000000001'),
42
(b'1010101010101010101010101010101010101010101010101010101010101010'),
43
(b'0101010101010101010101010101010101010101010101010101010101010101');
44
select hex(a) from t1;
52
create table t1 (a bit(64), primary key (a)) engine='INNODB'
53
partition by key (a) (
54
partition pa1 max_rows=20 min_rows=2,
55
partition pa2 max_rows=30 min_rows=3,
56
partition pa3 max_rows=30 min_rows=4,
57
partition pa4 max_rows=40 min_rows=2);
60
t1 CREATE TABLE `t1` (
61
`a` bit(64) NOT NULL DEFAULT b'0',
63
) ENGINE=InnoDB DEFAULT CHARSET=latin1
64
/*!50100 PARTITION BY KEY (a)
65
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
66
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
67
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
68
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
70
(b'1111111111111111111111111111111111111111111111111111111111111111'),
71
(b'1000000000000000000000000000000000000000000000000000000000000000'),
72
(b'0000000000000000000000000000000000000000000000000000000000000001'),
73
(b'1010101010101010101010101010101010101010101010101010101010101010'),
74
(b'0101010101010101010101010101010101010101010101010101010101010101');
75
select hex(a) from t1 where a=b'0101010101010101010101010101010101010101010101010101010101010101';
78
delete from t1 where a=b'0101010101010101010101010101010101010101010101010101010101010101';
79
select hex(a) from t1;
86
create table t2 (a bit, primary key (a)) engine='INNODB'
87
partition by key (a) partitions 4;
90
t2 CREATE TABLE `t2` (
91
`a` bit(1) NOT NULL DEFAULT b'0',
93
) ENGINE=InnoDB DEFAULT CHARSET=latin1
94
/*!50100 PARTITION BY KEY (a)
96
insert into t2 values (b'0'), (b'1');
97
select hex(a) from t2;
101
alter table t2 drop primary key;
102
show create table t2;
104
t2 CREATE TABLE `t2` (
105
`a` bit(1) NOT NULL DEFAULT b'0'
106
) ENGINE=InnoDB DEFAULT CHARSET=latin1
107
/*!50100 PARTITION BY KEY (a)
109
select hex(a) from t2;
113
alter table t2 add primary key (a);
114
show create table t2;
116
t2 CREATE TABLE `t2` (
117
`a` bit(1) NOT NULL DEFAULT b'0',
119
) ENGINE=InnoDB DEFAULT CHARSET=latin1
120
/*!50100 PARTITION BY KEY (a)
122
select hex(a) from t2;
127
create table t3 (a bit(8), primary key (a)) engine='INNODB'
128
partition by range (a) subpartition by key (a) subpartitions 2 (
129
partition pa1 values less than (3),
130
partition pa2 values less than (16),
131
partition pa3 values less than (64),
132
partition pa4 values less than (256));
133
show create table t3;
135
t3 CREATE TABLE `t3` (
136
`a` bit(8) NOT NULL DEFAULT b'0',
138
) ENGINE=InnoDB DEFAULT CHARSET=latin1
139
/*!50100 PARTITION BY RANGE (a)
140
SUBPARTITION BY KEY (a)
142
(PARTITION pa1 VALUES LESS THAN (3) ENGINE = InnoDB,
143
PARTITION pa2 VALUES LESS THAN (16) ENGINE = InnoDB,
144
PARTITION pa3 VALUES LESS THAN (64) ENGINE = InnoDB,
145
PARTITION pa4 VALUES LESS THAN (256) ENGINE = InnoDB) */
147
select hex(a) from t3 where a=b'01010101';
150
delete from t3 where a=b'01010101';
151
select count(*) from t3;
154
select hex(a) from t3;
411
create table t4 (a bit(8), primary key (a)) engine='INNODB'
412
partition by list (a) subpartition by key (a) subpartitions 2 (
413
partition pa1 values in (0,1,2,3),
414
partition pa2 values in (4,5,6,7,8,9,10,11,12,13,14,15,16),
415
partition pa3 values in (17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32));
416
show create table t4;
418
t4 CREATE TABLE `t4` (
419
`a` bit(8) NOT NULL DEFAULT b'0',
421
) ENGINE=InnoDB DEFAULT CHARSET=latin1
422
/*!50100 PARTITION BY LIST (a)
423
SUBPARTITION BY KEY (a)
425
(PARTITION pa1 VALUES IN (0,1,2,3) ENGINE = InnoDB,
426
PARTITION pa2 VALUES IN (4,5,6,7,8,9,10,11,12,13,14,15,16) ENGINE = InnoDB,
427
PARTITION pa3 VALUES IN (17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32) ENGINE = InnoDB) */
429
select hex(a) from t4 where a=b'00000001';
432
delete from t4 where a=b'00000001';
433
select count(*) from t4;
436
select hex(a) from t4;