1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
--- partition.result
+++ partition,heap.reject
@@ -1545,82 +1545,3 @@
(PARTITION `p0` HISTORY ENGINE = X,
PARTITION `pn` CURRENT ENGINE = X)
drop tables t1, tp1;
-# Complex table
-create or replace table t1 (
-x int primary key auto_increment,
-t timestamp(6) default '2001-11-11 11:11:11',
-b blob(4096) compressed null,
-c varchar(1033) character set utf8 not null,
-u int,
-unique key (x, u),
-m enum('a', 'b', 'c') not null default 'a' comment 'absolute',
-i1 tinyint, i2 smallint, i3 bigint,
-index three(i1, i2, i3),
-v1 timestamp(6) generated always as (t + interval 1 day),
-v2 timestamp(6) generated always as (t + interval 1 month) stored,
-s timestamp(6) as row start,
-e timestamp(6) as row end,
-period for system_time (s, e),
-ps date, pe date,
-period for app_time (ps, pe),
-constraint check_constr check (u > -1))
-with system versioning default charset=ucs2
-partition by range(x) (
-partition p0 values less than (10),
-partition p1 values less than (20),
-partition pn values less than maxvalue);
-alter table t1 convert partition p1 to table tp1;
-show create table tp1;
-Table Create Table
-tp1 CREATE TABLE `tp1` (
- `x` int(11) NOT NULL AUTO_INCREMENT,
- `t` timestamp(6) NOT NULL DEFAULT '2001-11-11 11:11:11.000000',
- `b` blob /*!100301 COMPRESSED*/ DEFAULT NULL,
- `c` varchar(1033) CHARACTER SET utf8mb3 NOT NULL,
- `u` int(11) DEFAULT NULL,
- `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
- `i1` tinyint(4) DEFAULT NULL,
- `i2` smallint(6) DEFAULT NULL,
- `i3` bigint(20) DEFAULT NULL,
- `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
- `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
- `s` timestamp(6) GENERATED ALWAYS AS ROW START,
- `e` timestamp(6) GENERATED ALWAYS AS ROW END,
- `ps` date NOT NULL,
- `pe` date NOT NULL,
- PERIOD FOR `app_time` (`ps`, `pe`),
- PRIMARY KEY (`x`,`e`),
- UNIQUE KEY `x` (`x`,`u`,`e`),
- KEY `three` (`i1`,`i2`,`i3`),
- PERIOD FOR SYSTEM_TIME (`s`, `e`),
- CONSTRAINT `check_constr` CHECK (`u` > -1)
-) ENGINE=X DEFAULT CHARSET=ucs2 WITH SYSTEM VERSIONING
-show create table t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `x` int(11) NOT NULL AUTO_INCREMENT,
- `t` timestamp(6) NOT NULL DEFAULT '2001-11-11 11:11:11.000000',
- `b` blob /*!100301 COMPRESSED*/ DEFAULT NULL,
- `c` varchar(1033) CHARACTER SET utf8mb3 NOT NULL,
- `u` int(11) DEFAULT NULL,
- `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
- `i1` tinyint(4) DEFAULT NULL,
- `i2` smallint(6) DEFAULT NULL,
- `i3` bigint(20) DEFAULT NULL,
- `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
- `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
- `s` timestamp(6) GENERATED ALWAYS AS ROW START,
- `e` timestamp(6) GENERATED ALWAYS AS ROW END,
- `ps` date NOT NULL,
- `pe` date NOT NULL,
- PERIOD FOR `app_time` (`ps`, `pe`),
- PRIMARY KEY (`x`,`e`),
- UNIQUE KEY `x` (`x`,`u`,`e`),
- KEY `three` (`i1`,`i2`,`i3`),
- PERIOD FOR SYSTEM_TIME (`s`, `e`),
- CONSTRAINT `check_constr` CHECK (`u` > -1)
-) ENGINE=X DEFAULT CHARSET=ucs2 WITH SYSTEM VERSIONING
- PARTITION BY RANGE (`x`)
-(PARTITION `p0` VALUES LESS THAN (10) ENGINE = X,
- PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
-drop tables t1, tp1;
|
