12. Tables temporelles : versionnement système et périodes applicatives

MIRAJ offre deux dimensions temporelles, séparément ou ensemble (tables bitemporelles) :

• le versionnement système (WITH SYSTEM VERSIONING) garde l'historique des lignes : un UPDATE ou un DELETE conserve l'ancienne version, avec la date de sa disparition. On répond ensuite à « quelle était la valeur au 31 décembre ? » sans journal d'audit écrit à la main ;
• les périodes applicatives (PERIOD FOR) décrivent une validité métier (un tarif valable du 1er janvier au 31 mars, un contrat, une affectation) que l'application écrit elle-même, avec FOR PORTION OF pour modifier une partie seulement d'une période et WITHOUT OVERLAPS pour interdire les chevauchements.

12.1 Créer une table versionnée#

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL
) WITH SYSTEM VERSIONING;

MIRAJ ajoute deux colonnes invisibles TIMESTAMP(6), row_start et row_end, qui délimitent la période de validité de chaque version, à la microseconde. SELECT * ne les montre pas, INSERT sans liste de colonnes ne leur donne pas de valeur, SHOW CREATE TABLE et DESCRIBE les omettent ; on les désigne par leur nom :

INSERT INTO stock VALUES (12, 40), (13, 8);
SELECT article, qte, row_start, row_end FROM stock;

+---------+-----+----------------------------+----------------------------+
| article | qte | row_start                  | row_end                    |
+---------+-----+----------------------------+----------------------------+
|      12 |  40 | 2026-10-19 03:33:14.747306 | 2038-01-19 03:14:07.999999 |
|      13 |   8 | 2026-10-19 03:33:14.747306 | 2038-01-19 03:14:07.999999 |
+---------+-----+----------------------------+----------------------------+

Une ligne courante a pour row_end le maximum, 2038-01-19 03:14:07.999999. On peut écrire soi-même les colonnes de période, qui restent alors visibles :

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL,
    debut   TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    fin     TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (debut, fin)
) WITH SYSTEM VERSIONING;

Les colonnes de période sont de type TIMESTAMP ou DATETIME (sinon erreur 4146, `d` doit être de type TIMESTAMP(6) pour la table versionnée `mauvais`) ; TIMESTAMP(6) garde la microseconde (avec moins de décimales, la fin courante est 2038-01-19 03:14:07 suivi d'autant de 9). MIRAJ écrase toute valeur qu'un INSERT ou un UPDATE leur donne (voir system_versioning_insert_history, 12.10).

12.2 Ce que conservent les écritures#

L'historique suit la transaction : un ROLLBACK le défait, un arrêt brutal ne garde que les transactions validées. Les lectures ordinaires (SELECT * FROM stock) ne voient que les lignes courantes ; les clés (PRIMARY KEY, UNIQUE) ne portent que sur elles.

Une table versionnée se combine avec les autres objets du moteur. Une colonne qui tire sa valeur d'une séquence (id BIGINT DEFAULT NEXTVAL(ids), voir le chapitre 7) fonctionne comme sur une table ordinaire, y compris après ALTER TABLE … ADD SYSTEM VERSIONING : l'historique garde l'identifiant tel qu'il a été écrit, et les copies d'archive ne consomment pas la séquence. Une table sur disque (ENGINE = Aria) peut être versionnée (voir 12.13).

12.3 Lire le passé : FOR SYSTEM_TIME#

SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME AS OF TIMESTAMP '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';
SELECT * FROM stock FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM stock FOR SYSTEM_TIME ALL;

Exemple : la table stock de 12.1 après une modification et une suppression.

SET @avant = NOW(6);
UPDATE stock SET qte = 35 WHERE article = 12;
DELETE FROM stock WHERE article = 13;

SELECT * FROM stock;                                  -- lignes courantes
SELECT * FROM stock FOR SYSTEM_TIME AS OF @avant;     -- état d'avant les deux écritures
SELECT article, qte, row_start, row_end FROM stock FOR SYSTEM_TIME ALL ORDER BY article, row_start;

+---------+-----+
| article | qte |
+---------+-----+
|      12 |  35 |
+---------+-----+

+---------+-----+
| article | qte |
+---------+-----+
|      12 |  40 |
|      13 |   8 |
+---------+-----+

+---------+-----+----------------------------+----------------------------+
| article | qte | row_start                  | row_end                    |
+---------+-----+----------------------------+----------------------------+
|      12 |  40 | 2026-10-19 03:33:14.747306 | 2026-10-19 03:33:15.756576 |
|      12 |  35 | 2026-10-19 03:33:15.756576 | 2038-01-19 03:14:07.999999 |
|      13 |   8 | 2026-10-19 03:33:14.747306 | 2026-10-19 03:33:15.757618 |
+---------+-----+----------------------------+----------------------------+

La clause se place après le nom de la table, avant l'alias, et vaut pour chaque table d'une jointure, d'une sous-requête, d'une table dérivée, d'une UNION, de la source d'un INSERT … SELECT, d'une vue (la clause reste dans sa définition et s'applique à chaque lecture), et dans le corps d'un déclencheur ou d'une procédure :

