System-Versioned Tables

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.3.4

System versioned tables support was added in MariaDB 10.3.4, the description below applies only to MariaDB 10.3.4 and later

System versioned tables, also known as Temporal, is a feature first introduced in SQL:2011 standard. System versioned tables contain data which was added at any point in time, not only the data which is valid at the current moment in time.

Using this built-in feature tables can be created to store a full history of changes. This allows data analysis for any point in time, auditing of changes and comparison of data from different points in time.

Typical uses cases are

  • Forensic discovery & legal requirements to store data for N years
  • Data analysis (retrospective, trends etc.), e.g. to get your staff information as of one year ago
  • Point-in-time recovery - recover a table state as of particular point in time

How to create a System versioned table

The create table syntax has been enhanced to create a System versioned table. A system versioned table needs to track the time when data was created, changed or deleted. It therefore needs to have fields of type timestamp(6) to store the timestamp of when data becomes valid and invalid.

MariaDB Server will create the required fields, when they have not been defined in the CREATE TABLE statement.

CREATE TABLE t(
  x INT
) WITH SYSTEM VERSIONING;

is creating table t with system versioning enabled, and will add two system invisible fields row_start and row_end to it.

DESC  t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

The full create statement including the required fields and syntax would be

CREATE TABLE t(
   x INT,
   row_start TIMESTAMP(6) AS ROW START INVISIBLE,
   row_end  TIMESTAMP(6) AS ROW END INVISIBLE,
   PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING;

PERIOD FOR SYSTEM_TIME(row_start, row_end) is needed to register the two created fields row_start and row_end as to be used for system versioning, AS ROW START INVISIBLE defines that the field is invisible and the timestamp will be set when data is created. The equivalent AS ROW END INVISIBLE is setting the timestamp to when data becomes invalid. One difference of creating the table this way is, that the fields row_start and row_end are user invisible instead of system invisible, see

DESC t;
+-----------+--------------+------+-----+---------+-----------+
| Field     | Type         | Null | Key | Default | Extra     |
+-----------+--------------+------+-----+---------+-----------+
| x         | int(11)      | YES  |     | NULL    |           |
| row_start | timestamp(6) | NO   |     | NULL    | INVISIBLE |
| row_end   | timestamp(6) | NO   |     | NULL    | INVISIBLE |
+-----------+--------------+------+-----+---------+-----------+

How to add/remove System versioning to/from a table

An existing table can be altered to enable system versioning for it

CREATE TABLE t(
  x INT
);
ALTER TABLE t ADD SYSTEM VERSIONING;
SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.