问题:历史轨迹的业务中每次编辑都会存储,查询需要过滤相邻没有变更的数据
-- 数据操作过程(简略描述)
主键 操作 数据 MD5
第1次:100 新增 'a' '9'
第2次:200 编辑 'b' '10'
第3次:300 编辑 'b' '10'
第4次:400 编辑 'c' '11'
第5次:500 编辑 'b' '10'
第6次:600 编辑 'd' '12'
-- 期望结果:
-- operate_time operator res_type action res_id md5 last_md5 new_value old_value
-- 100 系统管理员 2 新增 335891161551028224 9 0 a null
-- 200 系统管理员 2 编辑 335891161551028224 10 11 b a
-- 400 系统管理员 2 编辑 335891161551028224 11 10 c b
-- 500 系统管理员 2 编辑 335891161551028224 10 11 b c
-- 600 系统管理员 2 编辑 335891161551028224 12 11 d c
测试数据准备
-- table
CREATE TABLE id_t_history_trace (
id bigint NOT NULL COMMENT '主键',
operate_time bigint DEFAULT NULL COMMENT '操作时间',
operator varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '操作员名',
res_type tinyint DEFAULT NULL COMMENT '资源类型,1:用户,2:机构 3: 设备',
action int DEFAULT NULL COMMENT '历史轨迹动作,100, "添加",101, "编辑",102, "注销"',
old_value varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '老数据',
new_value varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新数据',
md5 varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 'md5值',
tenant_id bigint DEFAULT NULL COMMENT '租户id',
res_id bigint DEFAULT NULL COMMENT '资源ID',
PRIMARY KEY (id) USING BTREE,
KEY id_t_history_trace_res_id_index (res_id) USING BTREE COMMENT '查询是按照"res_id"列进行筛选和分组的,因此在该列上创建索引可以减少扫描的行数,从而提高查询性能。',
KEY id_t_history_trace_operate_time_index (operate_time) USING BTREE COMMENT '查询需要按照"operate_time"列进行排序,因此在该列上创建索引可以提高排序性能并减少内存使用。'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- ALTER TABLE id_t_history_trace
-- ADD INDEX id_t_history_trace_res_id_index(res_id) USING BTREE COMMENT '查询是按照 res_id 列进行筛选和分组的,因此在该列上创建索引可以减少扫描的行数,从而提高查询性能',
-- ADD INDEX id_t_history_trace_operate_time_index(operate_time) USING BTREE COMMENT '查询需要按照 operate_time 列进行排序,因此在该列上创建索引可以提高排序性能并减少内存使用'
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (100, 100, '系统管理员', 2, 100, NULL, 'a', '9', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (200, 200, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (300, 300, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (400, 400, '系统管理员', 2, 101, NULL, 'c', '11', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (500, 500, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (600, 600, '系统管理员', 2, 102, NULL, 'd', '12', 1, 335891161551028224);
lag()函数处理
-
思路参考了:https://blog.csdn.net/qq_41404557/article/details/126298624
-- lag()函数:LAG()函数是标准SQL函数,MySQL 8.0及以上版本支持,Oracle,SQL Server,PostgreSQL,SQLite3.25.0及以上版本支持
-- LAG()函数的语法:LAG(column, offset, default) OVER (ORDER BY column)
-- 其中,column是要查找的列,offset是要向前查找的行数,default是当没有前一行时返回的值。ORDER BY子句指定了要对哪一列进行排序。
-- LAG()函数可以支持多列。在使用LAG()函数时,我们可以使用PARTITION BY子句来指定多列作为分组依据。
-- 根据条件查询去重上一条重复数据,利用lag()函数增加一列“上一个数据”,利用本次访问的页面不等于上一个访问的页面作为条件,取出要求的结果,思路与第一个思路一致。
-- 注意:lag( h.md5, 1, 0 ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) 中ORDER By的顺序,ASC为获取前一行数据,DESC为获取后一行数据
-- 使用中发现人大金仓数据量lag函数不支持LAG(column, offset, default),支持LAG(column),即去掉lag函数后两个参数,得到结果也是一致的
-- Mysql经过实测lag可以不要后两个参数也是可以的
SELECT
history.operate_time,
history.operator,
history.res_type,
history.action,
history.res_id,
history.md5,
history.last_md5,
history.new_value,
history.old_value
FROM
(
SELECT
h.operate_time,
h.operator,
h.res_type,
h.action,
h.new_value,
h.res_id,
h.tenant_id,
h.md5,
lag( h.md5, 1, 0 ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) AS last_md5,
lag( h.new_value, 1, NULL ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) AS old_value
FROM
id_t_history_trace h
WHERE
h.res_id = 335891161551028224
ORDER BY
h.operate_time
) history
WHERE
history.res_id = 335891161551028224
AND history.last_md5 IS NULL
OR history.md5 != history.last_md5
ORDER BY
history.operate_time
LIMIT 0,
101;
关联子查询处理
-- 使用带有关联子查询的子查询的示例查询:
-- 这个查询使用了带有关联子查询的子查询,将前一行的"new_value"值作为子查询的结果列。
-- 注意,这个查询中使用了NOT EXISTS子查询来检查是否有前一行。如果没有前一行,则子查询的结果为NULL,因此需要在SELECT语句中使用COALESCE函数将其转换为适当的值。
SELECT
current.operate_time,
current.operator,
current.res_type,
current.action,
current.res_id,
current.md5,
current.new_value,
(
SELECT
previous.new_value
FROM
id_t_history_trace previous
WHERE
previous.res_id = current.res_id
AND previous.operate_time < current.operate_time
ORDER BY
previous.operate_time DESC
LIMIT
1
) AS old_value
FROM
id_t_history_trace current
WHERE
current.res_id = 335891161551028224
AND (
NOT EXISTS (
SELECT
*
FROM
id_t_history_trace previous
WHERE
previous.res_id = current.res_id
AND previous.operate_time < current.operate_time
) OR current.md5 != (
SELECT
previous.md5
FROM
id_t_history_trace previous
WHERE
previous.res_id = current.res_id
AND previous.operate_time < current.operate_time
ORDER BY
previous.operate_time DESC
LIMIT
1
)
)
ORDER BY
current.operate_time
LIMIT
0, 101;
业务层面处理
-- 原来业务处理,每次查询多查询一条,后端处理旧数据,数据少一条,数据为3条,缺失1条数据
SELECT
history.operate_time,
history.operator,
history.res_type,
history.action,
history.new_value,
history.res_id,
history.tenant_id,
history.md5
FROM
id_t_history_trace history
INNER JOIN ( SELECT max( id ) AS id FROM id_t_history_trace WHERE res_type = 2 AND res_id=335891161551028224
GROUP BY md5 ) temp ON history.id = temp.id
ORDER BY
history.operate_time DESC
LIMIT 0,
101;
-- 这里其实可以优化:每次入库查询上次编辑内容,进行对比,没有变化,则不进行入库(利用一次查询换取存储空间和查询简化)