MariaDB时态数据表

MariaDB现在支持以下形式的时态数据表：系统版本化表(允许您查询和操作历史数据)、应用程序时间段(允许您查询和操作数据的时间范围)和双时态表(将系统版本控制和应用程序时间段结合在一起)。

系统版本化表存储所有更改的历史记录，而不仅仅是当前有效的数据。这允许对任意时间点进行数据分析、审核更改以及比较来自不同时间点的数据。典型使用案例包括：

数据分析(回顾、趋势等)，例如获取一年前的员工信息。

CREATE TABLE语法已扩展，允许创建系统版本化表。根据SQL：2011，要进行系统版本控制，表必须有两个生成的列、一个句点和一个特殊的表选项子句：

CREATE TABLE t(x int，start_timeamp时间戳(6)始终作为行开始生成，end_timeamp(6)始终作为行结束生成，system_time(start_timeamp，end_timeamp))周期为系统版本控制；

在后一种情况下，不会创建额外的列，并且它们不会扰乱SELECT*FROM t的输出。版本控制信息仍将存储，并且可以通过伪列ROW_START和ROW_END进行访问：

show create table t\G*1.行*。

show create table t\G*1.行*

ALTER TABLE t添加列ts时间戳(6)始终生成为行开始，添加列TE时间戳(6)始终生成为行结束，添加SYSTEM_TIME(ts，TE)周期，添加系统版本化；

