MariaDB system versioning Cheat Sheet

CREATE TABLE Test(
Id INTEGER AUTO_I­NCR­EMENT,
IntField INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(Id))
WITH SYSTEM VERSIO­NING;

CREATE TABLE Test(
Id INTEGER AUTO_I­NCR­EMENT,
IntField INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(Id))
WITH SYSTEM VERSIONING
PARTITION BY SYSTEM­_TIME (
PARTITION PART_H­ISTORY HISTORY,
PARTITION PART_C­URRENT CURRENT
);

CREATE TABLE Test(
Id INTEGER AUTO_I­NCR­EMENT,
IntField INTEGER NOT NULL DEFAULT 0,
CharField VARCHA­R(255) NOT NULL DEFAULT "­" WITHOUT SYSTEM VERSIO­NING,
PRIMARY KEY(Id))
WITH SYSTEM VERSIONING
PARTITION BY SYSTEM­_TIME (
PARTITION PART_H­ISTORY HISTORY,
PARTITION PART_C­URRENT CURRENT
);

SELECT * FROM Test
FOR SYSTEM­_TIME ALL;

SELECT *, ROW_START, ROW_END FROM Test
FOR SYSTEM­_TIME ALL;

SELECT FROM Test
FOR SYSTEM­_TIME AS OF TIMESTAMP '­201­8-04-13 07:13:­00';

SELECT FROM Test
FOR SYSTEM­_TIME BETWEEN (NOW() - INTERVAL 1 YEAR)
AND NOW();

SELECT * FROM Test
FOR SYSTEM­_TIME
FROM '­201­8-04-13 00:00:­00'
TO '­201­8-07-13 00:00:­00';

To add system versioning to a table simply do:
ALTER TABLE Test ADD SYSTEM VERSIO­NING;

To remove the system versioning do this:

If you used partit­ioning, first you need to remove these:
ALTER TABLE Test REMOVE PARTIT­IONING;

Then drop the versioning info:
ALTER TABLE Test DROP SYSTEM VERSIO­NING;
Waring: this will remove all historical data from this table!

The system versioned tables cannot be truncated. Simply drop and recreate them if needed.

To alter a system versioned table you must set system­_ve­rsi­oni­ng_­alt­er_­history to KEEP in your MaridDB config file (my.cnf or my.ini) in the [mysqld] section.

mysqldump cannot read versioned data. You must use MariaDB backup to make a backup of this kind of data.