Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@TomoProgin株式会社ネオジニア

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

Posted at

はじめに

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

参考資料

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?