Show Menu
Cheatography

MariaDB system versioning Cheat Sheet by

How to use system versioned tables in MariaDB 10.3+

Creating table

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

Creating table - partit­ioned

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
);
This partition the table based on the current and historical data. This can boost perfor­mance a lot.

Creating table - Skip field from versioning

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
);
When you update only the CharField field the server won't save a new version of this row.
 

Select data

SELECT * FROM Test
FOR SYSTEM­_TIME ALL;
This will select all rows with all saved versions.

Select data with rows lifespan

SELECT *, ROW_START, ROW_END FROM Test
FOR SYSTEM­_TIME ALL;
The ROW_START and ROW_END fields are invisible columns. These are repres­enting the time when the row has that data.

Select data at a time

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';
 

Adding or removing system versioning

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!

Warnings

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.
   
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts

          More Cheat Sheets by fenistil

          Go fmt formattings Cheat Sheet