はじめに

MariaDBの機能を調べている中でSystem Versioned Tableという機能があることを知った。
この記事ではその機能について調べたことをまとめる。

この記事の対象者

System Versioned Tableについて知りたい人
MariaDBでデータの履歴を管理する方法を探している人

`System Versioned Table` とは

Temporal Table（テンポラルテーブル）と言われるものの一つ。
Temporary Table（一時テーブル）とは違うので要注意。
すべてのデータの変更履歴を保存しておくことができる。

MariaDBではバージョン10.3から使用可能。
また、MySQLではこの機能はサポートされていない。

どういうときに使うのか

データの変更履歴を保存しておけるので、履歴テーブルのようなものに使用できる。
例えば、会員制サービスなどで会員のプラン（無料プラン、有料プランなど）の変更履歴を管理したいときなどに使用できると思われる。

また、MariaDBのナレッジベースでは使用例として、

Forensic analysis & legal requirements to store data for N years

Data analytics (retrospective, trends etc.), e.g. to get your staff information as of one year ago

Point-in-time recovery - recover a table state as of particular point in time

という記載もあり、法的にN年間分のデータを持っておく必要があったり、データ分析などにも使用できるようだ。

使い方

`System Versioned Table` の作り方

CREATE TABLE時にWITH SYSTEM VERSIONINGというキーワードを入れるとSystem Versioned Tableになる。

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

この例だとカラムは一つしか定義していないが、実際はROW_START、ROW_ENDというカラムが擬似的に作成されており、select句で明示的に指定することで値を見ることができる。
ちなみにROW_START、ROW_ENDの型はtimestamp(6)

MariaDB [test_db]> select x, ROW_START, ROW_END from t;
+------+----------------------------+----------------------------+
| x    | ROW_START                  | ROW_END                    |
+------+----------------------------+----------------------------+
|    1 | 2024-07-31 04:51:10.469755 | 2038-01-19 03:14:07.999999 |
+------+----------------------------+----------------------------+

あくまで擬似的に作られているため、show create table を見てもカラムは存在しない。

MariaDB [test_db]> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING

select句で * を使った場合もカラムは出てこない。

MariaDB [test_db]> select * from t;
+------+
| x    |
+------+
|    1 |
+------+

また、既存のテーブルをSystem Versioned Tableにする方法もある。
その方法については詳しく書かないが、以下のリンクが参考になる。

insert時の挙動

insertすると、ROW_START、ROW_ENDに以下の値が設定される。

ROW_START

現在日時（CURRENT_TIMESTAMP(6))

ROW_END

FROM_UNIXTIME(2147483647.999999)
変換すると 2038-01-19 03:14:07.999999

例

テーブル定義

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING

レコードを追加する

-- CURRENT_TIMESTAMPの値を固定する
SET @@timestamp = UNIX_TIMESTAMP('2024-07-31 12:34:56.000001');

-- レコードを追加する
insert into users values(1, 'tomoprog')

追加したレコードを確認する

ROW_STARTにはCURRENT_TIMESTAMP(6)の値、ROW_ENDにはFROM_UNIXTIME(2147483647.999999)の値が入っている。

select id, name, ROW_START, ROW_END from users;

+----+----------+----------------------------+----------------------------+
| id | name     | ROW_START                  | ROW_END                    |
+----+----------+----------------------------+----------------------------+
|  1 | tomoprog | 2024-07-31 12:34:56.000001 | 2038-01-19 03:14:07.999999 |
+----+----------+----------------------------+----------------------------+

update時の挙動

update対象となったレコードのROW_STARTカラムの値が更新される

例

データ確認

select id, name, ROW_START, ROW_END from users;

+----+----------+----------------------------+----------------------------+
| id | name     | ROW_START                  | ROW_END                    |
+----+----------+----------------------------+----------------------------+
|  1 | tomoprog | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
|  2 | tanaka   | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
+----+----------+----------------------------+----------------------------+

データを更新する

対象となったレコード（id=1）だけROW_STARTカラムが更新される。
また、updateの元になったレコードのROW_ENDが更新される。

update users set name = 'tomoprog1' where id = 1;

-- for system_time all ですべてのレコードを抽出できる
select id, name, ROW_START, ROW_END from users for system_time all;

