Poorman的MySQL表审计信息–第2部分

我非常喜欢深入研究我昨天在这篇文章中描述的解决方案，使用不可见列和触发器生成表审计信息。在本文中，我更关注使用JSON列存储审计信息的解决方案。当然还可以跟踪所有更改，不仅可以跟踪最后一次更改，还可以保留有关更改内容的信息。我想用一个例子来说明这一点：

当然，我们也可以搜索审计信息。例如，让我们搜索所有已修改的记录，以及旧名称或新名称是/是frederic的位置：

我们可以验证，确实该记录将frederic作为初始值。

触发器

与昨天相比，只有更新时的触发器有所不同：

DELIMITER $$
drop trigger demo2_update$$
create trigger demo2_update before update on demo2 
         for each row
         BEGIN
            declare modif json default null;            
            declare modif_arr json default null;           
            select json_object('updated_at', now(), 
                    'updated_by', session_user()) into modif;
            if old.id <> new.id then
               select json_merge_patch(modif, 
                      json_object('id', 
                        json_object("old", old.id, 
                                    "new", new.id)
                      )
               ) into modif;
            end if;
            if old.firstname <> new.firstname then
               select json_merge_patch(modif,
                      json_object('firstname', 
                        json_object("old", old.firstname, 
                                    "new", new.firstname)
                      )
               ) into modif;
            end if;
            if old.lastname <> new.lastname then
               select json_merge_patch(modif, 
                      json_object('lastname', 
                        json_object("old", old.lastname, 
                                    "new", new.lastname)
                      )
               ) into modif;
            end if;

            if json_extract(old.audit_info, "$.modifications") 
               is NULL then
              set new.audit_info=json_merge_patch(old.audit_info,
                  json_set(old.audit_info, 
                           "$.modifications", modif));              
            else                
              set new.audit_info=json_merge_patch(old.audit_info,
                  json_array_append(old.audit_info,
                           "$.modifications", modif));
            end if; 
END$$

DELIMITER ;
复制

所有魔法都包含在上面的触发器中😉

现在，最好有一个存储过程或脚本，可以添加不可见的JSON列，并为要保留审计信息的表的每一列自动生成这些触发器（并将其删除）。

享受MySQL！