MariaDBで始めるテポーラルテーブル：履歴データをタイムトラベルで追跡

MariaDBでシステムバージョン化された(テポーラル)テーブルに履歴データを挿入する方法

前提条件

このチュートリアルを始める前に、以下の条件を満たしていることを確認してください。

• MariaDB 10.4がインストールされている
• テポーラルテーブルを作成する方法を知っている

手順

1. 履歴データを挿入するテポーラルテーブルを作成します。

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP DEFAULT NULL,
  UNIQUE KEY (product_id, valid_from)
) ENGINE = InnoDB TEMPORAL('valid_from', 'valid_to');

1. 履歴データを挿入します。

INSERT INTO sales (product_id, quantity, price, valid_from)
VALUES
  (1, 10, 10.00, '2024-04-01'),
  (1, 12, 12.00, '2024-04-10'),
  (2, 5, 15.00, '2024-04-15');

1. 特定の時点におけるデータの状態をクエリします。

SELECT * FROM sales
WHERE product_id = 1 AND valid_from <= '2024-04-10';

このクエリは、2024年4月10日時点における製品1の販売情報を返します。

1. データの変更履歴を分析します。

SELECT * FROM sales_history
WHERE product_id = 1;

このクエリは、製品1の販売情報の変更履歴を返します。

このチュートリアルでは、MariaDB 10.4でシステムバージョン化された(テポーラル)テーブルに履歴データを挿入する方法について説明しました。テポーラルテーブルは、データの変更履歴を自動的に追跡する特殊なタイプのテーブルです。これにより、特定の時点におけるデータの状態を簡単にクエリしたり、データの変更履歴を分析したりすることができます。

テポーラルテーブルの作成

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP DEFAULT NULL,
  UNIQUE KEY (product_id, valid_from)
) ENGINE = InnoDB TEMPORAL('valid_from', 'valid_to');

• id: 主キー
• product_id: 製品ID
• quantity: 販売数量
• price: 販売価格
• valid_from: 有効開始日

valid_fromとvalid_to列を使用して、データの有効期間を定義します。valid_from列は、データが有効になる日付を指定します。valid_to列は、データが無効になる日付を指定します。valid_to列がNULLの場合は、データは現在も有効です。

履歴データの挿入

INSERT INTO sales (product_id, quantity, price, valid_from)
VALUES
  (1, 10, 10.00, '2024-04-01'),
  (1, 12, 12.00, '2024-04-10'),
  (2, 5, 15.00, '2024-04-15');

このコードは、salesテーブルに3つの履歴データを挿入します。

1. 2024年4月1日に製品1を10個、価格10.00ドルで販売した

特定の時点におけるデータの状態のクエリ

SELECT * FROM sales
WHERE product_id = 1 AND valid_from <= '2024-04-10';

データの変更履歴の分析

SELECT * FROM sales_history
WHERE product_id = 1;

説明

補足

• サンプルコードは、説明のために簡略化されています。実際のアプリケーションでは、より複雑なクエリや操作を使用する必要がある場合があります。

MariaDBでシステムバージョン化された(テポーラル)テーブルに履歴データを挿入するその他の方法

INSERT ... SELECTステートメント

INSERT INTO sales (product_id, quantity, price, valid_from)
SELECT product_id, quantity, price, '2024-04-15'
FROM sales_history
WHERE product_id = 1 AND valid_from = '2024-04-10';

このコードは、sales_historyテーブルから2024年4月10日時点における製品1の販売情報を抽出し、salesテーブルに新しい履歴データとして挿入します。

MERGEステートメント

MERGE INTO sales AS target
USING sales_history AS source
ON (target.product_id = source.product_id AND target.valid_from = source.valid_from)
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity, price, valid_from)
  VALUES (source.product_id, source.quantity, source.price, source.valid_from);

このコードは、sales_historyテーブルとsalesテーブルを結合し、product_idとvalid_from列で一致するレコードを比較します。一致するレコードが見つからない場合、sales_historyテーブルから新しい履歴データをsalesテーブルに挿入します。

トリガーを使用して、salesテーブルに新しいレコードが挿入されるたびに、sales_historyテーブルに履歴データを自動的に挿入することができます。

CREATE TRIGGER sales_history_insert
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  INSERT INTO sales_history (product_id, quantity, price, valid_from, valid_to)
  VALUES (NEW.product_id, NEW.quantity, NEW.price, NEW.valid_from, NEW.valid_to);
END;

MariaDB 10.4でシステムバージョン化された(テポーラル)テーブルに履歴データを挿入するには、さまざまな方法があります。上記の方法は、それぞれ異なる利点と欠点があります。ニーズに合った方法を選択してください。

補足

• トリガーを使用する場合は、パフォーマンスへの影響に注意する必要があります。
• INSERT ... SELECTステートメントを使用する場合は、データの重複に注意する必要があります。
• MERGEステートメントは、INSERT ... SELECTステートメントとトリガーよりも柔軟性がありますが、複雑さも増します。