SELECT c.nom, s.qte
FROM clients c JOIN stock FOR SYSTEM_TIME AS OF '2025-12-31' s ON s.article = c.article;

CREATE VIEW stock_fin_2025 AS SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';

Les bornes sont des expressions (NOW(6) - INTERVAL 1 DAY, @instant, ?). Comparer une version à NOW() (à la seconde) plutôt qu'à NOW(6) peut manquer une version écrite dans la seconde en cours. Une table qui n'est pas versionnée donne l'erreur 4124 (La table `ordinaire` n'est pas versionnée par le système).

12.4 Supprimer de l'historique : DELETE HISTORY#

DELETE HISTORY FROM stock;                                   -- tout l'historique
DELETE HISTORY FROM stock BEFORE SYSTEM_TIME '2024-01-01';   -- versions terminées avant cette date

Les lignes courantes ne sont jamais touchées. L'instruction rend le nombre de versions supprimées (OK, 1 ligne(s) affectée(s) pour une seule version passée). La borne est une expression (BEFORE SYSTEM_TIME NOW(6) - INTERVAL 1 YEAR).

12.5 Ajouter ou retirer le versionnement, modifier une table versionnée#

ALTER TABLE stock ADD SYSTEM VERSIONING;
ALTER TABLE stock DROP SYSTEM VERSIONING;

À l'ajout, les lignes existantes démarrent à l'instant de l'ALTER. Au retrait, l'historique est supprimé et les colonnes de période implicites disparaissent.

Un ALTER TABLE qui change les colonnes d'une table versionnée (colonne ajoutée, supprimée, modifiée, renommée, moteur, jeu de caractères) est refusé (4119) tant que system_versioning_alter_history vaut ERROR (défaut) :

ERREUR 4119 (HY000) : Interdit sur la table versionnée `tempo`.`stock`. Changez @@system_versioning_alter_history pour poursuivre l'ALTER.

SET system_versioning_alter_history = KEEP;
ALTER TABLE stock ADD COLUMN emplacement VARCHAR(20);

Sous KEEP, la modification s'applique aussi à l'historique ; une colonne ajoutée y prend sa valeur par défaut. Index, clés, contraintes, options et DROP SYSTEM VERSIONING restent admis sous ERROR. Les colonnes de période ne se modifient pas (1235). RENAME TABLE, DROP TABLE, CREATE TABLE … LIKE et CREATE OR REPLACE TABLE suivent la table.

12.6 Périodes applicatives : PERIOD FOR#

CREATE TABLE tarif (
    article INT,
    prix    DECIMAL(10,2),
    debut   DATE,
    fin     DATE,
    PERIOD FOR validite (debut, fin)
);
ALTER TABLE contrat ADD PERIOD IF NOT EXISTS FOR duree (signe_le, echeance);
ALTER TABLE contrat DROP PERIOD IF EXISTS FOR duree;

Les deux colonnes sont de même type, DATE, DATETIME ou TIMESTAMP (sinon 1063 ou 4153), distinctes (1110), ni générées ni colonnes du versionnement système (4155) ; elles deviennent NOT NULL. Une seule période applicative par table (4154), jamais sur une table temporaire (4152). Chaque ligne doit vérifier début < fin : sinon erreur 4025 CONSTRAINTvaliditefailed forbase.tarif`, à l'écriture comme à l'ajout de la période sur des lignes existantes. Cette contrainte ne se retire que parDROP PERIOD(DROP CONSTRAINT validite: 4158). La période est affichée parSHOW CREATE TABLE(PERIOD FOR validite (debut, fin)), suit le renommage de ses colonnes et figure dansinformation_schema.PERIODS(avec la périodeSYSTEM_TIME` des tables versionnées).

La période est fermée au début et ouverte à la fin : [debut, fin).

INSERT INTO tarif VALUES (13, 5, '2026-05-01', '2026-04-01');
ERREUR 4025 (23000) : CONSTRAINT `validite` failed for `tempo`.`tarif`

12.7 Modifier une portion de période : FOR PORTION OF#

UPDATE tarif FOR PORTION OF validite FROM '2026-03-01' TO '2026-04-01'
   SET prix = prix * 0.9 WHERE article = 12;
