MySQL Manual | 3.2 CREATE TABLE ... AS TRANSACTION Syntax

Table of Contents

3.1 `CREATE TABLE ... AS TRANSACTION` Syntax

CREATE TABLE [IF NOT EXISTS] tbl_name AS TRANSACTION [(create_definition,...)]
[table_options] [select_statement]

table_options:
    TYPE = BDB

CREATE TABLE ... AS TRANSACTION creates a temporal table with the given name in the current database. This command works similarly to the original CREATE TABLE. The only difference in syntax is the presence of the AS TRANSACTION after the tbl_name. There is only one choice for the type of table: the table which is to support Transaction-Time has to be of type BerkeleyDB.

Support for transaction-time includes temporal upward compatibility (TUC). This means that all the functions that work with a table of type BerkeleyDB will work exactly the same with tables created with AS TRANSACTION. However, the power of having transaction-time support in the table comes from temporal queries that can be made on it. The command NONSEQUENCED TRANSACTION SELECT ... displays the relevant rows as well as the StartTime and StopTime times for each row. This will become clear in the following example.

Suppose we create a row in a temporal database on June 3, 2002 at 10:00 am:

CREATE TABLE ttdb1 AS TRANSACTION (a integer, b integer) TYPE=BDB;
INSERT INTO ttdb1 VALUES (1, 2);

a	b	StartTime	StopTime
1	2	6-3-2002, 10 am	untilchanged

The start time of the row will become 10:00 am and the stop time would be "until changed" (since the row has not yet deleted). Now, if we modify the table, all the old rows are retained. We execute this command at 11:00 am:

UPDATE TABLE ttdb1 SET b=100 where a=1;

The internal representation of the data in the temporal table would look something like this:

a	b	StartTime	StopTime
1	2	6-3-2002, 10 am	6-3-2002, 11 am
1	100	6-3-2002, 11 am	untilchanged

Now, when we query on the database after 11 am, the second row is returned (without the start and stop time) because the first row is invalid at that time. All the pre-written programs and procedures would work as before. Now, if an authorized person uses a temporal query, he/she can retrieve all the past information about the various states of the table: the rows and when were they modified.

Using transaction-time support in the database increases the space needed for the database files corresponding to the temporal tables. When dealing with non-temporal tables, the underlying methods in MySQL (and BerkeleyDB) will have a similar running time as the original functions. Non-temporal queries on temporal tables will exhibit a performance overhead.

Developed by the TAU Project, Computer Science Department, University of Arizona

3.1 CREATE TABLE ... AS TRANSACTION Syntax

3.1 `CREATE TABLE ... AS TRANSACTION` Syntax