Debian Codegraph @

mysql-test/suite/parts/r/partition_change_from_range_to_hash_key.result

raw
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 c1 c2
9 0 abc
10 5 abc
11 10 abc
12 15 abc
13 20 abc
14 25 abc
15 30 abc
16 35 abc
17 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
18 SELECT * FROM t1 ORDER BY c1;
19 c1 c2
20 0 abc
21 5 abc
22 10 abc
23 15 abc
24 20 abc
25 25 abc
26 30 abc
27 35 abc
28 SHOW TABLES;
29 Tables_in_test
30 t1
31 SHOW CREATE TABLE t1;
32 Table Create Table
33 t1 CREATE TABLE `t1` (
34 `c1` tinyint NOT NULL,
35 `c2` char(5) DEFAULT NULL
36 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
37 /*!50100 PARTITION BY HASH (`c1`)
38 PARTITIONS 5 */
39 ALTER TABLE t1 COALESCE PARTITION 2;
40 SELECT * FROM t1 ORDER BY c1;
41 c1 c2
42 0 abc
43 5 abc
44 10 abc
45 15 abc
46 20 abc
47 25 abc
48 30 abc
49 35 abc
50 SHOW TABLES;
51 Tables_in_test
52 t1
53 SHOW CREATE TABLE t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `c1` tinyint NOT NULL,
57 `c2` char(5) DEFAULT NULL
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
59 /*!50100 PARTITION BY HASH (`c1`)
60 PARTITIONS 3 */
61 DROP TABLE t1;
62 SHOW TABLES;
63 Tables_in_test
64 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
65 PARTITION p0 VALUES LESS THAN (10),
66 PARTITION p1 VALUES LESS THAN (20),
67 PARTITION p2 VALUES LESS THAN (30),
68 PARTITION p3 VALUES LESS THAN MAXVALUE);
69 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
70 SELECT * FROM t1 ORDER BY c1;
71 c1 c2
72 0 abc
73 5 abc
74 10 abc
75 15 abc
76 20 abc
77 25 abc
78 30 abc
79 35 abc
80 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
81 SELECT * FROM t1 ORDER BY c1;
82 c1 c2
83 0 abc
84 5 abc
85 10 abc
86 15 abc
87 20 abc
88 25 abc
89 30 abc
90 35 abc
91 SHOW TABLES;
92 Tables_in_test
93 t1
94 SHOW CREATE TABLE t1;
95 Table Create Table
96 t1 CREATE TABLE `t1` (
97 `c1` smallint NOT NULL,
98 `c2` char(5) DEFAULT NULL
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
100 /*!50100 PARTITION BY HASH (`c1`)
101 PARTITIONS 5 */
102 ALTER TABLE t1 COALESCE PARTITION 2;
103 SELECT * FROM t1 ORDER BY c1;
104 c1 c2
105 0 abc
106 5 abc
107 10 abc
108 15 abc
109 20 abc
110 25 abc
111 30 abc
112 35 abc
113 SHOW TABLES;
114 Tables_in_test
115 t1
116 SHOW CREATE TABLE t1;
117 Table Create Table
118 t1 CREATE TABLE `t1` (
119 `c1` smallint NOT NULL,
120 `c2` char(5) DEFAULT NULL
121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
122 /*!50100 PARTITION BY HASH (`c1`)
123 PARTITIONS 3 */
124 DROP TABLE t1;
125 SHOW TABLES;
126 Tables_in_test
127 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
128 PARTITION p0 VALUES LESS THAN (10),
129 PARTITION p1 VALUES LESS THAN (20),
130 PARTITION p2 VALUES LESS THAN (30),
131 PARTITION p3 VALUES LESS THAN MAXVALUE);
132 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
133 SELECT * FROM t1 ORDER BY c1;
134 c1 c2
135 0 abc
136 5 abc
137 10 abc
138 15 abc
139 20 abc
140 25 abc
141 30 abc
142 35 abc
143 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
144 SELECT * FROM t1 ORDER BY c1;
145 c1 c2
146 0 abc
147 5 abc
148 10 abc
149 15 abc
150 20 abc
151 25 abc
152 30 abc
153 35 abc
154 SHOW TABLES;
155 Tables_in_test
156 t1
157 SHOW CREATE TABLE t1;
158 Table Create Table
159 t1 CREATE TABLE `t1` (
160 `c1` mediumint NOT NULL,
161 `c2` char(5) DEFAULT NULL
162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
163 /*!50100 PARTITION BY HASH (`c1`)
164 PARTITIONS 5 */
165 ALTER TABLE t1 COALESCE PARTITION 2;
166 SELECT * FROM t1 ORDER BY c1;
167 c1 c2
168 0 abc
169 5 abc
170 10 abc
171 15 abc
172 20 abc
173 25 abc
174 30 abc
175 35 abc
176 SHOW TABLES;
177 Tables_in_test
178 t1
179 SHOW CREATE TABLE t1;
180 Table Create Table
181 t1 CREATE TABLE `t1` (
182 `c1` mediumint NOT NULL,
183 `c2` char(5) DEFAULT NULL
184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
185 /*!50100 PARTITION BY HASH (`c1`)
186 PARTITIONS 3 */
187 DROP TABLE t1;
188 SHOW TABLES;
189 Tables_in_test
190 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
191 PARTITION p0 VALUES LESS THAN (10),
192 PARTITION p1 VALUES LESS THAN (20),
193 PARTITION p2 VALUES LESS THAN (30),
194 PARTITION p3 VALUES LESS THAN MAXVALUE);
195 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
196 SELECT * FROM t1 ORDER BY c1;
197 c1 c2
198 0 abc
199 5 abc
200 10 abc
201 15 abc
202 20 abc
203 25 abc
204 30 abc
205 35 abc
206 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
207 SELECT * FROM t1 ORDER BY c1;
208 c1 c2
209 0 abc
210 5 abc
211 10 abc
212 15 abc
213 20 abc
214 25 abc
215 30 abc
216 35 abc
217 SHOW TABLES;
218 Tables_in_test
219 t1
220 SHOW CREATE TABLE t1;
221 Table Create Table
222 t1 CREATE TABLE `t1` (
223 `c1` int NOT NULL,
224 `c2` char(5) DEFAULT NULL
225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
226 /*!50100 PARTITION BY HASH (`c1`)
227 PARTITIONS 5 */
228 ALTER TABLE t1 COALESCE PARTITION 2;
229 SELECT * FROM t1 ORDER BY c1;
230 c1 c2
231 0 abc
232 5 abc
233 10 abc
234 15 abc
235 20 abc
236 25 abc
237 30 abc
238 35 abc
239 SHOW TABLES;
240 Tables_in_test
241 t1
242 SHOW CREATE TABLE t1;
243 Table Create Table
244 t1 CREATE TABLE `t1` (
245 `c1` int NOT NULL,
246 `c2` char(5) DEFAULT NULL
247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
248 /*!50100 PARTITION BY HASH (`c1`)
249 PARTITIONS 3 */
250 DROP TABLE t1;
251 SHOW TABLES;
252 Tables_in_test
253 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
254 PARTITION p0 VALUES LESS THAN (10),
255 PARTITION p1 VALUES LESS THAN (20),
256 PARTITION p2 VALUES LESS THAN (30),
257 PARTITION p3 VALUES LESS THAN MAXVALUE);
258 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
259 SELECT * FROM t1 ORDER BY c1;
260 c1 c2
261 0 abc
262 5 abc
263 10 abc
264 15 abc
265 20 abc
266 25 abc
267 30 abc
268 35 abc
269 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
270 SELECT * FROM t1 ORDER BY c1;
271 c1 c2
272 0 abc
273 5 abc
274 10 abc
275 15 abc
276 20 abc
277 25 abc
278 30 abc
279 35 abc
280 SHOW TABLES;
281 Tables_in_test
282 t1
283 SHOW CREATE TABLE t1;
284 Table Create Table
285 t1 CREATE TABLE `t1` (
286 `c1` int NOT NULL,
287 `c2` char(5) DEFAULT NULL
288 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
289 /*!50100 PARTITION BY HASH (`c1`)
290 PARTITIONS 5 */
291 ALTER TABLE t1 COALESCE PARTITION 2;
292 SELECT * FROM t1 ORDER BY c1;
293 c1 c2
294 0 abc
295 5 abc
296 10 abc
297 15 abc
298 20 abc
299 25 abc
300 30 abc
301 35 abc
302 SHOW TABLES;
303 Tables_in_test
304 t1
305 SHOW CREATE TABLE t1;
306 Table Create Table
307 t1 CREATE TABLE `t1` (
308 `c1` int NOT NULL,
309 `c2` char(5) DEFAULT NULL
310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
311 /*!50100 PARTITION BY HASH (`c1`)
312 PARTITIONS 3 */
313 DROP TABLE t1;
314 SHOW TABLES;
315 Tables_in_test
316 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
317 PARTITION p0 VALUES LESS THAN (10),
318 PARTITION p1 VALUES LESS THAN (20),
319 PARTITION p2 VALUES LESS THAN (30),
320 PARTITION p3 VALUES LESS THAN MAXVALUE);
321 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
322 SELECT * FROM t1 ORDER BY c1;
323 c1 c2
324 0 abc
325 5 abc
326 10 abc
327 15 abc
328 20 abc
329 25 abc
330 30 abc
331 35 abc
332 ALTER TABLE t1 PARTITION BY HASH(c1) PARTITIONS 5;
333 SELECT * FROM t1 ORDER BY c1;
334 c1 c2
335 0 abc
336 5 abc
337 10 abc
338 15 abc
339 20 abc
340 25 abc
341 30 abc
342 35 abc
343 SHOW TABLES;
344 Tables_in_test
345 t1
346 SHOW CREATE TABLE t1;
347 Table Create Table
348 t1 CREATE TABLE `t1` (
349 `c1` bigint NOT NULL,
350 `c2` char(5) DEFAULT NULL
351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
352 /*!50100 PARTITION BY HASH (`c1`)
353 PARTITIONS 5 */
354 ALTER TABLE t1 COALESCE PARTITION 2;
355 SELECT * FROM t1 ORDER BY c1;
356 c1 c2
357 0 abc
358 5 abc
359 10 abc
360 15 abc
361 20 abc
362 25 abc
363 30 abc
364 35 abc
365 SHOW TABLES;
366 Tables_in_test
367 t1
368 SHOW CREATE TABLE t1;
369 Table Create Table
370 t1 CREATE TABLE `t1` (
371 `c1` bigint NOT NULL,
372 `c2` char(5) DEFAULT NULL
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
374 /*!50100 PARTITION BY HASH (`c1`)
375 PARTITIONS 3 */
376 DROP TABLE t1;
377 SHOW TABLES;
378 Tables_in_test
379 CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
380 PARTITION p0 VALUES LESS THAN (10),
381 PARTITION p1 VALUES LESS THAN (20),
382 PARTITION p2 VALUES LESS THAN (30),
383 PARTITION p3 VALUES LESS THAN MAXVALUE);
384 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
385 SELECT * FROM t1 ORDER BY c1;
386 c1 c2
387 0 abc
388 5 abc
389 10 abc
390 15 abc
391 20 abc
392 25 abc
393 30 abc
394 35 abc
395 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
396 SELECT * FROM t1 ORDER BY c1;
397 c1 c2
398 0 abc
399 5 abc
400 10 abc
401 15 abc
402 20 abc
403 25 abc
404 30 abc
405 35 abc
406 SHOW TABLES;
407 Tables_in_test
408 t1
409 SHOW CREATE TABLE t1;
410 Table Create Table
411 t1 CREATE TABLE `t1` (
412 `c1` tinyint NOT NULL,
413 `c2` char(5) DEFAULT NULL
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
415 /*!50100 PARTITION BY KEY (c1)
416 PARTITIONS 5 */
417 ALTER TABLE t1 COALESCE PARTITION 2;
418 SELECT * FROM t1 ORDER BY c1;
419 c1 c2
420 0 abc
421 5 abc
422 10 abc
423 15 abc
424 20 abc
425 25 abc
426 30 abc
427 35 abc
428 SHOW TABLES;
429 Tables_in_test
430 t1
431 SHOW CREATE TABLE t1;
432 Table Create Table
433 t1 CREATE TABLE `t1` (
434 `c1` tinyint NOT NULL,
435 `c2` char(5) DEFAULT NULL
436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
437 /*!50100 PARTITION BY KEY (c1)
438 PARTITIONS 3 */
439 DROP TABLE t1;
440 SHOW TABLES;
441 Tables_in_test
442 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
443 PARTITION p0 VALUES LESS THAN (10),
444 PARTITION p1 VALUES LESS THAN (20),
445 PARTITION p2 VALUES LESS THAN (30),
446 PARTITION p3 VALUES LESS THAN MAXVALUE);
447 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
448 SELECT * FROM t1 ORDER BY c1;
449 c1 c2
450 0 abc
451 5 abc
452 10 abc
453 15 abc
454 20 abc
455 25 abc
456 30 abc
457 35 abc
458 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
459 SELECT * FROM t1 ORDER BY c1;
460 c1 c2
461 0 abc
462 5 abc
463 10 abc
464 15 abc
465 20 abc
466 25 abc
467 30 abc
468 35 abc
469 SHOW TABLES;
470 Tables_in_test
471 t1
472 SHOW CREATE TABLE t1;
473 Table Create Table
474 t1 CREATE TABLE `t1` (
475 `c1` smallint NOT NULL,
476 `c2` char(5) DEFAULT NULL
477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
478 /*!50100 PARTITION BY KEY (c1)
479 PARTITIONS 5 */
480 ALTER TABLE t1 COALESCE PARTITION 2;
481 SELECT * FROM t1 ORDER BY c1;
482 c1 c2
483 0 abc
484 5 abc
485 10 abc
486 15 abc
487 20 abc
488 25 abc
489 30 abc
490 35 abc
491 SHOW TABLES;
492 Tables_in_test
493 t1
494 SHOW CREATE TABLE t1;
495 Table Create Table
496 t1 CREATE TABLE `t1` (
497 `c1` smallint NOT NULL,
498 `c2` char(5) DEFAULT NULL
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
500 /*!50100 PARTITION BY KEY (c1)
501 PARTITIONS 3 */
502 DROP TABLE t1;
503 SHOW TABLES;
504 Tables_in_test
505 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
506 PARTITION p0 VALUES LESS THAN (10),
507 PARTITION p1 VALUES LESS THAN (20),
508 PARTITION p2 VALUES LESS THAN (30),
509 PARTITION p3 VALUES LESS THAN MAXVALUE);
510 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
511 SELECT * FROM t1 ORDER BY c1;
512 c1 c2
513 0 abc
514 5 abc
515 10 abc
516 15 abc
517 20 abc
518 25 abc
519 30 abc
520 35 abc
521 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
522 SELECT * FROM t1 ORDER BY c1;
523 c1 c2
524 0 abc
525 5 abc
526 10 abc
527 15 abc
528 20 abc
529 25 abc
530 30 abc
531 35 abc
532 SHOW TABLES;
533 Tables_in_test
534 t1
535 SHOW CREATE TABLE t1;
536 Table Create Table
537 t1 CREATE TABLE `t1` (
538 `c1` mediumint NOT NULL,
539 `c2` char(5) DEFAULT NULL
540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
541 /*!50100 PARTITION BY KEY (c1)
542 PARTITIONS 5 */
543 ALTER TABLE t1 COALESCE PARTITION 2;
544 SELECT * FROM t1 ORDER BY c1;
545 c1 c2
546 0 abc
547 5 abc
548 10 abc
549 15 abc
550 20 abc
551 25 abc
552 30 abc
553 35 abc
554 SHOW TABLES;
555 Tables_in_test
556 t1
557 SHOW CREATE TABLE t1;
558 Table Create Table
559 t1 CREATE TABLE `t1` (
560 `c1` mediumint NOT NULL,
561 `c2` char(5) DEFAULT NULL
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
563 /*!50100 PARTITION BY KEY (c1)
564 PARTITIONS 3 */
565 DROP TABLE t1;
566 SHOW TABLES;
567 Tables_in_test
568 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
569 PARTITION p0 VALUES LESS THAN (10),
570 PARTITION p1 VALUES LESS THAN (20),
571 PARTITION p2 VALUES LESS THAN (30),
572 PARTITION p3 VALUES LESS THAN MAXVALUE);
573 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
574 SELECT * FROM t1 ORDER BY c1;
575 c1 c2
576 0 abc
577 5 abc
578 10 abc
579 15 abc
580 20 abc
581 25 abc
582 30 abc
583 35 abc
584 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
585 SELECT * FROM t1 ORDER BY c1;
586 c1 c2
587 0 abc
588 5 abc
589 10 abc
590 15 abc
591 20 abc
592 25 abc
593 30 abc
594 35 abc
595 SHOW TABLES;
596 Tables_in_test
597 t1
598 SHOW CREATE TABLE t1;
599 Table Create Table
600 t1 CREATE TABLE `t1` (
601 `c1` int NOT NULL,
602 `c2` char(5) DEFAULT NULL
603 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
604 /*!50100 PARTITION BY KEY (c1)
605 PARTITIONS 5 */
606 ALTER TABLE t1 COALESCE PARTITION 2;
607 SELECT * FROM t1 ORDER BY c1;
608 c1 c2
609 0 abc
610 5 abc
611 10 abc
612 15 abc
613 20 abc
614 25 abc
615 30 abc
616 35 abc
617 SHOW TABLES;
618 Tables_in_test
619 t1
620 SHOW CREATE TABLE t1;
621 Table Create Table
622 t1 CREATE TABLE `t1` (
623 `c1` int NOT NULL,
624 `c2` char(5) DEFAULT NULL
625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
626 /*!50100 PARTITION BY KEY (c1)
627 PARTITIONS 3 */
628 DROP TABLE t1;
629 SHOW TABLES;
630 Tables_in_test
631 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
632 PARTITION p0 VALUES LESS THAN (10),
633 PARTITION p1 VALUES LESS THAN (20),
634 PARTITION p2 VALUES LESS THAN (30),
635 PARTITION p3 VALUES LESS THAN MAXVALUE);
636 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
637 SELECT * FROM t1 ORDER BY c1;
638 c1 c2
639 0 abc
640 5 abc
641 10 abc
642 15 abc
643 20 abc
644 25 abc
645 30 abc
646 35 abc
647 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
648 SELECT * FROM t1 ORDER BY c1;
649 c1 c2
650 0 abc
651 5 abc
652 10 abc
653 15 abc
654 20 abc
655 25 abc
656 30 abc
657 35 abc
658 SHOW TABLES;
659 Tables_in_test
660 t1
661 SHOW CREATE TABLE t1;
662 Table Create Table
663 t1 CREATE TABLE `t1` (
664 `c1` int NOT NULL,
665 `c2` char(5) DEFAULT NULL
666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
667 /*!50100 PARTITION BY KEY (c1)
668 PARTITIONS 5 */
669 ALTER TABLE t1 COALESCE PARTITION 2;
670 SELECT * FROM t1 ORDER BY c1;
671 c1 c2
672 0 abc
673 5 abc
674 10 abc
675 15 abc
676 20 abc
677 25 abc
678 30 abc
679 35 abc
680 SHOW TABLES;
681 Tables_in_test
682 t1
683 SHOW CREATE TABLE t1;
684 Table Create Table
685 t1 CREATE TABLE `t1` (
686 `c1` int NOT NULL,
687 `c2` char(5) DEFAULT NULL
688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
689 /*!50100 PARTITION BY KEY (c1)
690 PARTITIONS 3 */
691 DROP TABLE t1;
692 SHOW TABLES;
693 Tables_in_test
694 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
695 PARTITION p0 VALUES LESS THAN (10),
696 PARTITION p1 VALUES LESS THAN (20),
697 PARTITION p2 VALUES LESS THAN (30),
698 PARTITION p3 VALUES LESS THAN MAXVALUE);
699 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
700 SELECT * FROM t1 ORDER BY c1;
701 c1 c2
702 0 abc
703 5 abc
704 10 abc
705 15 abc
706 20 abc
707 25 abc
708 30 abc
709 35 abc
710 ALTER TABLE t1 PARTITION BY KEY(c1) PARTITIONS 5;
711 SELECT * FROM t1 ORDER BY c1;
712 c1 c2
713 0 abc
714 5 abc
715 10 abc
716 15 abc
717 20 abc
718 25 abc
719 30 abc
720 35 abc
721 SHOW TABLES;
722 Tables_in_test
723 t1
724 SHOW CREATE TABLE t1;
725 Table Create Table
726 t1 CREATE TABLE `t1` (
727 `c1` bigint NOT NULL,
728 `c2` char(5) DEFAULT NULL
729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
730 /*!50100 PARTITION BY KEY (c1)
731 PARTITIONS 5 */
732 ALTER TABLE t1 COALESCE PARTITION 2;
733 SELECT * FROM t1 ORDER BY c1;
734 c1 c2
735 0 abc
736 5 abc
737 10 abc
738 15 abc
739 20 abc
740 25 abc
741 30 abc
742 35 abc
743 SHOW TABLES;
744 Tables_in_test
745 t1
746 SHOW CREATE TABLE t1;
747 Table Create Table
748 t1 CREATE TABLE `t1` (
749 `c1` bigint NOT NULL,
750 `c2` char(5) DEFAULT NULL
751 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
752 /*!50100 PARTITION BY KEY (c1)
753 PARTITIONS 3 */
754 DROP TABLE t1;
755 SHOW TABLES;
756 Tables_in_test