DELETE FROM tarif FOR PORTION OF validite FROM '2026-07-01' TO '2026-09-01' WHERE article = 12;

Avec une seule ligne (12, 20.00, 2026-01-01, 2027-01-01) au départ, les deux instructions ci-dessus laissent :

SELECT * FROM tarif ORDER BY debut;

+---------+-------+------------+------------+
| article | prix  | debut      | fin        |
+---------+-------+------------+------------+
|      12 | 20.00 | 2026-01-01 | 2026-03-01 |
|      12 | 18.00 | 2026-03-01 | 2026-04-01 |
|      12 | 20.00 | 2026-04-01 | 2026-07-01 |
|      12 | 20.00 | 2026-09-01 | 2027-01-01 |
+---------+-------+------------+------------+

Seules les lignes dont la période chevauche [a, b) sont visées. Une ligne qui déborde de l'intervalle est découpée : la portion [max(début, a), min(fin, b)) est modifiée (ou supprimée), et le reste de sa période est gardé dans une ou deux lignes insérées, copies de la ligne d'origine avant modification, de périodes [début, a) et [b, fin). Exemple, la ligne ('b', 1999-01-01, 2018-12-12) après DELETE … FOR PORTION OF p FROM '2001-01-01' TO '2018-01-01' devient ('b', 1999-01-01, 2001-01-01) et ('b', 2018-01-01, 2018-12-12).

Les déclencheurs INSERT de la table s'exécutent pour chaque morceau inséré, en plus des déclencheurs UPDATE ou DELETE de la portion. L'alias de la table s'écrit après la clause (UPDATE t FOR PORTION OF p FROM a TO b AS x SET …). WHERE, ORDER BY et LIMIT s'appliquent ; un intervalle vide ne touche rien. Une colonne de la période dans SET : 4157 ; période inconnue : 4156. L'instruction est atomique : un morceau qui viole une clé annule tout. Le nombre de lignes affectées est celui des portions modifiées ou supprimées.

12.8 Interdire les chevauchements : WITHOUT OVERLAPS#

CREATE TABLE affectation (
    employe INT,
    poste   VARCHAR(20),
    debut   DATE,
    fin     DATE,
    PERIOD FOR p (debut, fin),
    PRIMARY KEY (employe, p WITHOUT OVERLAPS)
);
ALTER TABLE tarif ADD UNIQUE u_tarif (article, validite WITHOUT OVERLAPS);

INSERT INTO affectation VALUES (1, 'caisse',  '2026-01-01', '2026-02-01');
INSERT INTO affectation VALUES (1, 'accueil', '2026-02-01', '2026-03-01');  -- contiguë : admise
INSERT INTO affectation VALUES (1, 'stock',   '2026-01-20', '2026-02-10');

ERREUR 1062 (23000) : Duplicate entry '1-2026-02-10-2026-01-20' for key 'PRIMARY'

Deux lignes de même clé (sans NULL) ne peuvent pas avoir de périodes qui se chevauchent : erreur 1062 (le message cite les colonnes de la clé, puis la fin et le début de la ligne refusée), à l'insertion, à la mise à jour, sous FOR PORTION OF (les morceaux ne chevauchent jamais la portion) et à l'ajout de la clé sur des lignes existantes. Des périodes contiguës ([1, 5) et [5, 9)) ne se chevauchent pas. En concurrence, deux transactions qui écrivent des lignes qui se chevauchent ne peuvent pas valider toutes les deux : la seconde échoue à sa validation (1213, à rejouer). La clé ne peut pas citer une colonne de la période (4170) ; un index non unique n'accepte pas WITHOUT OVERLAPS (4171). Les clés figurent dans information_schema.KEY_PERIOD_USAGE. Une période utilisée par une telle clé ne se retire pas (1235) : retirer d'abord la clé.

12.9 Partitions de l'historique : PARTITION BY SYSTEM_TIME#

