MariaDB system versioning Cheat Sheet by fenistil

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 '2018-­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 '2018-­04-13 00:00:00'

TO '2018-­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.