【MariaDB】System Versioned Table について調べてみた

## はじめに
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`になる。

```sql
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`にする方法もある。
その方法については詳しく書かないが、以下のリンクが参考になる。

https://mariadb.com/kb/en/system-versioned-tables/#adding-or-removing-system-versioning-tofrom-a-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` 

#### 例
- テーブル定義

```sql
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
```

- レコードを追加する

```sql
-- 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)`の値が入っている。
```sql
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`カラムの値が更新される

#### 例
- データ確認
```sql
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`が更新される。

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

```sql
-- 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 |
+----+----------+----------------------------+----------------------------+
```

```sql
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前のデータが取得できる
```sql
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後のレコードは抽出されない。

```sql
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後のデータも抽出される。

```sql
-- '... 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後のレコードは抽出されていない。

```sql
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`

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

##### 例

```sql
select * from users for system_time all;
```
```
+----+-----------+
| id | name      |
+----+-----------+
|  1 | tomoprog  |
|  1 | tomoprog1 |
|  2 | tanaka    |
+----+-----------+
```

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

それではまた。
TomoProg

## 参考資料
https://mariadb.com/kb/en/system-versioned-tables/

https://mariadb.com/ja/resources/blog/temporal-table/