闪回读取和备机读取

-- 启用闪回查询功能
SET persist tdsql_enable_stale_read = ON;

SELECT ... FROM <table_name> [ AS OF {TIMESTAMP timestamp_option | GTS gts_option} ]

-- 创建测试表
CREATE TABLE t1(a INT PRIMARY KEY, b INT);

SELECT sleep(3);

-- 记录初始时间点
SELECT NOW() INTO @time0;
SELECT ((UNIX_TIMESTAMP(@time0) << 24) | 0xFFFFFF) INTO @gts0;

SELECT sleep(3);

-- 插入测试数据
INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,1),(7,2),(8,3),(9,4),(10,5);

SELECT sleep(3);

-- 记录数据插入后时间点
SELECT NOW() INTO @time1;
SELECT ((UNIX_TIMESTAMP(@time1) << 24) | 0xFFFFFF) INTO @gts1;

SELECT sleep(3);

-- 更新数据
UPDATE t1 SET b=b*10;

-- 当前数据查询
SELECT * FROM t1 ORDER BY a;
a»b
1»10
2»20
3»30
4»40
5»50
6»10
7»20
8»30
9»40
10»■50

-- 闪回查询到初始状态（空表）
SELECT * FROM t1 ORDER BY a AS OF TIMESTAMP @time0;
a»b
SELECT * FROM t1 ORDER BY a AS OF GTS @gts0;
a»b

-- 闪回查询到插入数据后的状态
SELECT * FROM t1 ORDER BY a AS OF TIMESTAMP @time1;
a»b
1»1
2»2
3»3
4»4
5»5
6»1
7»2
8»3
9»4
10»■5


SELECT * FROM t1 ORDER BY a AS OF GTS @gts1;
a»b
1»1
2»2
3»3
4»4
5»5
6»1
7»2
8»3
9»4
10»■5

-- 0：Leader节点，1：Follower节点
SET persist tdsql_stale_read_role=1;

--查询当前数据
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
+----+-----+

set tdsql_read_staleness_t='-10'; #读取10秒前的数据

--插入新的数据
insert into t1 values (5,50);

--查看当前时间
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-02-16 17:57:50 |
+---------------------+

--查询当前数据
select * from t1; # 返回历史数据
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
+----+-----+

--等待一段时间
--查看当前时间
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-02-16 17:58:01 |
+---------------------+

--再次查询当前数据
select * from t1; # 返回新数据
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+

--查询当前数据
select * from t1;
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+

--插入新的数据
insert into t1 values (6,60);

--开启事务
begin;
select * from t1; # 返回历史数据
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+
select sum(num) as total_num from t1;
+-----------+
| total_num |
+-----------+
|       150 |
+-----------+
rollback;

--等待一段时间
--开启事务
begin;
select * from t1; # 返回新数据
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
|  6 |  60 |
+----+-----+
select sum(num) as total_num from t1;
+-----------+
| total_num |
+-----------+
|       210 |
+-----------+
rollback;