1
CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
2
PARTITION p0 VALUES LESS THAN (10),
3
PARTITION p1 VALUES LESS THAN (20),
4
PARTITION p2 VALUES LESS THAN (30),
5
PARTITION p3 VALUES LESS THAN MAXVALUE);
6
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
7
SELECT * FROM t1 ORDER BY c1;
8
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
9
SELECT * FROM t1 ORDER BY c1;
11
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
12
--replace_result $ENGINE ENGINE
14
ALTER TABLE t1 COALESCE PARTITION 2;
15
SELECT * FROM t1 ORDER BY c1;
17
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
18
--replace_result $ENGINE ENGINE
19
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
20
CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
21
PARTITION p0 VALUES LESS THAN (10),
22
PARTITION p1 VALUES LESS THAN (20),
23
PARTITION p2 VALUES LESS THAN (30),
24
PARTITION p3 VALUES LESS THAN MAXVALUE);
25
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
26
SELECT * FROM t1 ORDER BY c1;
27
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
28
SELECT * FROM t1 ORDER BY c1;
30
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
31
--replace_result $ENGINE ENGINE
33
ALTER TABLE t1 COALESCE PARTITION 2;
34
SELECT * FROM t1 ORDER BY c1;
36
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
37
--replace_result $ENGINE ENGINE
38
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
39
CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
40
PARTITION p0 VALUES LESS THAN (10),
41
PARTITION p1 VALUES LESS THAN (20),
42
PARTITION p2 VALUES LESS THAN (30),
43
PARTITION p3 VALUES LESS THAN MAXVALUE);
44
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
45
SELECT * FROM t1 ORDER BY c1;
46
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
47
SELECT * FROM t1 ORDER BY c1;
49
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
50
--replace_result $ENGINE ENGINE
52
ALTER TABLE t1 COALESCE PARTITION 2;
53
SELECT * FROM t1 ORDER BY c1;
55
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
56
--replace_result $ENGINE ENGINE
57
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
58
CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
59
PARTITION p0 VALUES LESS THAN (10),
60
PARTITION p1 VALUES LESS THAN (20),
61
PARTITION p2 VALUES LESS THAN (30),
62
PARTITION p3 VALUES LESS THAN MAXVALUE);
63
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
64
SELECT * FROM t1 ORDER BY c1;
65
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
66
SELECT * FROM t1 ORDER BY c1;
68
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
69
--replace_result $ENGINE ENGINE
71
ALTER TABLE t1 COALESCE PARTITION 2;
72
SELECT * FROM t1 ORDER BY c1;
74
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
75
--replace_result $ENGINE ENGINE
76
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
77
CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
78
PARTITION p0 VALUES LESS THAN (10),
79
PARTITION p1 VALUES LESS THAN (20),
80
PARTITION p2 VALUES LESS THAN (30),
81
PARTITION p3 VALUES LESS THAN MAXVALUE);
82
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
83
SELECT * FROM t1 ORDER BY c1;
84
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
85
SELECT * FROM t1 ORDER BY c1;
87
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
88
--replace_result $ENGINE ENGINE
90
ALTER TABLE t1 COALESCE PARTITION 2;
91
SELECT * FROM t1 ORDER BY c1;
93
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
94
--replace_result $ENGINE ENGINE
95
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
96
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
97
PARTITION p0 VALUES LESS THAN (10),
98
PARTITION p1 VALUES LESS THAN (20),
99
PARTITION p2 VALUES LESS THAN (30),
100
PARTITION p3 VALUES LESS THAN MAXVALUE);
101
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
102
SELECT * FROM t1 ORDER BY c1;
103
ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
104
SELECT * FROM t1 ORDER BY c1;
106
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
107
--replace_result $ENGINE ENGINE
108
SHOW CREATE TABLE t1;
109
ALTER TABLE t1 COALESCE PARTITION 2;
110
SELECT * FROM t1 ORDER BY c1;
112
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
113
--replace_result $ENGINE ENGINE
114
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
115
CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
116
PARTITION p0 VALUES LESS THAN (10),
117
PARTITION p1 VALUES LESS THAN (20),
118
PARTITION p2 VALUES LESS THAN (30),
119
PARTITION p3 VALUES LESS THAN MAXVALUE);
120
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
121
SELECT * FROM t1 ORDER BY c1;
122
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
123
SELECT * FROM t1 ORDER BY c1;
125
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
126
--replace_result $ENGINE ENGINE
127
SHOW CREATE TABLE t1;
128
ALTER TABLE t1 COALESCE PARTITION 2;
129
SELECT * FROM t1 ORDER BY c1;
131
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
132
--replace_result $ENGINE ENGINE
133
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
134
CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
135
PARTITION p0 VALUES LESS THAN (10),
136
PARTITION p1 VALUES LESS THAN (20),
137
PARTITION p2 VALUES LESS THAN (30),
138
PARTITION p3 VALUES LESS THAN MAXVALUE);
139
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
140
SELECT * FROM t1 ORDER BY c1;
141
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
142
SELECT * FROM t1 ORDER BY c1;
144
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
145
--replace_result $ENGINE ENGINE
146
SHOW CREATE TABLE t1;
147
ALTER TABLE t1 COALESCE PARTITION 2;
148
SELECT * FROM t1 ORDER BY c1;
150
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
151
--replace_result $ENGINE ENGINE
152
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
153
CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
154
PARTITION p0 VALUES LESS THAN (10),
155
PARTITION p1 VALUES LESS THAN (20),
156
PARTITION p2 VALUES LESS THAN (30),
157
PARTITION p3 VALUES LESS THAN MAXVALUE);
158
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
159
SELECT * FROM t1 ORDER BY c1;
160
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
161
SELECT * FROM t1 ORDER BY c1;
163
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
164
--replace_result $ENGINE ENGINE
165
SHOW CREATE TABLE t1;
166
ALTER TABLE t1 COALESCE PARTITION 2;
167
SELECT * FROM t1 ORDER BY c1;
169
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
170
--replace_result $ENGINE ENGINE
171
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
172
CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
173
PARTITION p0 VALUES LESS THAN (10),
174
PARTITION p1 VALUES LESS THAN (20),
175
PARTITION p2 VALUES LESS THAN (30),
176
PARTITION p3 VALUES LESS THAN MAXVALUE);
177
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
178
SELECT * FROM t1 ORDER BY c1;
179
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
180
SELECT * FROM t1 ORDER BY c1;
182
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
183
--replace_result $ENGINE ENGINE
184
SHOW CREATE TABLE t1;
185
ALTER TABLE t1 COALESCE PARTITION 2;
186
SELECT * FROM t1 ORDER BY c1;
188
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
189
--replace_result $ENGINE ENGINE
190
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
191
CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
192
PARTITION p0 VALUES LESS THAN (10),
193
PARTITION p1 VALUES LESS THAN (20),
194
PARTITION p2 VALUES LESS THAN (30),
195
PARTITION p3 VALUES LESS THAN MAXVALUE);
196
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
197
SELECT * FROM t1 ORDER BY c1;
198
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
199
SELECT * FROM t1 ORDER BY c1;
201
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
202
--replace_result $ENGINE ENGINE
203
SHOW CREATE TABLE t1;
204
ALTER TABLE t1 COALESCE PARTITION 2;
205
SELECT * FROM t1 ORDER BY c1;
207
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
208
--replace_result $ENGINE ENGINE
209
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
210
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
211
PARTITION p0 VALUES LESS THAN (10),
212
PARTITION p1 VALUES LESS THAN (20),
213
PARTITION p2 VALUES LESS THAN (30),
214
PARTITION p3 VALUES LESS THAN MAXVALUE);
215
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
216
SELECT * FROM t1 ORDER BY c1;
217
ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
218
SELECT * FROM t1 ORDER BY c1;
220
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
221
--replace_result $ENGINE ENGINE
222
SHOW CREATE TABLE t1;
223
ALTER TABLE t1 COALESCE PARTITION 2;
224
SELECT * FROM t1 ORDER BY c1;
226
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
227
--replace_result $ENGINE ENGINE
228
SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;