| 1 |
set default_storage_engine=innodb; |
| 2 |
create or replace table dept ( |
| 3 |
dept_id int(10) primary key, |
| 4 |
name varchar(100) |
| 5 |
) |
| 6 |
with system versioning; |
| 7 |
create or replace table emp ( |
| 8 |
emp_id int(10) primary key, |
| 9 |
dept_id int(10), |
| 10 |
name varchar(100), |
| 11 |
salary int(10), |
| 12 |
constraint `dept-emp-fk` |
| 13 |
foreign key (dept_id) references dept (dept_id) |
| 14 |
on delete restrict |
| 15 |
on update restrict |
| 16 |
) |
| 17 |
with system versioning; |
| 18 |
select now() into @ts_0; |
| 19 |
insert into dept (dept_id, name) values (10, "accounting"); |
| 20 |
commit; |
| 21 |
select row_start into @ts_1 from dept where dept_id=10; |
| 22 |
insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10); |
| 23 |
commit; |
| 24 |
select row_start into @ts_2 from emp where name="bill"; |
| 25 |
select * from emp; |
| 26 |
emp_id dept_id name salary |
| 27 |
1 10 bill 1000 |
| 28 |
update emp set salary=2000 where name="bill"; |
| 29 |
commit; |
| 30 |
select row_start into @ts_3 from emp where name="bill"; |
| 31 |
select * from emp; |
| 32 |
emp_id dept_id name salary |
| 33 |
1 10 bill 2000 |
| 34 |
select * from emp for system_time as of timestamp @ts_2; |
| 35 |
emp_id dept_id name salary |
| 36 |
1 10 bill 1000 |
| 37 |
select * from emp for system_time as of timestamp @ts_3; |
| 38 |
emp_id dept_id name salary |
| 39 |
1 10 bill 2000 |
| 40 |
select * from emp e, dept d |
| 41 |
where d.dept_id = 10 |
| 42 |
and d.dept_id = e.dept_id; |
| 43 |
emp_id dept_id name salary dept_id name |
| 44 |
1 10 bill 2000 10 accounting |
| 45 |
select * from |
| 46 |
emp for system_time from timestamp @ts_1 to timestamp @ts_2 e, |
| 47 |
dept for system_time from timestamp @ts_1 to timestamp @ts_2 d |
| 48 |
where d.dept_id = 10 |
| 49 |
and d.dept_id = e.dept_id; |
| 50 |
emp_id dept_id name salary dept_id name |
| 51 |
set statement system_versioning_asof=@ts_0 for |
| 52 |
select * from emp e, dept d |
| 53 |
where d.dept_id = 10 |
| 54 |
and d.dept_id = e.dept_id; |
| 55 |
emp_id dept_id name salary dept_id name |
| 56 |
set statement system_versioning_asof=@ts_1 for |
| 57 |
select * from emp e, dept d |
| 58 |
where d.dept_id = 10 |
| 59 |
and d.dept_id = e.dept_id; |
| 60 |
emp_id dept_id name salary dept_id name |
| 61 |
set statement system_versioning_asof=@ts_2 for |
| 62 |
select * from emp e, dept d |
| 63 |
where d.dept_id = 10 |
| 64 |
and d.dept_id = e.dept_id; |
| 65 |
emp_id dept_id name salary dept_id name |
| 66 |
1 10 bill 1000 10 accounting |
| 67 |
set statement system_versioning_asof=@ts_3 for |
| 68 |
select * from emp e, dept d |
| 69 |
where d.dept_id = 10 |
| 70 |
and d.dept_id = e.dept_id; |
| 71 |
emp_id dept_id name salary dept_id name |
| 72 |
1 10 bill 2000 10 accounting |
| 73 |
drop table emp, dept; |
| 74 |
create table t1 ( |
| 75 |
a timestamp(6), |
| 76 |
b timestamp(6) generated always as (a + interval 1 day), |
| 77 |
c timestamp(6) generated always as (a + interval 1 month) stored, |
| 78 |
d timestamp(6) generated always as row start, |
| 79 |
e timestamp(6) generated always as row end, |
| 80 |
period for system_time(d,e) |
| 81 |
) with system versioning; |
| 82 |
show columns from t1; |
| 83 |
Field Type Null Key Default Extra |
| 84 |
a timestamp(6) YES NULL |
| 85 |
b timestamp(6) YES NULL VIRTUAL GENERATED |
| 86 |
c timestamp(6) YES NULL STORED GENERATED |
| 87 |
d timestamp(6) NO NULL STORED GENERATED |
| 88 |
e timestamp(6) NO NULL STORED GENERATED |
| 89 |
select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,column_comment,is_generated,generation_expression,'---' from information_schema.columns where table_name='t1'; |
| 90 |
table_catalog def |
| 91 |
table_schema test |
| 92 |
table_name t1 |
| 93 |
column_name a |
| 94 |
ordinal_position 1 |
| 95 |
column_default NULL |
| 96 |
is_nullable YES |
| 97 |
data_type timestamp |
| 98 |
character_maximum_length NULL |
| 99 |
character_octet_length NULL |
| 100 |
numeric_precision NULL |
| 101 |
numeric_scale NULL |
| 102 |
datetime_precision 6 |
| 103 |
character_set_name NULL |
| 104 |
collation_name NULL |
| 105 |
column_type timestamp(6) |
| 106 |
column_key |
| 107 |
extra |
| 108 |
column_comment |
| 109 |
is_generated NEVER |
| 110 |
generation_expression NULL |
| 111 |
--- --- |
| 112 |
table_catalog def |
| 113 |
table_schema test |
| 114 |
table_name t1 |
| 115 |
column_name b |
| 116 |
ordinal_position 2 |
| 117 |
column_default NULL |
| 118 |
is_nullable YES |
| 119 |
data_type timestamp |
| 120 |
character_maximum_length NULL |
| 121 |
character_octet_length NULL |
| 122 |
numeric_precision NULL |
| 123 |
numeric_scale NULL |
| 124 |
datetime_precision 6 |
| 125 |
character_set_name NULL |
| 126 |
collation_name NULL |
| 127 |
column_type timestamp(6) |
| 128 |
column_key |
| 129 |
extra VIRTUAL GENERATED |
| 130 |
column_comment |
| 131 |
is_generated ALWAYS |
| 132 |
generation_expression `a` + interval 1 day |
| 133 |
--- --- |
| 134 |
table_catalog def |
| 135 |
table_schema test |
| 136 |
table_name t1 |
| 137 |
column_name c |
| 138 |
ordinal_position 3 |
| 139 |
column_default NULL |
| 140 |
is_nullable YES |
| 141 |
data_type timestamp |
| 142 |
character_maximum_length NULL |
| 143 |
character_octet_length NULL |
| 144 |
numeric_precision NULL |
| 145 |
numeric_scale NULL |
| 146 |
datetime_precision 6 |
| 147 |
character_set_name NULL |
| 148 |
collation_name NULL |
| 149 |
column_type timestamp(6) |
| 150 |
column_key |
| 151 |
extra STORED GENERATED |
| 152 |
column_comment |
| 153 |
is_generated ALWAYS |
| 154 |
generation_expression `a` + interval 1 month |
| 155 |
--- --- |
| 156 |
table_catalog def |
| 157 |
table_schema test |
| 158 |
table_name t1 |
| 159 |
column_name d |
| 160 |
ordinal_position 4 |
| 161 |
column_default NULL |
| 162 |
is_nullable NO |
| 163 |
data_type timestamp |
| 164 |
character_maximum_length NULL |
| 165 |
character_octet_length NULL |
| 166 |
numeric_precision NULL |
| 167 |
numeric_scale NULL |
| 168 |
datetime_precision 6 |
| 169 |
character_set_name NULL |
| 170 |
collation_name NULL |
| 171 |
column_type timestamp(6) |
| 172 |
column_key |
| 173 |
extra STORED GENERATED |
| 174 |
column_comment |
| 175 |
is_generated ALWAYS |
| 176 |
generation_expression ROW START |
| 177 |
--- --- |
| 178 |
table_catalog def |
| 179 |
table_schema test |
| 180 |
table_name t1 |
| 181 |
column_name e |
| 182 |
ordinal_position 5 |
| 183 |
column_default NULL |
| 184 |
is_nullable NO |
| 185 |
data_type timestamp |
| 186 |
character_maximum_length NULL |
| 187 |
character_octet_length NULL |
| 188 |
numeric_precision NULL |
| 189 |
numeric_scale NULL |
| 190 |
datetime_precision 6 |
| 191 |
character_set_name NULL |
| 192 |
collation_name NULL |
| 193 |
column_type timestamp(6) |
| 194 |
column_key |
| 195 |
extra STORED GENERATED |
| 196 |
column_comment |
| 197 |
is_generated ALWAYS |
| 198 |
generation_expression ROW END |
| 199 |
--- --- |
| 200 |
drop table t1; |