Example of Versioning of tables in MySQL or MariaDB

System-versioned tables store all the changes in the data. It implements table versioning functionality for later recovery, analysis, or auditing.

Create a table with System Versioned by creating and altering syntax:

CREATE TABLE table_name (
   x varchar(100)
) WITH SYSTEM VERSIONING;

OR 
-- Enable on already created table:
ALTER TABLE table_name ADD SYSTEM VERSIONING;

Add or drop the system version from the table:

-- Enable system versioning for the table:
ALTER TABLE t ADD SYSTEM VERSIONING;

-- Disable system versioning for the table 
ALTER TABLE t DROP SYSTEM VERSIONING;

Check the system versioning data from the table:

You can also recover the table at a point in time recovery with system versioning.

--Check table as on timestamp 
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2023-05-15 08:07:06';

--Check data in range
SELECT * FROM t FOR SYSTEM_TIME FROM '2023-01-01 00:00:00' TO '2023-05-01 00:00:00';

--Check all data including deleted or modified data:
SELECT * FROM t FOR SYSTEM_TIME ALL;

Delete the history maintained with the system versioning feature:

DELETE HISTORY FROM t;
DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2016-10-09 08:07:06';

Maintain the different partitions for version data:

CREATE TABLE test (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION part_old HISTORY,
    PARTITION part_cur CURRENT
  );

Example of using System Versioning in a table maintained:

ROW_START and ROW_END columns are used to keep track of the history or versioning of the rows in the table: Note: When data is inserted into a system-versioned table, it is given a row_start value of the current timestamp, and a row_end value of FROM_UNIXTIME(2147483647. 999999). 

CREATE TABLE t (
   x varchar(100)
) WITH SYSTEM VERSIONING;

insert into t values ('RAM');

insert into t values ('SHAM'); 

insert into t values ('SITA'); 

SELECT x, ROW_START, ROW_END FROM t;

x   |ROW_START              |ROW_END                |
----+-----------------------+-----------------------+
RAM |2023-05-15 14:44:50.328|2038-01-19 08:44:07.999|
SHAM|2023-05-15 14:44:53.876|2038-01-19 08:44:07.999|
SITA|2023-05-15 14:44:57.852|2038-01-19 08:44:07.999|

DELETE from t where x='SITA';

SELECT x FROM t;
x   |
----+
RAM |
SHAM|

SELECT * FROM t FOR SYSTEM_TIME ALL;

x   |
----+
RAM |
SHAM|
SITA|

--Delete the versioning for the table t to save space
DELETE HISTORY FROM t;


SELECT * FROM t FOR SYSTEM_TIME ALL;
x   |
----+
RAM |
SHAM|