--- title: "テンポラル・テーブル – MariaDB Server 10.3 新機能" publish_date: 2019-01-22 updated_date: 2023-10-12 author: "MariaDB" tags: - name: "MariaDB Server" url: "/ja/resources/blog/tag/mariadb-server-ja.md" --- # テンポラル・テーブル – MariaDB Server 10.3 新機能 2018年5月にGAとなった [MariaDB Server 10.3](https://staging-mdb.com/resources/blog/whats-new-in-mariadb-server-10-3/) では，すべてのデータ作成/更新/削除履歴を記録可能なテンポラル・テーブル([System-Versioned Tables](https://staging-mdb.com/kb/en/library/system-versioned-tables/))が新機能として追加されました。 テンポラル・テーブルにより以下のオペレーションが可能となり，より高度なセキュリティ，データ復旧性などが得られます。 - すべてのデータ変更の監査 - 過去の任意時点でのデータのリカバリ - 偶発的なデータ変更/削除や人為的エラー，アプリケーション エラーからの復旧 今回は簡単なサンプルデータを用いてテンポラルテーブルを解説したいと思います。 ### テスト環境 - MariaDB Server 10.3.12 - CentOS 7.6.1810 ### サンプルテーブル作成 従業員リストのようなテーブルを作成し，Linuxのdateコマンドでシステム時間を変更しながらサンプルデータをINSERTします。 ``` create table employees (id int, name varchar(50) ) WITH SYSTEM VERSIONING; ``` テンポラルテーブルを用いるには，上記のように，`WITH SYSTEM VERSIONING` を付与します。なお，CREATE TABLEだけでなく，ALTER TABLE でも利用可能です。 ### サンプルデータINSERT 以下の日付でサンプルデータをINSERTします。システムの日時は，例えば2017-01-01 9:00の場合は，以下のコマンドで変更することが可能です。(chronyd は disable としています)。 ``` date 010109002017 # date MMDDhhmmCCYY ``` 2017-01-01: Johnが入社 ``` INSERT INTO employees VALUES (1, 'John'); ``` 2017-03-01: Mariaが入社 ``` INSERT INTO employees VALUES (2, 'Maria'); ``` 2018-06-01: Bobが入社 ``` INSERT INTO employees VALUES (3, 'Bob'); ``` 2018-06-30: John(id=1)が退職 ``` delete from employees where id=1; ``` 2018-12-24: Eveが入社 ``` INSERT INTO employees VALUES (4, 'Eve'); ``` 2018-12-31: Bob(id=3)が退職 ``` delete from employees where id=3; ``` 2019-01-01: Tomが入社 ``` INSERT INTO employees VALUES (5, 'Tom'); ``` ### サンプルクエリ ###### 全レコードのSELECT 全履歴を参照するには，`FOR SYSTEM_TIME ALL` を付与してSELECTを実行します。 ``` MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME ALL; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | | 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ ``` System Versioned Table では，row\_start と row\_end が隠しカラムとして追加され，各レコードがいつからいつまで存在していたか記録されます。 ###### 2017-01-01時点での全社員リスト 任意時点でのレコードを抽出するには，`FOR SYSTEM_TIME AS OF TIMESTAMP '日時'`をSELECTに付与します。 ``` MariaDB [test]> SELECT *, row_start, row_end 　FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2017-01-01 10:00'; +------+------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | +------+------+----------------------------+----------------------------+ ``` ###### 2018-01-01時点での全社員リスト ``` MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-01-01 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ ``` ###### 2018-06-30時点での全社員リスト John が 2018-06-30 17:00 で退職しますが，10:00の時点では社員数が3となります。 ``` MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-30 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | +------+-------+----------------------------+----------------------------+ ``` ###### 2018-12-31時点での全社員リスト John が退職済で，2018-12-24 に Eve が入社していますので，社員数は 3 となります。 ``` MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-12-31 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ ``` ###### 最新の社員リスト 2018年いっぱいで Bob が退職し，2019-01-01 に Tom が入社していますので，社員数は 3 となります。 ``` MariaDB [test]> SELECT *, row_start, row_end FROM employees; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | | 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ ``` ### まとめ [MariaDB Server](https://staging-mdb.com/products/mariadb-tx/) 10.3 で新たに導入された[テンポラル・テーブル](https://staging-mdb.com/kb/en/library/system-versioned-tables/)について解説いたしました。複雑なSQL文を用いたり，アプリケーション側で日時の演算を行うことなく，任意の過去の時点でのレコードを簡単に参照可能であることが確認できました。 商用データベースでは以前から同様の機能を備えているものもあり，MariaDBへの移行を検討されている方にとって有益な新機能と考えます。