mysql - 构建在每个 View 中显示不同的日志表的最佳方法是什么？

Question

我需要设计一个表的结构，用于存储项目管理网站的事件/操作日志。

问题是，这些日志的拼写会有所不同，具体取决于用户正在查看的内容

示例:

• 总体而言，操作可能会显示“John F. 删除了项目 #2881”
• 在单项页面上，会显示“John F. 删除了此项”
• 如果当前用户是 John F.，则会拼写“您删除了此项目”

我不确定是否应该将每种不同的可能性存储在表中，这听起来不是最佳方法。

Answer 1

对于任何类型的日志，您可以使用以下表结构

创建表日志 (id bigint NOT NULL AUTO_INCRMENT,auto_id bigint NOT NULL DEFAULT '0',表名 varchar(100) NOT NULL,updated_at 日期时间默认为 NULL，updated_by bigint NOT NULL DEFAULT '0',updated_by_name varchar(100) 默认为 NULL，主键(id)) ENGINE=InnoDB AUTO_INCRMENT=3870 默认字符集=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

然后创建另一个表来准确记录哪些列被更新。

创建表logs_entries (id bigint NOT NULL AUTO_INCRMENT,log_id bigint NOT NULL DEFAULT '0',field_name varchar(100) NOT NULL,old_value 文本，new_value 文本，主键(id)，KEY log_id (log_id),约束 logs_entries_ibfk_1 外键 (log_id) 引用 logs (id) 删除级联 更新级联) ENGINE=InnoDB AUTO_INCRMENT=7212 默认字符集=utf8mb3

现在您的表格数据如下所示

现在创建一个数据库 View 以通过简单查询轻松获取数据

分隔符$$

ALTER ALGORITHM=UNDEFINED SQL 安全定义器 View view_logs_entries AS选择le.id AS id,le.log_id AS log_id,le.field_name AS field_name,le.old_value AS old_value,le.new_value AS new_value,l.auto_id AS auto_id,l.table_name AS table_name,l.updated_at AS updated_at，l.updated_by AS updated_by，l.updated_by_name AS updated_by_nameFROM (logs_entries 文件LEFT JOIN 日志 lON ((le.log_id = l.id)))$$

分隔符；

创建数据库 View 后，您的数据将如下所示，因此现在您可以轻松查询项目的任何日志

你一定注意到了，我在日志表中添加了updated_by和updated_by_name列，现在有两种方法来填充这个updated_by_name列

1. 您可以在每个日志条目上编写查询来获取用户名并将其存储(不推荐)
2. 您可以创建数据库触发器来填充此列(推荐)

您可以像这样创建数据库触发器，只要在数据库中添加日志条目，它就会自动插入用户名。

分隔符$$

使用YOUR_DATABASE_NAME$$

创建在插入 logs 之前触发 logs_before_insert对于每一行开始SET new.updated_by_name= (SELECT fullname FROM users WHERE user_id = new.updated_by);结尾;$$

分隔符；

完成所有这些操作后，每当您在任何数据库表中进行更改时，您都可以在日志表中插入条目。就我而言，我有 PHP-CodeIgniter 项目，我在模型文件中这样做了，在这里我正在更新数据库的患者表

public function update($id,$data)
{
    // Log this activity
    $auto_id = $id;
    $table_name = 'patients';
    $updated_by = @$data['updated_by'];
    $new_record = $data;
    $old_record = $this->db->where('id',$auto_id)->get($table_name)->row();

    $data['updated_at'] = date('Y-m-d H:i:s');
    $this->db->where('id', $id);
    $return = $this->db->update($table_name,$data);
    //my_var_dump($this->db->last_query());

    if($updated_by)
    {
        $this->log_model->set_log($auto_id,$table_name,$updated_by,$new_record,$old_record);
    }

    return $return;
}

set_log函数代码检查哪些字段实际发生了变化

public function set_log($auto_id,$table_name,$updated_by,$new_record,$old_record)
{
    $entries = [];

    foreach ($new_record as $key => $value)
    {
        if($old_record->$key != $new_record[$key])
        {
            $entries[$key]['old_value'] = $old_record->$key;
            $entries[$key]['new_value'] = $new_record[$key];
        }
    }

    if(count($entries))
    {
        $data['auto_id'] = $auto_id;
        $data['table_name'] = $table_name;
        $data['updated_by'] = $updated_by;
        $this->insert($data,$entries);
    }
}

插入日志函数如下所示

public function insert($data,$entries)
{
    $data['updated_at'] = date('Y-m-d H:i:s');
    if($this->db->insert('logs', $data))
    {
        $id = $this->db->insert_id();

        foreach ($entries as $key => $value)
        {
            $entry['log_id'] = $id;
            $entry['field_name'] = $key;
            $entry['old_value'] = $value['old_value'];
            $entry['new_value'] = $value['new_value'];

            $this->db->insert('logs_entries',$entry);
        }

        return $id;
    }
    return false;
}