 |
System-versioned tables are shown in green color in the table list | |
|
|---|
 |
System-versioned tables are provided by MariaDB from version 10.3.
A third window then appears in the table header with the selection of the table type and the ROW START and ROW END fields, if these have been defined.
Normal tables can be changed to system-versioned tables.
|
Module for editing system-versioned MySQL tables:
|
| Display information and data of the table. |
| Create and delete table columns, indexes, partitions and data. |
| Edit table structure, table options, indexes, partitions, triggers, data and user permissions. |
| Tools for converting, copying and deleting data and structure. |
| Backup to the SQL script archive of table and manage files.
|
| The order of the table columns can be changed by drag & drop. |
|
| Table head: |
| Name: | Name of the new table |
| Comment: | Comment to be stored for the table. |
| Next autoindex: | First AUTOINCREMENT value for the table. |
|
| Engine: | Database format of the table. |
| Collation: | Character set and collation of the table. |
| Row format: | Format in which the table rows should be managed in the database. |
|
| Table type: | Selection of the table type. (BASE TABLE, SYSTEM VERSIONED) |
| Period start column: | Period field ROW START for SYSTEM VERSIONED. |
| Period end column: | Period field ROW END for SYSTEM VERSIONED. |
| |
| Table columns: |
| Name: | Column name |
| SV: | Select whether an update to the column creates a history data line at SYSTEM VERSIONED. |
| P/S: | Primary or spatial index of the table. |
| U: | Unique indexes to which the table column is linked. |
| I: | Simple indexes to which the table column is linked. |
| F: | Full-text indexes to which the table column is linked. |
| Type: | Data type of the column. |
| Length/Value: | Storage size or format of the data type. |
| Default value: | Default value of the table column that should be inserted if the column is not described when a data record is created. |
| NULL: | column accepts NULL values. |
| Option: | Data type options. For character strings, the character encoding. |
| Sorting: | Collation assignment to character set. |
| Extra: | Extra options for data column (AUTO_INCREMENT, ON UPDATE CURRENT_TIMESTAMP). |
| Comment: | Comment to be stored for the data column.
|
| Create fields after: | Number of new columns to be added from the specified position. |
|
Period fields cannot be changed. They must be of type BIGINT or TIMESTAMP.
With older server versions (MariaBD 10.3) these period fields must be created explicitly and selected under Extra: ROW START and ROW END. |
|
 |
Table is reloaded. |
 |
Display table information. |
 |
View and edit table data. |
 |
Data wizard to view and edit data. |
 |
Wizard to display and edit history data of system-versioned tables. |
 |
Display form for inserting new data records. |
 |
Module to import data into MySQL table. |
 |
View and edit table indexes. |
 |
View and edit the foreign keys the table is related to. |
 |
View and edit triggers on the table. |
 |
Creation and editing of table partitions.. |
 |
Editing table options. |
 |
Edit database user permissions to table. |
|
 |
Output table changes as an SQL script in the SQL editor. |
 |
Save table changes. |
|
 |
Save table definition and data in the archive. |
 |
Download table definition and data. |
 |
Display table archive. |
|
 |
Display table tools. |
 |
Display elete functions. |
|
 |
|
Labeled menu by double-clicking title bar.
|
| Save table definition and data to archive: |
 |
Saving the table creation script and data to the MyWAY SQL Manager archive.
|
| Table definition: | Table creation script is saved. |
| Table data: | Table data is saved as a SQL script. |
| GZIP: | Files are saved with GZip compression. |
|
| Download table definition and data: |
 |
Download the table creation script and data.
|
| Table definition: | Download the table creation script. |
| Table data: | Download the table data. |
| GZIP: | Files are downloaded with GZip compression. |
|
| Symbols in table columns: |
 |
Table column in primary index. By clicking, the index is displayed and can be edited. |
 |
Table column in spatial index. By clicking, the index is displayed and can be edited. |
 |
Table column in unique indexes. By clicking, the indexes will displayed and can be edited. |
 |
Table column in normal indexes. By clicking, the indexes will displayed and can be edited. |
 |
Table column in fulltext indexes. By clicking, the indexes will displayed and can be edited. |
 |
Shows the number of indexes in which the table field exists. By clicking, the indices are displayed and can be edited. |
|
 |
Add table column to table at position. |
 |
Delete table column from table. |
|
| Functions for selected table columns: |
| View: | Selected columns are shown as data. |
| Index: | Table columns are transferred to a new index. |
| Unique: | Table columns are transferred to a new unique index. |
| Full text: | Table columns are transferred to a new full-text index. |
| Spatial: | Table columns are included in a new spatial index. |
| Primary key: | Table columns are included in a primary index. |
| Delete: | Table columns are deleted. |
| Reverse the marking: | The selection is inverted. |
|
| Change system-versioned tables: |
 |
|
In order to be able to add, change or delete data columns in system-versioned tables, the system variable system_versioning_alter_history must be set to KEEP.
|
| System-versioned tables with TIMESTAMP for ROW START and ROW END: |
 |
|
In order to create system-versioned tables with period fields of TIMESTAMP type, it is necessary with older server versions to deactivate the NO_ZERO_DATE setting in the sql_mode system variable. Otherwise the error message appears: Invalid default value for ....
|
MariaDB System versioned tables, also known as temporal tables, are tables that store historical versions of data and allow data changes to be tracked over time. This type of table was introduced in MariaDB 10.3. They consist of a current data table and a history table. The current data table stores the current version of the data, while the history table maintains the historical versions of the data.
System-versioned tables use timestamp columns to track the validity period of each version. These columns indicate when a particular version of a row became valid and when it was no longer valid. When data is changed in a system-versioned table, MariaDB automatically creates a new historical version of the changed row in the history table. The current data table is updated with the new version and the previous data version remains in the history table. To retrieve historical data, queries can use the FOR SYSTEM_TIME clause, specifying a time period or a specific point in time. This allows the table to be queried as it was at a specific historical point in time.
System-versioned tables support primary keys, unique keys, and foreign keys. Indexes defined on the table are also used to optimize historical data retrieval.
System-versioned tables in MariaDB provide the ability to maintain histories of data changes, enabling querying and analysis over time. They are particularly useful in scenarios where there is a need to track data changes over time, implement audit trails, or perform historical data analysis. |