show create table t\G*1.row*表：t创建表：创建表`t`(`x`int(11)默认NULL，`ts`时间戳(6)始终作为行开始生成，`te`时间戳(6)总是在行结束时生成，PERIOD FOR SYSTEM_TIME(。

要查询历史数据，可以直接在表名之后(如果有的话)在表名之前使用FOR SYSTEM_TIME子句。SQL：2011提供了三个语法扩展：

as of用于查看过去特定时间点的表：

开始和结束之间将显示在两个指定时间点之间的任何点可见的所有行。它的工作方式是包容的，也会显示在开始时完全可见或在结束时完全可见的行。

从开始到结束还将显示在两个指定时间点之间的任何点可见的所有行，包括开始，但不包括结束。

如果未使用FOR SYSTEM_TIME子句，则表将显示当前数据，就好像已为SYSTEM_TIME指定了FOR CURRENT_TIMESTAMP。

在FROM子句的视图或子查询中使用系统版本化表时，FOR SYSTEM_TIME可以直接用于视图或子查询正文，或者在SELECT中使用FOR SYSTEM_TIME时(非标准)应用于整个视图：

将视图v1创建为SELECT*FROM t；SELECT*FROM v1 FOR SYSTEM_TIME，截止时间戳'；2016-10-09 08：07：06'；；

使用系统版本控制的表将ROW_END列隐式添加到主键。虽然对于大多数用例来说，这通常不是问题，但是在从二进制日志中重新应用WRITE语句或在复制环境中重新应用WRITE语句时，这可能会导致问题，在复制环境中，主服务器重试从服务器上的SQL语句。

具体地说，这些写入在ROW_END列上包含一个值，该值包含最初进行写入时的时间戳。使用旧的系统版本控制列重新出现主键会因重复而引发错误。

要通过MariaDB复制缓解此问题，请将从服务器上的SECURE_TIMESTAMP系统变量设置为YES。设置后，从机在应用于行日志时使用自己的系统时钟，这意味着主机可以根据需要重试任意多次，而不会导致冲突。重试使用ROW_START和ROW_END列的新值生成新的历史记录行。

插入或删除行的时间点并不一定意味着更改在同一时刻变得可见。对于事务性表，可能已在长事务中插入行，并在插入数小时后变为可见。

对于某些应用程序-例如，在对一年前的数据进行数据分析时-这种区别并不重要。对于其他人-法医分析-这可能是至关重要的。

MariaDB支持事务精确的历史记录(仅适用于InnoDB存储引擎)，它允许新连接在指定的时间点执行SELECT操作时看到的数据完全一样-在该时间点之前插入但在该时间点之后提交的行将不会显示。

要使用事务精确的历史记录，InnoDB需要记住的不是时间戳，而是每行的事务标识符。这是通过将生成的列创建为BIGINT UNSIGNED，而不是TIMESTAMP(6)来实现的：

创建具有系统版本控制的表t(x int，start_trxid BIGINT unsign Generated Always as row start，end_trxid BIGINT unsign Generated Always as row end，Period for system_time(start_trxid，end_trxid))；

这些列必须显式指定，但可以将它们设置为不可见，以避免使SELECT*OUTPUT变得混乱。

使用事务精确历史记录时，可以选择在FOR SYSTEM_TIME子句中使用事务标识符：

这将显示与具有标识符12345的事务所看到的数据完全相同的数据。

当历史记录与当前数据存储在一起时，会增加表的大小，因此当前数据查询-表扫描和索引搜索-将花费更多时间，因为它们将需要跳过历史数据。如果该表上大多数查询只使用当前数据，那么单独存储历史记录可能会有意义，以减少版本控制带来的开销。

这是通过按SYSTEM_TIME对表进行分区来实现的。由于分区修剪优化，所有当前数据查询将仅访问一个分区，即存储当前数据的分区。

创建表t(X Int)，按system_time进行系统版本控制分区(分区p_hist History，分区p_cur current)；

在本例中，所有历史记录将存储在分区p_hist中，而所有当前数据将存储在分区p_cur中。该表必须正好有一个当前分区和至少一个历史分区。

按SYSTEM_TIME分区还支持自动分区轮换。可以按时间或大小旋转历史分区。此示例说明如何按大小旋转分区：

创建表t(Xint)，其中系统版本控制PARTITION BY SYSTEM_TIME LIMIT 100000(分区P0历史、分区P1历史、分区PCUR CURRENT)；

mariadb将开始将历史记录行写入分区p0，当其大小达到100000行时，mariadb将切换到分区p1。只有两个历史分区，所以当p1溢出时，MariaDB会发出警告，但会继续写入。

按照system_time间隔1周(分区p0历史、分区p1历史、分区p2历史、分区pcur current)创建系统版本分区的表t(X Int)；

这意味着表创建后第一周的历史记录将存储在P0中。第二周的历史记录-在p1中，以及以后的所有历史记录都将记录到p2中。用户可以在INFORMATION_SCHEMA.PARTITIONS表中看到每个分区的确切轮换时间。

用系统版本化PARTITION BY SYSTEM_TIME SUBPARTITION BY KEY(X)SUBPARTITIONS 4(PARTION PH HISTORY，PARTION PC CURRENT)创建表t(X Int)；

因为它存储所有历史记录，所以系统版本化表可能会随着时间的推移变得非常大。有许多选项可以减少空间并删除旧的历史记录。

用户可以从表中完全删除版本控制，然后重新添加，这将删除所有历史记录：

不过，这可能是一个相当耗时的操作，因为表将需要重新构建，可能需要重建两次(取决于存储引擎)。

第三个选项；您可以使用DELETE语句的变体来修剪历史记录：

为了保护历史记录的完整性，此语句需要特殊的DELETE HISTORY特权。

另一个MariaDB扩展只允许版本化表中列的子集。例如，如果您有一个包含应该版本化的用户信息的表，但是有一列(比方说)是经常递增且对版本不感兴趣的登录计数器，那么这是很有用的。通过声明此类列而不进行版本控制，可以将其从版本控制中排除。

还可以使用版本化声明列，这将自动使表版本化。下面的语句等同于上面的语句：

通过扩展系统版本化表，MariaDB10.4支持应用程序时间段表。时间段由两个时间列之间的范围定义。这些列必须是相同的时态数据类型，例如DATE或TIMESTAMP(虽然不是当前不支持的TIME)，并且具有相同的宽度。

使用时间段隐式地将这两列定义为NOT NULL。它还添加了一个约束来检查第一个值是否小于第二个值。约束不可见以显示CREATE TABLE语句。此约束的名称以时间段名称为前缀，以避免与其他约束冲突。

要创建具有时间段的表，请使用带有PERIOD TABLE选项的CREATE TABLE语句。

这将创建一个具有Time_Period周期的表，并使用一些基本的时态值填充该表。

ALTER TABLE语句现在支持在表中添加和删除时段的语法。要添加句点，请使用ADD PERIOD子句。

创建表测试。T2(id int主键，time_1时间戳(6)，time_2时间戳(6))；ALTER TABLE TEST。T2为Time_Period(Time_1，Time_2)添加周期；

ADD PERIOD和DROP PERIOD子句都包括处理期间是否已存在的选项：

ALTER TABLE TEST。T2如果Time_Period(Time_1，Time_2)不存在，则添加周期；更改表测试。如果Time_Period存在T2丢弃周期；

除了更新之外，您还可以删除某些时间段内的行。

当行周期与删除周期重叠时，它会收缩该行，从第一个或第二个行周期值中删除重叠。

当删除周期完全落在行周期内时，它会将该行拆分为两行。第一行从开始行周期运行到开始删除周期。第二个从结束删除周期运行到结束行周期。

截断检验。T1；插入到测试中。T1(日期_1，日期_2)值('；1999-01-01'；，'；2018-12-12'；)，('；1999-01-01'；，'；2017-01-01'；)，('；2017-01-01'；，'；2019-01-01'；)，('；1998-01-01'；，'；2018-12-12'；)，('；1997-01-01'；，'；2015-01-01'；，('；2016-01-01'；，'；2020-01-01'；)，('；2010-01-01'；，'；2015-01-01'；)；从测试中选择*。T1；+-+|名称|日期_1|日期_2|+-+|NULL|1999-01-01|2018-12-12||NULL|1999-01-01|2017-01-01||NULL|2017-01-01|2019年。01-01||NULL|1998-01-01|2018-12-12||NULL|1997-01-01|2015-01-01||NULL|2016-01-01|2020-01-01||NULL|2010-01-01|2015-01-01|+-+-+。

从测试中删除。T1对于DATE_PERIOD从'；2001-01-01&39；到'；2018-01-01&39；的部分，选择*from test。T1；+-+|名称|日期_1|日期_2|+-+|NULL|1999-01-01|2001-01-01||NULL|1999-01-01|2001-01-01||NULL|2018-01-01|2019年。01-01||NULL|1998-01-01|2001-01-01||NULL|1997-01-01|2001-01-01||NULL|2018-01-01|2020-01-01||NULL|2018-01-01|2018-12-12||NULL|2018-01-01|2018-12-12|+-+。

在这里，第二排的值从1999年到2017年已经缩小到1999年到2001年。第一行，范围从1999年到2018年，分为两行，1999到2000和2018-01到2018-12。从2010年到2015年的第五行被完全删除。

如果有DELETE或INSERT触发器，它的工作方式如下：删除任何匹配的行，然后插入一两行。如果记录被完全删除，则不会插入任何内容。

UPDATE语法现在支持UPDATE FOR PARTION，它根据行在某个范围中的出现情况修改行：

截断检验。T1；插入到测试中。T1(日期_1，日期_2)值('；1999-01-01'；，'；2018-12-12'；)，('；1999-01-01'；，'；2017-01-01'；)，('；2017-01-01'；，'；2019-01-01'；)，('；1998-01-01'；，'；2018-12-12'；)，('；1997-01-01'；，'；2015-01-01'；，('；2016-01-01'；，'；2020-01-01'；)，('；2010-01-01'；，'；2015-01-01'；)；

更新测试。T1对于DATE_PERIOD从'；2000-01-01'；到'；2018-01-01'；Set Name="；Generation Z"；；选择*from test。T1；+-+名称|DATE_1|DATE_2|+-+|Z世代|2000-01-01|2018-01-01||Z世代|2000-01-01|。2017-01-01||Z世代|2017-01-01|2018-01-01||Z世代|2000-01-01|2018-01-01||Z世代|2000-01-01|2015-01-01||Z世代|2016-01-01|2018-01-01||Z世代|2010-01-01|2015-01-01||NULL|1999-01-01|2000-01-01||NULL|2018-01-01|2018-12-12|NULL|1999。-01-01|2000-01-01||NULL|2018-01-01|2019-01-01||NULL|1998-01-01|2000-01-01||NULL|2018-01-01|2018-12-12||NULL|1997-01-01|2000-01-01||NULL|2018-01-01|2020-01-01|+。

MariaDB10.4还支持双临时表。这些表在系统和应用程序时间段级别都使用版本控制。要创建双时态表，请使用

创建表测试。T3(DATE_1 DATE，DATE_2 DATE，ROW_START TIMESTAMP(6)表示行开始不可见，ROW_END TIMESTAMP(6)表示行结束不可见，APPLICATION_TIME(DATE_1，DATE_2)表示周期，SYSTEM_TIME(ROW_START，ROW_END)表示)；

请注意，虽然此处的SYSTEM_TIME也是一个时间段，但它不能用于DELETE FOR PARTION或UPDATE FOR PARTION语句。

从test.t3中删除SYSTEM_TIME的一部分，从'；2000-01-01'；到'；2018年-01-01'；；错误42000：您的SQL语法有错误；请查看与您的MariaDB服务器版本对应的手册，了解正确的语法，以便在第1行使用SYSTEM_TIME从'；2000-01-01'；到'；2018年-01-01'；的正确语法。

描述：SQL：2011不允许对系统版本化表执行ALTER TABLE。当此变量设置为ERROR时，尝试更改系统版本化表将导致错误。当此变量设置为KEEP时，将允许ALTER TABLE，但历史记录将变得不正确-查询历史数据将显示新的表结构。例如，在向表中添加新列时，此模式仍然很有用。

说明：如果设置为特定的时间戳值，则隐式FOR SYSTEM_TIME AS OF子句将应用于所有查询。如果用户想要对特定时间点的历史进行许多查询，这是很有用的。将其设置为默认值以恢复默认行为。对DML没有影响，因此INSERT等查询。选择并替换..。选择需要明确声明截止日期。

mysqldump不从版本化表中读取历史行，因此不会备份历史数据。此外，时间戳的恢复也是不可能的，因为它们不能由a插入/a用户定义。

本网站上转载的内容是其各自所有者的财产，MariaDB事先不会对此内容进行审查。本内容所表达的观点、信息和意见不一定代表MariaDB或任何其他方的观点、信息和意见。