从历史表跟踪记录的MySQL查询

Question

我需要了解mysql查询中的逻辑，以跟踪所有更改的历史记录。下面的例子将解释我的期望。

account表

+------+---------+---------------------+---------------------+
| id   | emailid | created_date        | modified_date       |
+------+---------+---------------------+---------------------+
|    1 | abc     | 2020-03-20 00:00:00 | 2020-07-10 00:00:00 |
+------+---------+---------------------+---------------------+

account_history表

+------+---------+---------------------+
| id   | emailid | modified_date       |
+------+---------+---------------------+
|    1 | def     | 2020-04-03 00:00:00 |
|    1 | ghi     | 2020-05-05 00:00:00 |
|    1 | lmn     | 2020-06-05 00:00:00 |
|    1 | opq     | 2020-07-01 00:00:00 |
|    1 | opq     | 2020-07-03 00:00:00 |
|    1 | qrs     | 2020-07-10 00:00:00 |
+------+---------+---------------------+

预期结果

+------+-----------+----------+----------+-------------------+---------------------+
| id   | parameter | oldvalue | newvalue | event             | event_datetime      |
+------+-----------+----------+----------+-------------------+---------------------+
|    1 | emailid   | NULL     | def      | New Entry         | 2020-03-20 00:00:00 |
|    1 | emailid   | def      | ghi      | Change in account | 2020-04-03 00:00:00 |
|    1 | emailid   | ghi      | lmn      | Change in account | 2020-05-05 00:00:00 |
|    1 | emailid   | lmn      | opq      | Change in account | 2020-06-05 00:00:00 |
|    1 | emailid   | opq      | qrs      | Change in account | 2020-07-03 00:00:00 |
|    1 | emailid   | qrs      | abc      | Change in account | 2020-07-10 00:00:00 |
+------+-----------+----------+----------+-------------------+---------------------+

我有主表名为account，历史表名为account_history。帐户中的每个更改都将在其历史表中跟踪，当前值将存储在历史表中。我期望我的产出是这样的。如果没有发生任何变化，就不需要跟踪它。我每天都有追踪的逻辑。但我想追踪过去的数据。每天追踪基础查询，

mysql>  select id,'emailid',acch.emailid as oldvalue,acc.emailid as newvalue,'Change in account',acc.modified_date from account acc join account_history acch using(id) where acc.emailid!=acch.emailid and acc.modified_date=acch.modified_date;
+------+---------+----------+----------+-------------------+---------------------+
| id   | emailid | oldvalue | newvalue | Change in account | modified_date       |
+------+---------+----------+----------+-------------------+---------------------+
|    1 | emailid | qrs      | abc      | Change in account | 2020-07-10 00:00:00 |
+------+---------+----------+----------+-------------------+---------------------+
1 row in set (0.00 sec)

对过去的数据进行跟踪时感到困难。帮我解决这个问题。

mysql Ver 14.14远端5.7.23

甚至超过1或2个查询，以达成解决方案将是有帮助的。通过创建任何中间表并找到解决方案将是有帮助的。

Answer 1

假设我们没有优化表结构的能力。因此，主要的想法是做以下工作：

将初始数据转换为modifications.

• Join的顺序列表--这个列表本身就是新值记录的序列号，它的下一个序列号是相对较旧的值记录的序列号。

为了简单起见，MySQL 5.7的解决方案被描述为两阶段查询。这在MySQL 8中可以做得更简单。而且，这不是优雅的解决方案，而是简单的解释方法。

步骤1.顺序列表。

-- The variable to create sequential number
SET @rn := 0;

SELECT @rn := @rn + 1 as rn,
       ah.*
FROM (
    -- Add history record for creating new entry
    SELECT id, null as emailid, 'New Entry' as event, created_date as modified_date
    FROM account

    UNION
    -- Add intermediate history records
    -- Need grouping to filter identical values (like 'opq')
    SELECT id, emailid, 'Change in account' as event, min(modified_date) as modified_date
    FROM account_history GROUP BY id, emailid

    UNION

    -- Add history record for current value
    SELECT id, emailid, 'Change in account' as event, modified_date
    FROM account
) as ah,
  (SELECT @row := 0) as r

ORDER BY id, modified_date;

此查询将生成以下列表：

rn  id  emailid  event              modified_date
 1   1  NULL     New Entry          2020-03-20 00:00:00
 2   1  def      Change in account  2020-04-03 00:00:00
 3   1  ghi      Change in account  2020-05-05 00:00:00
 4   1  lmn      Change in account  2020-06-05 00:00:00
 5   1  opq      Change in account  2020-07-01 00:00:00
 6   1  qrs      Change in account  2020-07-10 00:00:00
 7   1  abc      Change in account  2020-07-10 00:00:00

步骤2.自联接顺序列表.

假设上面的列表名为subquery (子查询、视图、临时表等)。加入它的方式如下：

SELECT s1.id,
       'emailid' as emailid,
       s1.emailid as oldvalue,
       s2.emailid as newvalue,
       s1.event,
       s1.modified_date
FROM subquery as s1
    JOIN subquery as s2
        ON s2.id = s1.id AND s2.rn = s1.rn + 1