System-Versioned Tables
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
In the same we can remove System versioning from a table.
ALTER TABLE t DROP 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