Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【MySQL】MariaDB10.3新特性--闪回查询

27 Sep 2019 »

MariaDB10.3新特性–闪回查询

System-Versioned表特性的引入,可以对表进行闪回。完成类似于Oracle的闪回查询。

修改已有表为System-Versioned

MariaDB [zsd]> alter table test with system versioning;
MariaDB [zsd]> select * from test;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张盛东    |
|    2 | 刘忠浩    |
+------+-----------+
2 rows in set (0.001 sec)

MariaDB [zsd]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-09-26 15:38:29 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [zsd]> insert into  test values(3,'胡轲');
Query OK, 1 row affected (0.004 sec)

对插入之前的时间点,做基于时间点的闪回查询,如下:

MariaDB [zsd]> select * from test for SYSTEM_TIME AS OF TIMESTAMP '2019-09-26 15:38:29 ';
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张盛东    |
|    2 | 刘忠浩    |
+------+-----------+
2 rows in set (0.000 sec)

MariaDB [zsd]> select * from test;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张盛东    |
|    2 | 刘忠浩    |
|    3 | 胡轲      |
+------+-----------+
3 rows in set (0.001 sec)