MariaDB10.3新特性--System-Versioned表(MariaDB版的闪回查询)

终于等到你，System-Versioned表特性的引入，对于启用了该特性的表，数据的误删再也不怕了。相当于Oracle的闪回查询，可以查询任意时间点的数据。下面举个小例子：

–创建表，with system versioning

MySQL [(none)]> use test;
Database changed
MySQL [test]> drop table student;
Query OK, 0 rows affected (0.435 sec)

MySQL [test]> create table student(id int,name varchar(20),age tinyint) with system versioning;
Query OK, 0 rows affected (0.010 sec)

MySQL [test]> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                            
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING |
+---------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)      

–记录时间，插入记录

MySQL [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-05-21 13:43:31 |
+---------------------+
1 row in set (0.000 sec)

MySQL [test]> insert into student values(1,'name',26);
Query OK, 1 row affected (0.000 sec)

MySQL [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-05-21 13:44:07 |
+---------------------+
1 row in set (0.000 sec)
 
MySQL [test]> update student set age=27;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

MySQL [test]> select * from student;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | name |   27 |
+------+------+------+
1 row in set (0.001 sec)      

–再次查询插入数据之前的表里的数据

MySQL [test]> SELECT * FROM student FOR SYSTEM_TIME AS OF TIMESTAMP'2018-05-21 13:43:31';
Empty set (0.001 sec) --可以看到是没有数据的。      

–查看更新之前的数据

MySQL [test]> SELECT * FROM student FOR SYSTEM_TIME AS OF TIMESTAMP'2018-05-21 13:44:07';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | name |   26 |  --可以看到是更新之前的记录，age=26.
+------+------+------+
1 row in set (0.001 sec)      

–查看现在的记录

MySQL [test]> select * from student;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | name |   27 |
+------+------+------+
1 rows in set (0.000 sec)