mcpField
M
mariadb-system-versioned-tables

mariadb-system-versioned-tables

github.com/mariadb数据与数据库17117 次安装2 周前更新
代码仓库

关于 mariadb-system-versioned-tables

面向 MariaDB 开发者和数据库管理员的技能指南，介绍系统版本表的配置、历史查询与维护，并涵盖版本差异和常见陷阱。

主要功能

  1. 01自动记录行变更历史
  2. 02按时间点或区间查询
  3. 03控制列级版本记录
  4. 04通过分区管理历史数据
  5. 05说明备份与结构变更注意事项

适用场景

  1. 01审计数据变更及时间
  2. 02核查过去某时点的数据
  3. 03跟踪薪资或价格变化
  4. 04排查生产数据变更

安装

复制下面的命令，在终端中运行。

$npx skills add https://github.com/mariadb/skills --skill mariadb-system-versioned-tables

安装后在 Claude Code 中输入/mariadb-system-versioned-tables确认已加载。

详细介绍

MariaDB 系统版本表

最后更新：2026-06-04

系统版本表会自动记录每一行变更的完整历史记录——无需触发器、应用逻辑或单独的审计表。MariaDB 在存储引擎中直接内置了数据历史追踪功能，可以查看数据在过去任意时间点的状态。

在兼容 MySQL 的数据库中，这项功能是 MariaDB 独有的。MySQL 没有同等功能。

提供版本： MariaDB 10.3。精确到事务的历史记录（仅限 InnoDB）：10.3。自动创建分区：10.9。备份时使用 --dump-history：10.11。将隐式 row_start/row_end 转换为显式列：11.7。在 64 位平台上，ROW_END 的 TIMESTAMP 范围扩展至 2106-02-07 UTC：11.5。

默认上下文： 除非用户指定其他版本，否则假设使用 MariaDB 11.8 LTS（2025 年 5 月 GA）。本技能中的版本说明表示各项功能所需的最低版本。

LLM 经常忽略的要点

情况建议
使用自定义 created_at/updated_at/deleted_at 列进行审计使用 WITH SYSTEM VERSIONING——MariaDB 会自动追踪所有变更
使用 SQL Server 的时态语法——WITH (SYSTEM_VERSIONING = ON)、显式的 PERIOD FOR SYSTEM_TIME 和命名的 HISTORY_TABLEMariaDB 使用 CREATE TABLE ... WITH SYSTEM VERSIONING（没有括号、没有 = ON，也没有单独的历史表）；在现有表上使用 ALTER TABLE t ADD SYSTEM VERSIONING 启用
使用单独的审计日志表和触发器系统版本表可以完全取代这种模式
询问上个月的数据状态使用 FOR SYSTEM_TIME AS OF '2026-01-01'——无需自定义逻辑
对版本表执行 TRUNCATE不允许（错误 4137）——改用 DELETE HISTORY
对版本表执行 ALTER TABLE 失败先设置 system_versioning_alter_history = KEEP
mysqldump 未包含历史行使用 mariadb-dump --dump-history（10.11+）

创建系统版本表

简化语法（推荐）：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2)
) WITH SYSTEM VERSIONING;

MariaDB 会自动添加隐藏的 ROW_START 和 ROW_END 时间戳列。每次 INSERT、UPDATE 和 DELETE 都会创建一条历史记录。

为现有表添加版本控制：

ALTER TABLE employees ADD SYSTEM VERSIONING;

移除版本控制（删除所有历史记录）：

ALTER TABLE employees DROP SYSTEM VERSIONING;

查询历史数据

FOR SYSTEM_TIME 子句紧跟在表名之后：

-- Data as it was at a specific point in time:
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2026-01-01 00:00:00';

-- All rows that existed during a period (both boundaries included):
SELECT * FROM employees FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2026-01-01';

-- Half-open range (includes start, excludes end):
SELECT * FROM employees FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';

-- Every version of every row, ever:
SELECT * FROM employees FOR SYSTEM_TIME ALL;

-- See the full history of one employee:
SELECT *, ROW_START, ROW_END FROM employees FOR SYSTEM_TIME ALL WHERE id = 42;

为会话中的所有查询应用隐式 AS OF：

SET system_versioning_asof = '2026-01-01 00:00:00';
-- Now all queries against versioned tables see that point in time
SELECT * FROM employees; -- returns 2026-01-01 snapshot
SET system_versioning_asof = DEFAULT; -- reset

精确到事务的历史记录（仅限 InnoDB）

如需精确追踪事务边界，而非使用时间戳：

