1
create table t1 (a decimal(10,4) not null, primary key(a)) engine='MYISAM'
3
partition pa1 max_rows=20 min_rows=2,
4
partition pa2 max_rows=30 min_rows=3,
5
partition pa3 max_rows=30 min_rows=4,
6
partition pa4 max_rows=40 min_rows=2);
10
`a` decimal(10,4) NOT NULL,
12
) ENGINE=MyISAM DEFAULT CHARSET=latin1
13
/*!50100 PARTITION BY KEY (a)
14
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
15
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
16
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
17
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) */
18
insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567);
32
select * from t1 where a=1234.567;
35
delete from t1 where a=1234.567;
49
create table t2 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
50
partition by key (a) partitions 10;
53
t2 CREATE TABLE `t2` (
54
`a` decimal(18,9) NOT NULL,
56
) ENGINE=MyISAM DEFAULT CHARSET=latin1
57
/*!50100 PARTITION BY KEY (a)
59
insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567);
70
select * from t2 where a=1234.567;
73
delete from t2 where a=1234.567;
85
select count(*) from t2;
89
create table t3 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
90
partition by range (floor(a)) subpartition by key (a) subpartitions 2 (
91
partition pa2 values less than (2),
92
partition pa4 values less than (4),
93
partition pa6 values less than (6),
94
partition pa8 values less than (8),
95
partition pa10 values less than (10)
99
t3 CREATE TABLE `t3` (
100
`a` decimal(18,9) NOT NULL,
102
) ENGINE=MyISAM DEFAULT CHARSET=latin1
103
/*!50100 PARTITION BY RANGE (floor(a))
104
SUBPARTITION BY KEY (a)
106
(PARTITION pa2 VALUES LESS THAN (2) ENGINE = MyISAM,
107
PARTITION pa4 VALUES LESS THAN (4) ENGINE = MyISAM,
108
PARTITION pa6 VALUES LESS THAN (6) ENGINE = MyISAM,
109
PARTITION pa8 VALUES LESS THAN (8) ENGINE = MyISAM,
110
PARTITION pa10 VALUES LESS THAN (10) ENGINE = MyISAM) */
112
insert into t3 values (9);
113
insert into t3 values (9+0.333333333);
114
insert into t3 values (9+0.755555555);
115
insert into t3 values (8);
116
insert into t3 values (8+0.333333333);
117
insert into t3 values (8+0.755555555);
118
insert into t3 values (7);
119
insert into t3 values (7+0.333333333);
120
insert into t3 values (7+0.755555555);
121
insert into t3 values (6);
122
insert into t3 values (6+0.333333333);
123
insert into t3 values (6+0.755555555);
124
insert into t3 values (5);
125
insert into t3 values (5+0.333333333);
126
insert into t3 values (5+0.755555555);
127
insert into t3 values (4);
128
insert into t3 values (4+0.333333333);
129
insert into t3 values (4+0.755555555);
130
insert into t3 values (3);
131
insert into t3 values (3+0.333333333);
132
insert into t3 values (3+0.755555555);
133
insert into t3 values (2);
134
insert into t3 values (2+0.333333333);
135
insert into t3 values (2+0.755555555);
136
insert into t3 values (1);
137
insert into t3 values (1+0.333333333);
138
insert into t3 values (1+0.755555555);
139
select count(*) from t3;
143
create table t4 (a decimal(18,9) not null, primary key(a)) engine='MYISAM'
144
partition by list (ceiling(a)) subpartition by key (a) subpartitions 2 (
145
partition pa2 values in (1,2),
146
partition pa4 values in (3,4),
147
partition pa6 values in (5,6),
148
partition pa8 values in (7,8),
149
partition pa10 values in (9,10)
151
show create table t4;
153
t4 CREATE TABLE `t4` (
154
`a` decimal(18,9) NOT NULL,
156
) ENGINE=MyISAM DEFAULT CHARSET=latin1
157
/*!50100 PARTITION BY LIST (ceiling(a))
158
SUBPARTITION BY KEY (a)
160
(PARTITION pa2 VALUES IN (1,2) ENGINE = MyISAM,
161
PARTITION pa4 VALUES IN (3,4) ENGINE = MyISAM,
162
PARTITION pa6 VALUES IN (5,6) ENGINE = MyISAM,
163
PARTITION pa8 VALUES IN (7,8) ENGINE = MyISAM,
164
PARTITION pa10 VALUES IN (9,10) ENGINE = MyISAM) */
166
insert into t4 values (9);
167
insert into t4 values (9+0.333333333);
168
insert into t4 values (9+0.755555555);
169
insert into t4 values (8);
170
insert into t4 values (8+0.333333333);
171
insert into t4 values (8+0.755555555);
172
insert into t4 values (7);
173
insert into t4 values (7+0.333333333);
174
insert into t4 values (7+0.755555555);
175
insert into t4 values (6);
176
insert into t4 values (6+0.333333333);
177
insert into t4 values (6+0.755555555);
178
insert into t4 values (5);
179
insert into t4 values (5+0.333333333);
180
insert into t4 values (5+0.755555555);
181
insert into t4 values (4);
182
insert into t4 values (4+0.333333333);
183
insert into t4 values (4+0.755555555);
184
insert into t4 values (3);
185
insert into t4 values (3+0.333333333);
186
insert into t4 values (3+0.755555555);
187
insert into t4 values (2);
188
insert into t4 values (2+0.333333333);
189
insert into t4 values (2+0.755555555);
190
insert into t4 values (1);
191
insert into t4 values (1+0.333333333);
192
insert into t4 values (1+0.755555555);
193
select count(*) from t4;