CREATE TABLE mouvement (id INT, qte INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS '2026-01-01 00:00:00'
(PARTITION h_janvier HISTORY, PARTITION h_fevrier HISTORY, PARTITION courant CURRENT);

CREATE TABLE journal (id INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME LIMIT 100000 PARTITIONS 4;      -- p0, p1, p2 HISTORY puis pn CURRENT

Les partitions sont logiques : les versions passées restent dans la table compagnon (12.11), et une version appartient à une partition HISTORY selon sa fin de période (INTERVAL n unité [STARTS …], la première recevant aussi les versions d'avant, la dernière celles d'après) ou son rang dans l'ordre des fins (LIMIT n, la dernière recevant le reste) ; sans critère, tout l'historique est dans la dernière. Il faut au moins une partition HISTORY et une seule CURRENT, la dernière (4128) ; INTERVAL ou LIMIT nul : 4127 ; STARTS absent : début du jour de création. AUTO est gardé et réaffiché, sans créer de partition.

• SELECT … FROM mouvement PARTITION (h_janvier, courant) lit ces partitions (partition HISTORY d'une rotation LIMIT : 1235) ; les autres lectures ne changent pas.
• ALTER TABLE mouvement DROP PARTITION h_janvier supprime les versions de la partition et la retire (les plus anciennes seulement sous INTERVAL ou LIMIT ; STARTS avance d'autant) ; la partition CURRENT ou la dernière HISTORY ne se retirent pas (4128).
• ALTER TABLE mouvement TRUNCATE PARTITION h_fevrier supprime ses versions et la garde.
• ALTER TABLE mouvement REMOVE PARTITIONING retire les partitions (l'historique reste) ; DROP SYSTEM VERSIONING sur une table ainsi partitionnée : 4131. PARTITION BY SYSTEM_TIME ne s'écrit qu'à la création (dans un ALTER TABLE : 1235).

SHOW CREATE TABLE, CREATE TABLE … LIKE et information_schema.PARTITIONS (méthode SYSTEM_TIME, description HISTORY / CURRENT) montrent la définition.

12.10 Variables#

Les trois se règlent pour la session (SET) ou pour les sessions suivantes (SET GLOBAL). La valeur de system_versioning_asof est évaluée au SET (SET system_versioning_asof = NOW() - INTERVAL 1 DAY) et s'affiche à la microseconde.

12.11 Sous le capot#

L'historique vit dans une table compagnon __sv_<table> de la même base, sans clé ni contrainte, que SHOW TABLES et information_schema cachent. Elle se lit comme une table ordinaire (SELECT * FROM __sv_stock), avec les droits de sa table. information_schema.TABLES.TABLE_TYPE vaut SYSTEM VERSIONED. BACKUP / RESTORE la copient avec la table ; miraj-dump n'exporte que les lignes courantes (le script recrée une table versionnée, sans historique), avec ses périodes applicatives, ses clés WITHOUT OVERLAPS et ses partitions SYSTEM_TIME. Un nom de table qui commence par __sv_ est réservé.

La contrainte début < fin d'une période applicative et le refus des chevauchements sont faits par des déclencheurs synthétiques, comme l'écriture de l'historique : ils ne figurent ni dans SHOW TRIGGERS ni dans information_schema.TRIGGERS. Une clé WITHOUT OVERLAPS est rangée comme un index d'unicité sur (colonnes, fin, début).

12.12 Codes d'erreur#

12.13 Écarts avec le moteur de référence#

• Une même transaction qui modifie deux fois une ligne y laisse deux versions (la référence n'en laisse qu'une).
• FOR PORTION OF insère les morceaux restants après toutes les lignes modifiées (la référence : ligne après ligne) ; l'ordre d'exécution des déclencheurs INSERT diffère donc quand plusieurs lignes sont découpées.
• ALTER TABLE … ADD d'une clé WITHOUT OVERLAPS compare les valeurs de clé de texte sans casse ni interclassement fin ; à l'écriture, la comparaison est celle de la colonne.
• PARTITION BY SYSTEM_TIME : partitions logiques (une seule table compagnon), AUTO sans création automatique, ADD PARTITION et ALTER TABLE … PARTITION BY SYSTEM_TIME non pris en charge (1235).
• system_versioning_insert_history ne permet pas d'insérer une version déjà terminée (1235).
• Non pris en charge : versionnement par identifiant de transaction (colonnes BIGINT UNSIGNED : 4146, AS OF TRANSACTION : 1235), WITH / WITHOUT SYSTEM VERSIONING par colonne, tables temporaires versionnées, versionnement d'une table partitionnée par RANGE / LIST / HASH.
• Les colonnes de période sont des TIMESTAMP : elles suivent le time_zone de la session comme toute colonne TIMESTAMP, FOR SYSTEM_TIME compare des instants et non des heures affichées. La fin de période d'une ligne courante, écrite comme 2038-01-19 03:14:07.999999 dans le fuseau de la session qui écrit, s'affiche donc décalée sous un autre fuseau.
• Une table sur disque (ENGINE = Aria…) peut être versionnée ; sa table compagnon reste une table en mémoire.
• Les suppressions et modifications faites par une action de clé étrangère (ON DELETE CASCADE…) ne laissent pas d'historique : elles ne déclenchent rien, comme pour les déclencheurs.