CREATE TABLE ledger (
    id INT,
    amount DECIMAL(10,2),
    trx_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
    trx_end   BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(trx_start, trx_end)
) WITH SYSTEM VERSIONING;

SELECT * FROM ledger FOR SYSTEM_TIME AS OF TRANSACTION 12345;

内部使用 mysql.transaction_registry。与 PARTITION BY SYSTEM_TIME 不兼容。

列级控制

将特定列排除在版本控制之外（适用于计数器等频繁更新的列）：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    view_count INT WITHOUT SYSTEM VERSIONING  -- not tracked
) WITH SYSTEM VERSIONING;

或者，仅对非版本表中的特定列启用版本控制：

CREATE TABLE config (
    key VARCHAR(50),
    value TEXT WITH SYSTEM VERSIONING  -- only this column tracked
);

管理历史记录增长

每次更新都会添加一行。对于更新频繁的表，历史记录会快速增长。使用分区来保持当前数据的性能：

-- Separate current and historical partitions:
CREATE TABLE events (
    id INT,
    payload JSON
) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur  CURRENT
  );

-- Auto-rotate history by time interval (10.9+):
CREATE TABLE prices (
    symbol VARCHAR(10),
    price DECIMAL(10,4)
) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH AUTO (
    PARTITION p_cur CURRENT
  );

删除旧历史记录：

-- Delete all history before a date:
DELETE HISTORY FROM employees BEFORE SYSTEM_TIME '2024-01-01';

-- Delete all history (requires DROP SYSTEM VERSIONING + re-add, or partition drop):
ALTER TABLE employees DROP PARTITION p_hist;

需要 DELETE HISTORY 权限。版本表不允许使用 TRUNCATE。

对版本表执行 ALTER TABLE

默认情况下，为保护历史记录的完整性，对版本表执行 ALTER TABLE 会报错。如需允许该操作：

SET system_versioning_alter_history = KEEP;
ALTER TABLE employees ADD COLUMN manager_id INT;
SET system_versioning_alter_history = ERROR; -- restore default

KEEP 允许执行更改，但新列对应的历史行值将为 NULL——历史记录在技术上并不完整。添加列时，这通常可以接受。

将隐藏的 ROW_START/ROW_END 转换为显式列（11.7+，MDEV-27293）——如果表最初使用隐藏列形式启用了版本控制（没有 PERIOD FOR SYSTEM_TIME 子句），之后可以将其转换为显式列，无需移除版本控制。如果你最初采用了简单方式，后来需要精确到事务的历史记录或为工具显式命名列，这项功能会很有用。

关键注意事项

  • 版本化表禁止使用 TRUNCATE（错误 4137）。请改用 DELETE HISTORY 或分区管理。
  • 复制：ROW_END 会隐式添加到主键中。在副本上，这可能导致重放日志时出现重复键错误。解决方法：在副本上设置 secure_timestamp = YES。
  • 备份：mysqldump / mariadb-dump 默认会跳过历史行。使用 --dump-history（10.11+）将其包含在内。恢复时，设置 system_versioning_insert_history=ON（10.11+，MDEV-16546），以允许加载器直接写入 ROW_START/ROW_END；同时设置 secure_timestamp，以允许更改会话时间戳。
  • 表增长：如果不使用分区，历史行会无限累积。对于更新频繁的表，请从一开始就规划分区。
  • 带未来时间戳的 DELETE HISTORY：使用时间晚于 ROW_END 最大值的 BEFORE SYSTEM_TIME，可能会意外删除活动行（MDEV-25468）。在 32 位平台以及 MariaDB 11.5 之前，ROW_END 的最大值为 2038-01-19；在 64 位平台上的 MariaDB 11.5+ 中，最大值扩展至 2106-02-07 06:28:15 UTC（MDEV-32188）。请确保时间远低于平台的最大值。
  • 将 SYSTEM 用作列名：会导致 ALTER TABLE 语句出现解析器错误。请使用反引号：`SYSTEM`。

使用场景

  • 审计跟踪 — 无需编写应用程序代码，即可了解谁在何时更改了什么
  • GDPR / PCI DSS 合规 — 证明个人数据在任意日期包含哪些内容
  • 时间点恢复 — 无需完整恢复备份，即可将行或表还原到先前状态
  • 缓慢变化维度 — 跟踪员工部门 / 薪资历史、产品价格历史
  • 调试 — 准确查看生产数据的更改内容和时间

来源

本文未涵盖的主题，请参阅 MariaDB 官方文档：mariadb.com/docs。

评论

支持 Markdown