Debian Codegraph @

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

raw
1 create table t1 (a timestamp not null, primary key(a)) engine='InnoDB'
2 partition by key (a) (
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);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` timestamp NOT NULL,
11 PRIMARY KEY (`a`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
13 /*!50100 PARTITION BY KEY (a)
14 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
15 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
16 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
17 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
18 insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
19 select * from t1;
20 a
21 2000-06-15 23:59:00
22 2020-12-31 12:10:30
23 1975-01-01 21:21:21
24 1980-10-14 03:03:00
25 select * from t1 where a=19801014030300;
26 a
27 1980-10-14 03:03:00
28 delete from t1 where a=19801014030300;
29 select * from t1;
30 a
31 2000-06-15 23:59:00
32 2020-12-31 12:10:30
33 1975-01-01 21:21:21
34 drop table t1;
35 create table t2 (a timestamp not null, primary key(a)) engine='InnoDB'
36 partition by key (a) partitions 12;
37 show create table t2;
38 Table Create Table
39 t2 CREATE TABLE `t2` (
40 `a` timestamp NOT NULL,
41 PRIMARY KEY (`a`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 /*!50100 PARTITION BY KEY (a)
44 PARTITIONS 12 */
45 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
46 select * from t2;
47 a
48 1980-10-14 13:14:15
49 2000-06-15 14:15:16
50 2020-12-31 10:11:12
51 1975-01-01 00:01:01
52 select * from t2 where a='1980-10-14 13:14:15';
53 a
54 1980-10-14 13:14:15
55 delete from t2 where a='1980-10-14 13:14:15';
56 select * from t2;
57 a
58 2000-06-15 14:15:16
59 2020-12-31 10:11:12
60 1975-01-01 00:01:01
61 delete from t2;
62 59 inserts;
63 insert into t2 values (19710101000000+59);
64 insert into t2 values (19710101000000+58);
65 insert into t2 values (19710101000000+57);
66 insert into t2 values (19710101000000+56);
67 insert into t2 values (19710101000000+55);
68 insert into t2 values (19710101000000+54);
69 insert into t2 values (19710101000000+53);
70 insert into t2 values (19710101000000+52);
71 insert into t2 values (19710101000000+51);
72 insert into t2 values (19710101000000+50);
73 insert into t2 values (19710101000000+49);
74 insert into t2 values (19710101000000+48);
75 insert into t2 values (19710101000000+47);
76 insert into t2 values (19710101000000+46);
77 insert into t2 values (19710101000000+45);
78 insert into t2 values (19710101000000+44);
79 insert into t2 values (19710101000000+43);
80 insert into t2 values (19710101000000+42);
81 insert into t2 values (19710101000000+41);
82 insert into t2 values (19710101000000+40);
83 insert into t2 values (19710101000000+39);
84 insert into t2 values (19710101000000+38);
85 insert into t2 values (19710101000000+37);
86 insert into t2 values (19710101000000+36);
87 insert into t2 values (19710101000000+35);
88 insert into t2 values (19710101000000+34);
89 insert into t2 values (19710101000000+33);
90 insert into t2 values (19710101000000+32);
91 insert into t2 values (19710101000000+31);
92 insert into t2 values (19710101000000+30);
93 insert into t2 values (19710101000000+29);
94 insert into t2 values (19710101000000+28);
95 insert into t2 values (19710101000000+27);
96 insert into t2 values (19710101000000+26);
97 insert into t2 values (19710101000000+25);
98 insert into t2 values (19710101000000+24);
99 insert into t2 values (19710101000000+23);
100 insert into t2 values (19710101000000+22);
101 insert into t2 values (19710101000000+21);
102 insert into t2 values (19710101000000+20);
103 insert into t2 values (19710101000000+19);
104 insert into t2 values (19710101000000+18);
105 insert into t2 values (19710101000000+17);
106 insert into t2 values (19710101000000+16);
107 insert into t2 values (19710101000000+15);
108 insert into t2 values (19710101000000+14);
109 insert into t2 values (19710101000000+13);
110 insert into t2 values (19710101000000+12);
111 insert into t2 values (19710101000000+11);
112 insert into t2 values (19710101000000+10);
113 insert into t2 values (19710101000000+9);
114 insert into t2 values (19710101000000+8);
115 insert into t2 values (19710101000000+7);
116 insert into t2 values (19710101000000+6);
117 insert into t2 values (19710101000000+5);
118 insert into t2 values (19710101000000+4);
119 insert into t2 values (19710101000000+3);
120 insert into t2 values (19710101000000+2);
121 insert into t2 values (19710101000000+1);
122 select count(*) from t2;
123 count(*)
124 59
125 select * from t2;
126 a
127 1971-01-01 00:00:14
128 1971-01-01 00:00:34
129 1971-01-01 00:00:42
130 1971-01-01 00:00:58
131 1971-01-01 00:00:21
132 1971-01-01 00:00:29
133 1971-01-01 00:00:37
134 1971-01-01 00:00:53
135 1971-01-01 00:00:57
136 1971-01-01 00:00:04
137 1971-01-01 00:00:08
138 1971-01-01 00:00:20
139 1971-01-01 00:00:36
140 1971-01-01 00:00:44
141 1971-01-01 00:00:11
142 1971-01-01 00:00:23
143 1971-01-01 00:00:27
144 1971-01-01 00:00:31
145 1971-01-01 00:00:02
146 1971-01-01 00:00:06
147 1971-01-01 00:00:10
148 1971-01-01 00:00:22
149 1971-01-01 00:00:38
150 1971-01-01 00:00:50
151 1971-01-01 00:00:17
152 1971-01-01 00:00:33
153 1971-01-01 00:00:45
154 1971-01-01 00:00:12
155 1971-01-01 00:00:52
156 1971-01-01 00:00:56
157 1971-01-01 00:00:07
158 1971-01-01 00:00:19
159 1971-01-01 00:00:35
160 1971-01-01 00:00:39
161 1971-01-01 00:00:47
162 1971-01-01 00:00:51
163 1971-01-01 00:00:18
164 1971-01-01 00:00:26
165 1971-01-01 00:00:30
166 1971-01-01 00:00:46
167 1971-01-01 00:00:54
168 1971-01-01 00:00:01
169 1971-01-01 00:00:05
170 1971-01-01 00:00:09
171 1971-01-01 00:00:13
172 1971-01-01 00:00:25
173 1971-01-01 00:00:41
174 1971-01-01 00:00:49
175 1971-01-01 00:00:16
176 1971-01-01 00:00:24
177 1971-01-01 00:00:28
178 1971-01-01 00:00:32
179 1971-01-01 00:00:40
180 1971-01-01 00:00:48
181 1971-01-01 00:00:03
182 1971-01-01 00:00:15
183 1971-01-01 00:00:43
184 1971-01-01 00:00:55
185 1971-01-01 00:00:59
186 drop table t2;
187 create table t1 (a date not null, primary key(a)) engine='InnoDB'
188 partition by key (a) (
189 partition pa1 max_rows=20 min_rows=2,
190 partition pa2 max_rows=30 min_rows=3,
191 partition pa3 max_rows=30 min_rows=4,
192 partition pa4 max_rows=40 min_rows=2);
193 show create table t1;
194 Table Create Table
195 t1 CREATE TABLE `t1` (
196 `a` date NOT NULL,
197 PRIMARY KEY (`a`)
198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
199 /*!50100 PARTITION BY KEY (a)
200 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
201 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
202 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
203 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
204 insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
205 select * from t1;
206 a
207 1980-10-14
208 2020-12-31
209 1975-01-01
210 2000-06-15
211 select * from t1 where a=19801014;
212 a
213 1980-10-14
214 delete from t1 where a=19801014;
215 select * from t1;
216 a
217 2020-12-31
218 1975-01-01
219 2000-06-15
220 drop table t1;
221 create table t2 (a date not null, primary key(a)) engine='InnoDB'
222 partition by key (a) partitions 12;
223 show create table t2;
224 Table Create Table
225 t2 CREATE TABLE `t2` (
226 `a` date NOT NULL,
227 PRIMARY KEY (`a`)
228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 /*!50100 PARTITION BY KEY (a)
230 PARTITIONS 12 */
231 insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
232 select * from t2;
233 a
234 1980-10-14
235 1975-01-01
236 2020-12-31
237 2000-06-15
238 select * from t2 where a='1980-10-14';
239 a
240 1980-10-14
241 delete from t2 where a='1980-10-14';
242 select * from t2;
243 a
244 1975-01-01
245 2020-12-31
246 2000-06-15
247 delete from t2;
248 28 inserts;
249 insert into t2 values (19700101+28-1);
250 insert into t2 values (19700201+28-1);
251 insert into t2 values (19700301+28-1);
252 insert into t2 values (19700101+27-1);
253 insert into t2 values (19700201+27-1);
254 insert into t2 values (19700301+27-1);
255 insert into t2 values (19700101+26-1);
256 insert into t2 values (19700201+26-1);
257 insert into t2 values (19700301+26-1);
258 insert into t2 values (19700101+25-1);
259 insert into t2 values (19700201+25-1);
260 insert into t2 values (19700301+25-1);
261 insert into t2 values (19700101+24-1);
262 insert into t2 values (19700201+24-1);
263 insert into t2 values (19700301+24-1);
264 insert into t2 values (19700101+23-1);
265 insert into t2 values (19700201+23-1);
266 insert into t2 values (19700301+23-1);
267 insert into t2 values (19700101+22-1);
268 insert into t2 values (19700201+22-1);
269 insert into t2 values (19700301+22-1);
270 insert into t2 values (19700101+21-1);
271 insert into t2 values (19700201+21-1);
272 insert into t2 values (19700301+21-1);
273 insert into t2 values (19700101+20-1);
274 insert into t2 values (19700201+20-1);
275 insert into t2 values (19700301+20-1);
276 insert into t2 values (19700101+19-1);
277 insert into t2 values (19700201+19-1);
278 insert into t2 values (19700301+19-1);
279 insert into t2 values (19700101+18-1);
280 insert into t2 values (19700201+18-1);
281 insert into t2 values (19700301+18-1);
282 insert into t2 values (19700101+17-1);
283 insert into t2 values (19700201+17-1);
284 insert into t2 values (19700301+17-1);
285 insert into t2 values (19700101+16-1);
286 insert into t2 values (19700201+16-1);
287 insert into t2 values (19700301+16-1);
288 insert into t2 values (19700101+15-1);
289 insert into t2 values (19700201+15-1);
290 insert into t2 values (19700301+15-1);
291 insert into t2 values (19700101+14-1);
292 insert into t2 values (19700201+14-1);
293 insert into t2 values (19700301+14-1);
294 insert into t2 values (19700101+13-1);
295 insert into t2 values (19700201+13-1);
296 insert into t2 values (19700301+13-1);
297 insert into t2 values (19700101+12-1);
298 insert into t2 values (19700201+12-1);
299 insert into t2 values (19700301+12-1);
300 insert into t2 values (19700101+11-1);
301 insert into t2 values (19700201+11-1);
302 insert into t2 values (19700301+11-1);
303 insert into t2 values (19700101+10-1);
304 insert into t2 values (19700201+10-1);
305 insert into t2 values (19700301+10-1);
306 insert into t2 values (19700101+9-1);
307 insert into t2 values (19700201+9-1);
308 insert into t2 values (19700301+9-1);
309 insert into t2 values (19700101+8-1);
310 insert into t2 values (19700201+8-1);
311 insert into t2 values (19700301+8-1);
312 insert into t2 values (19700101+7-1);
313 insert into t2 values (19700201+7-1);
314 insert into t2 values (19700301+7-1);
315 insert into t2 values (19700101+6-1);
316 insert into t2 values (19700201+6-1);
317 insert into t2 values (19700301+6-1);
318 insert into t2 values (19700101+5-1);
319 insert into t2 values (19700201+5-1);
320 insert into t2 values (19700301+5-1);
321 insert into t2 values (19700101+4-1);
322 insert into t2 values (19700201+4-1);
323 insert into t2 values (19700301+4-1);
324 insert into t2 values (19700101+3-1);
325 insert into t2 values (19700201+3-1);
326 insert into t2 values (19700301+3-1);
327 insert into t2 values (19700101+2-1);
328 insert into t2 values (19700201+2-1);
329 insert into t2 values (19700301+2-1);
330 insert into t2 values (19700101+1-1);
331 insert into t2 values (19700201+1-1);
332 insert into t2 values (19700301+1-1);
333 select count(*) from t2;
334 count(*)
335 84
336 select * from t2;
337 a
338 1970-01-04
339 1970-01-12
340 1970-01-14
341 1970-01-16
342 1970-01-20
343 1970-01-28
344 1970-02-02
345 1970-02-08
346 1970-02-26
347 1970-03-02
348 1970-03-08
349 1970-03-26
350 1970-01-05
351 1970-01-21
352 1970-01-25
353 1970-02-01
354 1970-02-15
355 1970-02-17
356 1970-02-19
357 1970-02-27
358 1970-03-01
359 1970-03-15
360 1970-03-17
361 1970-03-19
362 1970-03-27
363 1970-01-08
364 1970-01-18
365 1970-01-24
366 1970-02-10
367 1970-02-14
368 1970-02-16
369 1970-02-20
370 1970-02-22
371 1970-03-10
372 1970-03-14
373 1970-03-16
374 1970-03-20
375 1970-03-22
376 1970-03-28
377 1970-01-01
378 1970-01-09
379 1970-01-13
380 1970-01-19
381 1970-01-27
382 1970-02-09
383 1970-02-21
384 1970-02-25
385 1970-03-09
386 1970-03-21
387 1970-03-25
388 1970-01-02
389 1970-01-06
390 1970-01-10
391 1970-01-22
392 1970-01-26
393 1970-02-04
394 1970-02-06
395 1970-02-12
396 1970-02-18
397 1970-02-24
398 1970-02-28
399 1970-03-04
400 1970-03-06
401 1970-03-12
402 1970-03-18
403 1970-03-24
404 1970-01-03
405 1970-01-07
406 1970-01-11
407 1970-01-15
408 1970-01-17
409 1970-01-23
410 1970-02-03
411 1970-02-05
412 1970-02-07
413 1970-02-11
414 1970-02-13
415 1970-02-23
416 1970-03-03
417 1970-03-05
418 1970-03-07
419 1970-03-11
420 1970-03-13
421 1970-03-23
422 drop table t2;
423 create table t3 (a date not null, primary key(a)) engine='InnoDB'
424 partition by range (month(a)) subpartition by key (a)
425 subpartitions 3 (
426 partition quarter1 values less than (4),
427 partition quarter2 values less than (7),
428 partition quarter3 values less than (10),
429 partition quarter4 values less than (13)
430 );
431 show create table t3;
432 Table Create Table
433 t3 CREATE TABLE `t3` (
434 `a` date NOT NULL,
435 PRIMARY KEY (`a`)
436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
437 /*!50100 PARTITION BY RANGE (month(`a`))
438 SUBPARTITION BY KEY (a)
439 SUBPARTITIONS 3
440 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB,
441 PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB,
442 PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB,
443 PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */
444 12 inserts;
445 insert into t3 values (adddate(19700101,interval 12-1 month));
446 insert into t3 values (adddate(19700101,interval 11-1 month));
447 insert into t3 values (adddate(19700101,interval 10-1 month));
448 insert into t3 values (adddate(19700101,interval 9-1 month));
449 insert into t3 values (adddate(19700101,interval 8-1 month));
450 insert into t3 values (adddate(19700101,interval 7-1 month));
451 insert into t3 values (adddate(19700101,interval 6-1 month));
452 insert into t3 values (adddate(19700101,interval 5-1 month));
453 insert into t3 values (adddate(19700101,interval 4-1 month));
454 insert into t3 values (adddate(19700101,interval 3-1 month));
455 insert into t3 values (adddate(19700101,interval 2-1 month));
456 insert into t3 values (adddate(19700101,interval 1-1 month));
457 select count(*) from t3;
458 count(*)
459 12
460 select * from t3;
461 a
462 1970-01-01
463 1970-02-01
464 1970-03-01
465 1970-04-01
466 1970-05-01
467 1970-06-01
468 1970-07-01
469 1970-08-01
470 1970-09-01
471 1970-10-01
472 1970-11-01
473 1970-12-01
474 drop table t3;
475 create table t4 (a date not null, primary key(a)) engine='InnoDB'
476 partition by list (month(a)) subpartition by key (a)
477 subpartitions 3 (
478 partition quarter1 values in (1,2,3),
479 partition quarter2 values in (4,5,6),
480 partition quarter3 values in (7,8,9),
481 partition quarter4 values in (10,11,12)
482 );
483 show create table t4;
484 Table Create Table
485 t4 CREATE TABLE `t4` (
486 `a` date NOT NULL,
487 PRIMARY KEY (`a`)
488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
489 /*!50100 PARTITION BY LIST (month(`a`))
490 SUBPARTITION BY KEY (a)
491 SUBPARTITIONS 3
492 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB,
493 PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB,
494 PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB,
495 PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */
496 12 inserts;
497 insert into t4 values (adddate(19700101,interval 12-1 month));
498 insert into t4 values (adddate(19700101,interval 11-1 month));
499 insert into t4 values (adddate(19700101,interval 10-1 month));
500 insert into t4 values (adddate(19700101,interval 9-1 month));
501 insert into t4 values (adddate(19700101,interval 8-1 month));
502 insert into t4 values (adddate(19700101,interval 7-1 month));
503 insert into t4 values (adddate(19700101,interval 6-1 month));
504 insert into t4 values (adddate(19700101,interval 5-1 month));
505 insert into t4 values (adddate(19700101,interval 4-1 month));
506 insert into t4 values (adddate(19700101,interval 3-1 month));
507 insert into t4 values (adddate(19700101,interval 2-1 month));
508 insert into t4 values (adddate(19700101,interval 1-1 month));
509 select count(*) from t4;
510 count(*)
511 12
512 select * from t4;
513 a
514 1970-01-01
515 1970-02-01
516 1970-03-01
517 1970-04-01
518 1970-05-01
519 1970-06-01
520 1970-07-01
521 1970-08-01
522 1970-09-01
523 1970-10-01
524 1970-11-01
525 1970-12-01
526 drop table t4;
527 create table t1 (a time not null, primary key(a)) engine='InnoDB'
528 partition by key (a) (
529 partition pa1 max_rows=20 min_rows=2,
530 partition pa2 max_rows=30 min_rows=3,
531 partition pa3 max_rows=30 min_rows=4,
532 partition pa4 max_rows=40 min_rows=2);
533 show create table t1;
534 Table Create Table
535 t1 CREATE TABLE `t1` (
536 `a` time NOT NULL,
537 PRIMARY KEY (`a`)
538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
539 /*!50100 PARTITION BY KEY (a)
540 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
541 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
542 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
543 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
544 insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
545 select * from t1;
546 a
547 03:03:03
548 21:21:21
549 23:59:00
550 12:10:30
551 select * from t1 where a=030303;
552 a
553 03:03:03
554 delete from t1 where a=030303;
555 select * from t1;
556 a
557 21:21:21
558 23:59:00
559 12:10:30
560 drop table t1;
561 create table t2 (a time not null, primary key(a)) engine='InnoDB'
562 partition by key (a) partitions 12;
563 show create table t2;
564 Table Create Table
565 t2 CREATE TABLE `t2` (
566 `a` time NOT NULL,
567 PRIMARY KEY (`a`)
568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
569 /*!50100 PARTITION BY KEY (a)
570 PARTITIONS 12 */
571 insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
572 select * from t2;
573 a
574 10:11:12
575 13:14:15
576 14:15:16
577 00:01:01
578 select * from t2 where a='13:14:15';
579 a
580 13:14:15
581 delete from t2 where a='13:14:15';
582 select * from t2;
583 a
584 10:11:12
585 14:15:16
586 00:01:01
587 delete from t2;
588 59 inserts;
589 insert into t2 values (000100+59);
590 insert into t2 values (000100+58);
591 insert into t2 values (000100+57);
592 insert into t2 values (000100+56);
593 insert into t2 values (000100+55);
594 insert into t2 values (000100+54);
595 insert into t2 values (000100+53);
596 insert into t2 values (000100+52);
597 insert into t2 values (000100+51);
598 insert into t2 values (000100+50);
599 insert into t2 values (000100+49);
600 insert into t2 values (000100+48);
601 insert into t2 values (000100+47);
602 insert into t2 values (000100+46);
603 insert into t2 values (000100+45);
604 insert into t2 values (000100+44);
605 insert into t2 values (000100+43);
606 insert into t2 values (000100+42);
607 insert into t2 values (000100+41);
608 insert into t2 values (000100+40);
609 insert into t2 values (000100+39);
610 insert into t2 values (000100+38);
611 insert into t2 values (000100+37);
612 insert into t2 values (000100+36);
613 insert into t2 values (000100+35);
614 insert into t2 values (000100+34);
615 insert into t2 values (000100+33);
616 insert into t2 values (000100+32);
617 insert into t2 values (000100+31);
618 insert into t2 values (000100+30);
619 insert into t2 values (000100+29);
620 insert into t2 values (000100+28);
621 insert into t2 values (000100+27);
622 insert into t2 values (000100+26);
623 insert into t2 values (000100+25);
624 insert into t2 values (000100+24);
625 insert into t2 values (000100+23);
626 insert into t2 values (000100+22);
627 insert into t2 values (000100+21);
628 insert into t2 values (000100+20);
629 insert into t2 values (000100+19);
630 insert into t2 values (000100+18);
631 insert into t2 values (000100+17);
632 insert into t2 values (000100+16);
633 insert into t2 values (000100+15);
634 insert into t2 values (000100+14);
635 insert into t2 values (000100+13);
636 insert into t2 values (000100+12);
637 insert into t2 values (000100+11);
638 insert into t2 values (000100+10);
639 insert into t2 values (000100+9);
640 insert into t2 values (000100+8);
641 insert into t2 values (000100+7);
642 insert into t2 values (000100+6);
643 insert into t2 values (000100+5);
644 insert into t2 values (000100+4);
645 insert into t2 values (000100+3);
646 insert into t2 values (000100+2);
647 insert into t2 values (000100+1);
648 select count(*) from t2;
649 count(*)
650 59
651 select * from t2;
652 a
653 00:01:15
654 00:01:23
655 00:01:39
656 00:01:47
657 00:01:59
658 00:01:16
659 00:01:24
660 00:01:40
661 00:01:48
662 00:01:05
663 00:01:13
664 00:01:21
665 00:01:37
666 00:01:45
667 00:01:57
668 00:01:14
669 00:01:22
670 00:01:38
671 00:01:46
672 00:01:58
673 00:01:03
674 00:01:11
675 00:01:19
676 00:01:35
677 00:01:43
678 00:01:55
679 00:01:04
680 00:01:12
681 00:01:20
682 00:01:36
683 00:01:44
684 00:01:56
685 00:01:01
686 00:01:09
687 00:01:29
688 00:01:33
689 00:01:41
690 00:01:53
691 00:01:02
692 00:01:10
693 00:01:18
694 00:01:34
695 00:01:42
696 00:01:54
697 00:01:07
698 00:01:27
699 00:01:31
700 00:01:51
701 00:01:08
702 00:01:28
703 00:01:32
704 00:01:52
705 00:01:17
706 00:01:25
707 00:01:49
708 00:01:06
709 00:01:26
710 00:01:30
711 00:01:50
712 drop table t2;
713 create table t3 (a time not null, primary key(a)) engine='InnoDB'
714 partition by range (second(a)) subpartition by key (a)
715 subpartitions 3 (
716 partition quarter1 values less than (16),
717 partition quarter2 values less than (31),
718 partition quarter3 values less than (46),
719 partition quarter4 values less than (61)
720 );
721 show create table t3;
722 Table Create Table
723 t3 CREATE TABLE `t3` (
724 `a` time NOT NULL,
725 PRIMARY KEY (`a`)
726 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
727 /*!50100 PARTITION BY RANGE (second(`a`))
728 SUBPARTITION BY KEY (a)
729 SUBPARTITIONS 3
730 (PARTITION quarter1 VALUES LESS THAN (16) ENGINE = InnoDB,
731 PARTITION quarter2 VALUES LESS THAN (31) ENGINE = InnoDB,
732 PARTITION quarter3 VALUES LESS THAN (46) ENGINE = InnoDB,
733 PARTITION quarter4 VALUES LESS THAN (61) ENGINE = InnoDB) */
734 59 inserts;
735 insert into t3 values (100000+59);
736 insert into t3 values (100000+58);
737 insert into t3 values (100000+57);
738 insert into t3 values (100000+56);
739 insert into t3 values (100000+55);
740 insert into t3 values (100000+54);
741 insert into t3 values (100000+53);
742 insert into t3 values (100000+52);
743 insert into t3 values (100000+51);
744 insert into t3 values (100000+50);
745 insert into t3 values (100000+49);
746 insert into t3 values (100000+48);
747 insert into t3 values (100000+47);
748 insert into t3 values (100000+46);
749 insert into t3 values (100000+45);
750 insert into t3 values (100000+44);
751 insert into t3 values (100000+43);
752 insert into t3 values (100000+42);
753 insert into t3 values (100000+41);
754 insert into t3 values (100000+40);
755 insert into t3 values (100000+39);
756 insert into t3 values (100000+38);
757 insert into t3 values (100000+37);
758 insert into t3 values (100000+36);
759 insert into t3 values (100000+35);
760 insert into t3 values (100000+34);
761 insert into t3 values (100000+33);
762 insert into t3 values (100000+32);
763 insert into t3 values (100000+31);
764 insert into t3 values (100000+30);
765 insert into t3 values (100000+29);
766 insert into t3 values (100000+28);
767 insert into t3 values (100000+27);
768 insert into t3 values (100000+26);
769 insert into t3 values (100000+25);
770 insert into t3 values (100000+24);
771 insert into t3 values (100000+23);
772 insert into t3 values (100000+22);
773 insert into t3 values (100000+21);
774 insert into t3 values (100000+20);
775 insert into t3 values (100000+19);
776 insert into t3 values (100000+18);
777 insert into t3 values (100000+17);
778 insert into t3 values (100000+16);
779 insert into t3 values (100000+15);
780 insert into t3 values (100000+14);
781 insert into t3 values (100000+13);
782 insert into t3 values (100000+12);
783 insert into t3 values (100000+11);
784 insert into t3 values (100000+10);
785 insert into t3 values (100000+9);
786 insert into t3 values (100000+8);
787 insert into t3 values (100000+7);
788 insert into t3 values (100000+6);
789 insert into t3 values (100000+5);
790 insert into t3 values (100000+4);
791 insert into t3 values (100000+3);
792 insert into t3 values (100000+2);
793 insert into t3 values (100000+1);
794 select count(*) from t3;
795 count(*)
796 59
797 select * from t3;
798 a
799 10:00:01
800 10:00:06
801 10:00:07
802 10:00:14
803 10:00:15
804 10:00:02
805 10:00:03
806 10:00:08
807 10:00:09
808 10:00:04
809 10:00:05
810 10:00:10
811 10:00:11
812 10:00:12
813 10:00:13
814 10:00:20
815 10:00:21
816 10:00:26
817 10:00:27
818 10:00:16
819 10:00:17
820 10:00:22
821 10:00:23
822 10:00:28
823 10:00:29
824 10:00:18
825 10:00:19
826 10:00:24
827 10:00:25
828 10:00:30
829 10:00:32
830 10:00:33
831 10:00:35
832 10:00:40
833 10:00:41
834 10:00:34
835 10:00:36
836 10:00:37
837 10:00:42
838 10:00:43
839 10:00:31
840 10:00:38
841 10:00:39
842 10:00:44
843 10:00:45
844 10:00:46
845 10:00:48
846 10:00:49
847 10:00:54
848 10:00:55
849 10:00:50
850 10:00:51
851 10:00:56
852 10:00:57
853 10:00:59
854 10:00:47
855 10:00:52
856 10:00:53
857 10:00:58
858 drop table t3;
859 create table t4 (a time not null, primary key(a)) engine='InnoDB'
860 partition by list (second(a)) subpartition by key (a)
861 subpartitions 3 (
862 partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
863 partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
864 partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
865 partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
866 );
867 show create table t4;
868 Table Create Table
869 t4 CREATE TABLE `t4` (
870 `a` time NOT NULL,
871 PRIMARY KEY (`a`)
872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
873 /*!50100 PARTITION BY LIST (second(`a`))
874 SUBPARTITION BY KEY (a)
875 SUBPARTITIONS 3
876 (PARTITION quarter1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = InnoDB,
877 PARTITION quarter2 VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
878 PARTITION quarter3 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = InnoDB,
879 PARTITION quarter4 VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
880 59 inserts;
881 insert into t4 values (100000+59);
882 insert into t4 values (100000+58);
883 insert into t4 values (100000+57);
884 insert into t4 values (100000+56);
885 insert into t4 values (100000+55);
886 insert into t4 values (100000+54);
887 insert into t4 values (100000+53);
888 insert into t4 values (100000+52);
889 insert into t4 values (100000+51);
890 insert into t4 values (100000+50);
891 insert into t4 values (100000+49);
892 insert into t4 values (100000+48);
893 insert into t4 values (100000+47);
894 insert into t4 values (100000+46);
895 insert into t4 values (100000+45);
896 insert into t4 values (100000+44);
897 insert into t4 values (100000+43);
898 insert into t4 values (100000+42);
899 insert into t4 values (100000+41);
900 insert into t4 values (100000+40);
901 insert into t4 values (100000+39);
902 insert into t4 values (100000+38);
903 insert into t4 values (100000+37);
904 insert into t4 values (100000+36);
905 insert into t4 values (100000+35);
906 insert into t4 values (100000+34);
907 insert into t4 values (100000+33);
908 insert into t4 values (100000+32);
909 insert into t4 values (100000+31);
910 insert into t4 values (100000+30);
911 insert into t4 values (100000+29);
912 insert into t4 values (100000+28);
913 insert into t4 values (100000+27);
914 insert into t4 values (100000+26);
915 insert into t4 values (100000+25);
916 insert into t4 values (100000+24);
917 insert into t4 values (100000+23);
918 insert into t4 values (100000+22);
919 insert into t4 values (100000+21);
920 insert into t4 values (100000+20);
921 insert into t4 values (100000+19);
922 insert into t4 values (100000+18);
923 insert into t4 values (100000+17);
924 insert into t4 values (100000+16);
925 insert into t4 values (100000+15);
926 insert into t4 values (100000+14);
927 insert into t4 values (100000+13);
928 insert into t4 values (100000+12);
929 insert into t4 values (100000+11);
930 insert into t4 values (100000+10);
931 insert into t4 values (100000+9);
932 insert into t4 values (100000+8);
933 insert into t4 values (100000+7);
934 insert into t4 values (100000+6);
935 insert into t4 values (100000+5);
936 insert into t4 values (100000+4);
937 insert into t4 values (100000+3);
938 insert into t4 values (100000+2);
939 insert into t4 values (100000+1);
940 select count(*) from t4;
941 count(*)
942 59
943 select * from t4;
944 a
945 10:00:01
946 10:00:06
947 10:00:07
948 10:00:14
949 10:00:15
950 10:00:02
951 10:00:03
952 10:00:08
953 10:00:09
954 10:00:04
955 10:00:05
956 10:00:10
957 10:00:11
958 10:00:12
959 10:00:13
960 10:00:20
961 10:00:21
962 10:00:26
963 10:00:27
964 10:00:16
965 10:00:17
966 10:00:22
967 10:00:23
968 10:00:28
969 10:00:29
970 10:00:18
971 10:00:19
972 10:00:24
973 10:00:25
974 10:00:30
975 10:00:32
976 10:00:33
977 10:00:35
978 10:00:40
979 10:00:41
980 10:00:34
981 10:00:36
982 10:00:37
983 10:00:42
984 10:00:43
985 10:00:31
986 10:00:38
987 10:00:39
988 10:00:44
989 10:00:45
990 10:00:46
991 10:00:48
992 10:00:49
993 10:00:54
994 10:00:55
995 10:00:50
996 10:00:51
997 10:00:56
998 10:00:57
999 10:00:59
1000 10:00:47
1001 10:00:52
1002 10:00:53
1003 10:00:58
1004 drop table t4;
1005 create table t1 (a datetime not null, primary key(a)) engine='InnoDB'
1006 partition by key (a) (
1007 partition pa1 max_rows=20 min_rows=2,
1008 partition pa2 max_rows=30 min_rows=3,
1009 partition pa3 max_rows=30 min_rows=4,
1010 partition pa4 max_rows=40 min_rows=2);
1011 show create table t1;
1012 Table Create Table
1013 t1 CREATE TABLE `t1` (
1014 `a` datetime NOT NULL,
1015 PRIMARY KEY (`a`)
1016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1017 /*!50100 PARTITION BY KEY (a)
1018 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
1019 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
1020 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
1021 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
1022 insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
1023 select * from t1;
1024 a
1025 1975-01-01 21:21:21
1026 2000-06-15 23:59:00
1027 2020-12-31 12:10:30
1028 1980-10-14 03:03:00
1029 select * from t1 where a=19801014030300;
1030 a
1031 1980-10-14 03:03:00
1032 delete from t1 where a=19801014030300;
1033 select * from t1;
1034 a
1035 1975-01-01 21:21:21
1036 2000-06-15 23:59:00
1037 2020-12-31 12:10:30
1038 drop table t1;
1039 create table t2 (a datetime not null, primary key(a)) engine='InnoDB'
1040 partition by key (a) partitions 12;
1041 show create table t2;
1042 Table Create Table
1043 t2 CREATE TABLE `t2` (
1044 `a` datetime NOT NULL,
1045 PRIMARY KEY (`a`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1047 /*!50100 PARTITION BY KEY (a)
1048 PARTITIONS 12 */
1049 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
1050 select * from t2;
1051 a
1052 2020-12-31 10:11:12
1053 2000-06-15 14:15:16
1054 1980-10-14 13:14:15
1055 1975-01-01 00:01:01
1056 select * from t2 where a='1980-10-14 13:14:15';
1057 a
1058 1980-10-14 13:14:15
1059 delete from t2 where a='1980-10-14 13:14:15';
1060 select * from t2;
1061 a
1062 2020-12-31 10:11:12
1063 2000-06-15 14:15:16
1064 1975-01-01 00:01:01
1065 delete from t2;
1066 59 inserts;
1067 insert into t2 values (19700101000000+59);
1068 insert into t2 values (19700101000000+58);
1069 insert into t2 values (19700101000000+57);
1070 insert into t2 values (19700101000000+56);
1071 insert into t2 values (19700101000000+55);
1072 insert into t2 values (19700101000000+54);
1073 insert into t2 values (19700101000000+53);
1074 insert into t2 values (19700101000000+52);
1075 insert into t2 values (19700101000000+51);
1076 insert into t2 values (19700101000000+50);
1077 insert into t2 values (19700101000000+49);
1078 insert into t2 values (19700101000000+48);
1079 insert into t2 values (19700101000000+47);
1080 insert into t2 values (19700101000000+46);
1081 insert into t2 values (19700101000000+45);
1082 insert into t2 values (19700101000000+44);
1083 insert into t2 values (19700101000000+43);
1084 insert into t2 values (19700101000000+42);
1085 insert into t2 values (19700101000000+41);
1086 insert into t2 values (19700101000000+40);
1087 insert into t2 values (19700101000000+39);
1088 insert into t2 values (19700101000000+38);
1089 insert into t2 values (19700101000000+37);
1090 insert into t2 values (19700101000000+36);
1091 insert into t2 values (19700101000000+35);
1092 insert into t2 values (19700101000000+34);
1093 insert into t2 values (19700101000000+33);
1094 insert into t2 values (19700101000000+32);
1095 insert into t2 values (19700101000000+31);
1096 insert into t2 values (19700101000000+30);
1097 insert into t2 values (19700101000000+29);
1098 insert into t2 values (19700101000000+28);
1099 insert into t2 values (19700101000000+27);
1100 insert into t2 values (19700101000000+26);
1101 insert into t2 values (19700101000000+25);
1102 insert into t2 values (19700101000000+24);
1103 insert into t2 values (19700101000000+23);
1104 insert into t2 values (19700101000000+22);
1105 insert into t2 values (19700101000000+21);
1106 insert into t2 values (19700101000000+20);
1107 insert into t2 values (19700101000000+19);
1108 insert into t2 values (19700101000000+18);
1109 insert into t2 values (19700101000000+17);
1110 insert into t2 values (19700101000000+16);
1111 insert into t2 values (19700101000000+15);
1112 insert into t2 values (19700101000000+14);
1113 insert into t2 values (19700101000000+13);
1114 insert into t2 values (19700101000000+12);
1115 insert into t2 values (19700101000000+11);
1116 insert into t2 values (19700101000000+10);
1117 insert into t2 values (19700101000000+9);
1118 insert into t2 values (19700101000000+8);
1119 insert into t2 values (19700101000000+7);
1120 insert into t2 values (19700101000000+6);
1121 insert into t2 values (19700101000000+5);
1122 insert into t2 values (19700101000000+4);
1123 insert into t2 values (19700101000000+3);
1124 insert into t2 values (19700101000000+2);
1125 insert into t2 values (19700101000000+1);
1126 select count(*) from t2;
1127 count(*)
1128 59
1129 select * from t2;
1130 a
1131 1970-01-01 00:00:09
1132 1970-01-01 00:00:13
1133 1970-01-01 00:00:19
1134 1970-01-01 00:00:23
1135 1970-01-01 00:00:33
1136 1970-01-01 00:00:37
1137 1970-01-01 00:00:51
1138 1970-01-01 00:00:59
1139 1970-01-01 00:00:04
1140 1970-01-01 00:00:10
1141 1970-01-01 00:00:14
1142 1970-01-01 00:00:24
1143 1970-01-01 00:00:28
1144 1970-01-01 00:00:34
1145 1970-01-01 00:00:38
1146 1970-01-01 00:00:48
1147 1970-01-01 00:00:52
1148 1970-01-01 00:00:56
1149 1970-01-01 00:00:03
1150 1970-01-01 00:00:07
1151 1970-01-01 00:00:17
1152 1970-01-01 00:00:21
1153 1970-01-01 00:00:27
1154 1970-01-01 00:00:31
1155 1970-01-01 00:00:35
1156 1970-01-01 00:00:39
1157 1970-01-01 00:00:41
1158 1970-01-01 00:00:45
1159 1970-01-01 00:00:08
1160 1970-01-01 00:00:12
1161 1970-01-01 00:00:18
1162 1970-01-01 00:00:22
1163 1970-01-01 00:00:32
1164 1970-01-01 00:00:36
1165 1970-01-01 00:00:42
1166 1970-01-01 00:00:46
1167 1970-01-01 00:00:01
1168 1970-01-01 00:00:05
1169 1970-01-01 00:00:11
1170 1970-01-01 00:00:15
1171 1970-01-01 00:00:25
1172 1970-01-01 00:00:29
1173 1970-01-01 00:00:43
1174 1970-01-01 00:00:47
1175 1970-01-01 00:00:49
1176 1970-01-01 00:00:53
1177 1970-01-01 00:00:55
1178 1970-01-01 00:00:57
1179 1970-01-01 00:00:02
1180 1970-01-01 00:00:06
1181 1970-01-01 00:00:16
1182 1970-01-01 00:00:20
1183 1970-01-01 00:00:26
1184 1970-01-01 00:00:30
1185 1970-01-01 00:00:40
1186 1970-01-01 00:00:44
1187 1970-01-01 00:00:50
1188 1970-01-01 00:00:54
1189 1970-01-01 00:00:58
1190 drop table t2;
1191 create table t3 (a datetime not null, primary key(a)) engine='InnoDB'
1192 partition by range (month(a)) subpartition by key (a)
1193 subpartitions 3 (
1194 partition quarter1 values less than (4),
1195 partition quarter2 values less than (7),
1196 partition quarter3 values less than (10),
1197 partition quarter4 values less than (13)
1198 );
1199 show create table t3;
1200 Table Create Table
1201 t3 CREATE TABLE `t3` (
1202 `a` datetime NOT NULL,
1203 PRIMARY KEY (`a`)
1204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1205 /*!50100 PARTITION BY RANGE (month(`a`))
1206 SUBPARTITION BY KEY (a)
1207 SUBPARTITIONS 3
1208 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB,
1209 PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB,
1210 PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB,
1211 PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */
1212 12 inserts;
1213 insert into t3 values (adddate(19700101000000,interval 12-1 month));
1214 insert into t3 values (adddate(19700101000000,interval 11-1 month));
1215 insert into t3 values (adddate(19700101000000,interval 10-1 month));
1216 insert into t3 values (adddate(19700101000000,interval 9-1 month));
1217 insert into t3 values (adddate(19700101000000,interval 8-1 month));
1218 insert into t3 values (adddate(19700101000000,interval 7-1 month));
1219 insert into t3 values (adddate(19700101000000,interval 6-1 month));
1220 insert into t3 values (adddate(19700101000000,interval 5-1 month));
1221 insert into t3 values (adddate(19700101000000,interval 4-1 month));
1222 insert into t3 values (adddate(19700101000000,interval 3-1 month));
1223 insert into t3 values (adddate(19700101000000,interval 2-1 month));
1224 insert into t3 values (adddate(19700101000000,interval 1-1 month));
1225 select count(*) from t3;
1226 count(*)
1227 12
1228 select * from t3;
1229 a
1230 1970-02-01 00:00:00
1231 1970-01-01 00:00:00
1232 1970-03-01 00:00:00
1233 1970-04-01 00:00:00
1234 1970-05-01 00:00:00
1235 1970-06-01 00:00:00
1236 1970-08-01 00:00:00
1237 1970-09-01 00:00:00
1238 1970-07-01 00:00:00
1239 1970-10-01 00:00:00
1240 1970-11-01 00:00:00
1241 1970-12-01 00:00:00
1242 drop table t3;
1243 create table t4 (a datetime not null, primary key(a)) engine='InnoDB'
1244 partition by list (month(a)) subpartition by key (a)
1245 subpartitions 3 (
1246 partition quarter1 values in (1,2,3),
1247 partition quarter2 values in (4,5,6),
1248 partition quarter3 values in (7,8,9),
1249 partition quarter4 values in (10,11,12)
1250 );
1251 show create table t4;
1252 Table Create Table
1253 t4 CREATE TABLE `t4` (
1254 `a` datetime NOT NULL,
1255 PRIMARY KEY (`a`)
1256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1257 /*!50100 PARTITION BY LIST (month(`a`))
1258 SUBPARTITION BY KEY (a)
1259 SUBPARTITIONS 3
1260 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB,
1261 PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB,
1262 PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB,
1263 PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */
1264 12 inserts;
1265 insert into t4 values (adddate(19700101000000,interval 12-1 month));
1266 insert into t4 values (adddate(19700101000000,interval 11-1 month));
1267 insert into t4 values (adddate(19700101000000,interval 10-1 month));
1268 insert into t4 values (adddate(19700101000000,interval 9-1 month));
1269 insert into t4 values (adddate(19700101000000,interval 8-1 month));
1270 insert into t4 values (adddate(19700101000000,interval 7-1 month));
1271 insert into t4 values (adddate(19700101000000,interval 6-1 month));
1272 insert into t4 values (adddate(19700101000000,interval 5-1 month));
1273 insert into t4 values (adddate(19700101000000,interval 4-1 month));
1274 insert into t4 values (adddate(19700101000000,interval 3-1 month));
1275 insert into t4 values (adddate(19700101000000,interval 2-1 month));
1276 insert into t4 values (adddate(19700101000000,interval 1-1 month));
1277 select count(*) from t4;
1278 count(*)
1279 12
1280 select * from t4;
1281 a
1282 1970-02-01 00:00:00
1283 1970-01-01 00:00:00
1284 1970-03-01 00:00:00
1285 1970-04-01 00:00:00
1286 1970-05-01 00:00:00
1287 1970-06-01 00:00:00
1288 1970-08-01 00:00:00
1289 1970-09-01 00:00:00
1290 1970-07-01 00:00:00
1291 1970-10-01 00:00:00
1292 1970-11-01 00:00:00
1293 1970-12-01 00:00:00
1294 drop table t4;
1295 create table t1 (a year not null, primary key(a)) engine='InnoDB'
1296 partition by key (a) (
1297 partition pa1 max_rows=20 min_rows=2,
1298 partition pa2 max_rows=30 min_rows=3,
1299 partition pa3 max_rows=30 min_rows=4,
1300 partition pa4 max_rows=40 min_rows=2);
1301 show create table t1;
1302 Table Create Table
1303 t1 CREATE TABLE `t1` (
1304 `a` year NOT NULL,
1305 PRIMARY KEY (`a`)
1306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1307 /*!50100 PARTITION BY KEY (a)
1308 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
1309 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
1310 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
1311 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
1312 insert into t1 values ('1975'), (2020), ('1980'), ('2000');
1313 select * from t1;
1314 a
1315 1980
1316 2000
1317 2020
1318 1975
1319 select * from t1 where a=1980;
1320 a
1321 1980
1322 delete from t1 where a=1980;
1323 select * from t1;
1324 a
1325 2000
1326 2020
1327 1975
1328 drop table t1;
1329 create table t2 (a year not null, primary key(a)) engine='InnoDB'
1330 partition by key (a) partitions 12;
1331 show create table t2;
1332 Table Create Table
1333 t2 CREATE TABLE `t2` (
1334 `a` year NOT NULL,
1335 PRIMARY KEY (`a`)
1336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1337 /*!50100 PARTITION BY KEY (a)
1338 PARTITIONS 12 */
1339 insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
1340 select * from t2;
1341 a
1342 2020
1343 1975
1344 1980
1345 2000
1346 select * from t2 where a='1980';
1347 a
1348 1980
1349 delete from t2 where a='1980';
1350 select * from t2;
1351 a
1352 2020
1353 1975
1354 2000
1355 delete from t2;
1356 255 inserts;
1357 Warnings:
1358 Warning 1264 Out of range value for column 'a' at row 1
1359 select count(*) from t2;
1360 count(*)
1361 255
1362 select * from t2;
1363 a
1364 1909
1365 1921
1366 1933
1367 1945
1368 1957
1369 1969
1370 1981
1371 1993
1372 2001
1373 2013
1374 2029
1375 2041
1376 2053
1377 2065
1378 2077
1379 2089
1380 2100
1381 2101
1382 2102
1383 2103
1384 2114
1385 2132
1386 2133
1387 2134
1388 2135
1389 2146
1390 1904
1391 1916
1392 1928
1393 1932
1394 1940
1395 1952
1396 1964
1397 1976
1398 1988
1399 2008
1400 2020
1401 2024
1402 2036
1403 2048
1404 2060
1405 2072
1406 2084
1407 1907
1408 1919
1409 1931
1410 1943
1411 1955
1412 1967
1413 1979
1414 1991
1415 1999
1416 2011
1417 2027
1418 2039
1419 2051
1420 2063
1421 2075
1422 2087
1423 2097
1424 2099
1425 2110
1426 2111
1427 2112
1428 2113
1429 2123
1430 2129
1431 2131
1432 2142
1433 2143
1434 2144
1435 2145
1436 2147
1437 1902
1438 1914
1439 1926
1440 1938
1441 1950
1442 1962
1443 1974
1444 1986
1445 2006
1446 2018
1447 2034
1448 2046
1449 2058
1450 2070
1451 2082
1452 1905
1453 1917
1454 1929
1455 1941
1456 1953
1457 1965
1458 1977
1459 1989
1460 1997
1461 2009
1462 2021
1463 2025
1464 2037
1465 2049
1466 2061
1467 2073
1468 2085
1469 2092
1470 2093
1471 2094
1472 2095
1473 2120
1474 2121
1475 2124
1476 2125
1477 2126
1478 2127
1479 2152
1480 2153
1481 0000
1482 1912
1483 1924
1484 1936
1485 1948
1486 1960
1487 1972
1488 1984
1489 1996
1490 2004
1491 2016
1492 2032
1493 2044
1494 2056
1495 2068
1496 2080
1497 2116
1498 2148
1499 1903
1500 1915
1501 1927
1502 1939
1503 1951
1504 1963
1505 1975
1506 1987
1507 2007
1508 2019
1509 2023
1510 2035
1511 2047
1512 2059
1513 2071
1514 2083
1515 2155
1516 1910
1517 1922
1518 1934
1519 1946
1520 1958
1521 1970
1522 1982
1523 1994
1524 2002
1525 2014
1526 2030
1527 2042
1528 2054
1529 2066
1530 2078
1531 2090
1532 1913
1533 1925
1534 1937
1535 1949
1536 1961
1537 1973
1538 1985
1539 2005
1540 2017
1541 2033
1542 2045
1543 2057
1544 2069
1545 2081
1546 2104
1547 2105
1548 2106
1549 2107
1550 2117
1551 2118
1552 2119
1553 2136
1554 2137
1555 2138
1556 2139
1557 2149
1558 2150
1559 2151
1560 1908
1561 1920
1562 1944
1563 1956
1564 1968
1565 1980
1566 1992
1567 2000
1568 2012
1569 2028
1570 2040
1571 2052
1572 2064
1573 2076
1574 2088
1575 2096
1576 2098
1577 2108
1578 2128
1579 2130
1580 2140
1581 1911
1582 1923
1583 1935
1584 1947
1585 1959
1586 1971
1587 1983
1588 1995
1589 2003
1590 2015
1591 2031
1592 2043
1593 2055
1594 2067
1595 2079
1596 2091
1597 2115
1598 1906
1599 1918
1600 1930
1601 1942
1602 1954
1603 1966
1604 1978
1605 1990
1606 1998
1607 2010
1608 2022
1609 2026
1610 2038
1611 2050
1612 2062
1613 2074
1614 2086
1615 2109
1616 2122
1617 2141
1618 2154
1619 drop table t2;