+----+-----------+----------------------------+----------------------------+
| id | name      | ROW_START                  | ROW_END                    |
+----+-----------+----------------------------+----------------------------+
|  1 | tomoprog  | 2024-07-31 05:55:18.521492 | 2024-07-31 05:58:15.982077 |
|  1 | tomoprog1 | 2024-07-31 05:58:15.982077 | 2038-01-19 03:14:07.999999 |
|  2 | tanaka    | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
+----+-----------+----------------------------+----------------------------+

過去のデータをselectする方法

FOR SYSTEM_TIME句を使うと過去のデータを抽出できる。
逆にFOR SYSTEM_TIME句を使わない場合は現在のデータを抽出することになる。
また、FOR SYSTEM_TIMEで指定する方法は以下の4種類ある。

過去の特定の日時を指定する `AS OF`

AS OFを使うと過去の特定の日時の時点で有効だったレコードを抽出できる。
使い方はFOR SYSTEM_TIME AS OF 日時となる。

例

以下のusersテーブルに対してupdate文を発行して、id=1のROW_STARTカラムが変化した状態とする。
（以降の他の方法についても、このテーブルの状況を例として扱う。）

+----+----------+----------------------------+----------------------------+
| id | name     | ROW_START                  | ROW_END                    |
+----+----------+----------------------------+----------------------------+
|  1 | tomoprog | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
|  2 | tanaka   | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
+----+----------+----------------------------+----------------------------+

update users set name = 'tomoprog1' where id = 1;

+----+-----------+----------------------------+----------------------------+
| id | name      | ROW_START                  | ROW_END                    |
+----+-----------+----------------------------+----------------------------+
|  1 | tomoprog1 | 2024-07-31 05:58:15.982077 | 2038-01-19 03:14:07.999999 |
|  2 | tanaka    | 2024-07-31 05:55:18.521492 | 2038-01-19 03:14:07.999999 |
+----+-----------+----------------------------+----------------------------+

ROW_STARTの1ミリ秒前でselectしてみる

update前のデータが取得できる

select * from users for system_time as of timestamp'2024-07-31 05:58:15.982076';

+----+----------+
| id | name     |
+----+----------+
|  1 | tomoprog |
|  2 | tanaka   |
+----+----------+

ROW_STARTと同じ日時でselectしてみる

update後のデータが取得できる

+----+-----------+
| id | name      |
+----+-----------+
|  1 | tomoprog1 |
|  2 | tanaka    |
+----+-----------+

範囲で指定する `BETWEEN 開始日時 AND 終了日時`

開始日時〜終了日時の時点で存在するレコードが対象となる。
開始日時、終了日時はどちらも範囲に含まれる。
使い方はFOR SYSTEM_TIME BETWEEN 開始日時 AND 終了日時となる。

例

updateする1ミリ秒前でselectしてみる

まだupdate後のレコードは抽出されない。

select * from users for system_time between '2024-07-31 05:55:18.521492' and '2024-07-31 05:58:15.982076';

+----+----------+
| id | name     |
+----+----------+
|  1 | tomoprog |
|  2 | tanaka   |
+----+----------+

updateした日時を含めてselectしてみる

update後のデータも抽出される。

-- '... 05:58:15.982076' -> '... 05:58:15.982077' に変更
select * from users for system_time between '2024-07-31 05:55:18.521492' and '2024-07-31 05:58:15.982077';

+----+-----------+
| id | name      |
+----+-----------+
|  1 | tomoprog  |
|  1 | tomoprog1 |
|  2 | tanaka    |
+----+-----------+

範囲で指定する `FROM 開始日時 TO 終了日時`

BETWEENと同じで範囲指定だが、終了日時は範囲外となる。

例

BETWEENのときと同じ日時でクエリを発行しているが、終了日時は範囲外となるため、update後のレコードは抽出されていない。

select * from users for system_time from '2024-07-31 05:55:18.521492' to '2024-07-31 05:58:15.982077';

+----+----------+
| id | name     |
+----+----------+
|  1 | tomoprog |
|  2 | tanaka   |
+----+----------+

すべてのデータを抽出する `ALL`

日時は関係なくすべての時間においてのレコードを抽出する。

例

select * from users for system_time all;

+----+-----------+
| id | name      |
+----+-----------+
|  1 | tomoprog  |
|  1 | tomoprog1 |
|  2 | tanaka    |
+----+-----------+

まとめ

この記事ではSystem Versioned Tableについて調べたことをまとめた。
MariaDBでデータの履歴を管理したいということがあれば、この機能を検討してみるのもよいかもしれない。

それではまた。
TomoProg

参考資料