TP5数据库数据变动日志记录设计
根据网友的设计进行了部分调整: 用户分为管理员admin表和用户user表
记录操作表数据 增删改: insert/delete/update
<?php /** * OperateLog.php * description */ namespace app\service; use think\Db; use think\Log; /** * 操作日志 * Class OperateLog * @package app\service */ class OperateLogService { // 日志表主键值id protected $primaryid; // 操作表的id protected $tbid; // 操作表名 protected $tbname; // 操作表行字段名 protected $keys; // 操作表行字段值 protected $values; // 表前缀 protected $prefix; // 操作用户类型: 1管理员,admin_id | 2用户,user_id protected $user_type; protected $user_id = 0; protected $admin_id = 0; protected $ip; const LOGT1 = 'operatelog'; const LOGT2 = 'operatelog_content'; /** * OperateLog constructor. * @param int $userType 操作用户类型,1管理员 * @param int $uid 操作用户类型不为1时传入 */ public function __construct($userType = 1, $uid = 0) { if ($userType == 1) { $this->admin_id = session('admin_id'); } else { $this->user_id = $uid; } $this->user_type = $userType; $this->ip = ip2long(getIp()); $this->url = request()->url(); $this->prefix = config('database.prefix'); } /** * 参数说明 插入行为 * int $tbid 查询指定表的id * string $tbname 数据库表名 */ public function insert($tbid, $tbname) { try { //查询表注释 $tb = Db::query('show table status where name = "' . $this->prefix . $tbname . '"'); $priIdName = $this->getPrimaryKey($tbname); $data = [ 'type' => 1, 'create_time' => time(), 'tablename' => $tbname, 'tableid' => $tbid, 'tableid_name' => $priIdName, 'admin_id' => $this->admin_id, 'user_id' => $this->user_id, 'user_type' => $this->user_type, 'ip' => $this->ip, 'comment' => $tb['Comment'], 'url' => $this->url, ]; //插入日志主表 $returnid = Db::name(self::LOGT1)->insertGetId($data); //查询字段注释 $fields = Db::query('show full columns from ' . $this->prefix . $tbname); foreach ($fields as $v) { $commentArray[$v['Field']] = $v['Comment']; } //查询所有字段信息,插入日志从表 $rs = Db::name($tbname)->where($priIdName, $tbid)->find(); $keys = array_keys($rs); $values = array_values($rs); for ($i = 0; $i < count($keys); $i++) { Db::name(self::LOGT2)->insert([ 'operatelog_id' => $returnid, 'tbkey' => $keys[$i], 'tbvalue' => $values[$i], 'comment' => $commentArray[$keys[$i]] ]); } } catch (\Exception $e) { Log::error($e->getMessage()); } } /** * 更新行为前 * @param $tbid * @param $tbname */ public function updateStart($tbid, $tbname) { try { //查询表注释 $tb = Db::query('show table status where name = "' . $this->prefix . $tbname . '"'); $priIdName = $this->getPrimaryKey($tbname); $data = [ 'type' => 2, 'create_time' => time(), 'tablename' => $tbname, 'tableid' => $tbid, 'tableid_name' => $priIdName, 'admin_id' => $this->admin_id, 'user_id' => $this->user_id, 'user_type' => $this->user_type, 'ip' => $this->ip, 'comment' => $tb['Comment'], 'url' => $this->url, ]; //插入日志主表 $returnid = Db::name(self::LOGT1)->insertGetId($data); //查询修改前数据信息 $rs = Db::name($tbname)->where($priIdName, $tbid)->find(); $keys = array_keys($rs); $values = array_values($rs); $this->primaryid = $returnid; $this->tbid = $tbid; $this->tbname = $tbname; $this->keys = $keys; $this->values = $values; } catch (\Exception $e) { Log::error($e->getMessage()); } } /** * 更新行为后 */ public function updateEnd() { try { //查询表注释 $tb = Db::query('show table status where name = "' . $this->prefix . $this->tbname . '"'); $priIdName = $this->getPrimaryKey($this->tbname); foreach ($tb as $v) { $commentArray[$v['Field']] = $v['Comment']; } //查询修改后数据信息 $rs = Db::name($this->tbname)->where($priIdName, $this->tbid)->find(); $currentvalues = array_values($rs); //前后信息进行比较 for ($i = 0; $i < count($currentvalues); $i++) { if ($this->values[$i] !== $currentvalues[$i]) { Db::name(self::LOGT2)->insert([ 'operatelog_id' => $this->primaryid, 'tbkey' => $this->keys[$i], 'tbvalue' => $this->values[$i], 'currenttbvalue' => $currentvalues[$i], 'comment' => $commentArray[$this->keys[$i]] ]); } } } catch (\Exception $e) { Log::error($e->getMessage()); } } /** * 删除行为 * @param $tbid * @param $tbname */ public function delete($tbid, $tbname) { try { //查询表注释 $tb = Db::query('show table status where name = "' . $this->prefix . $this->tbname . '"'); $priIdName = $this->getPrimaryKey($this->tbname); $data = [ 'type' => 3, 'create_time' => time(), 'tablename' => $tbname, 'tableid' => $tbid, 'tableid_name' => $priIdName, 'admin_id' => $this->admin_id, 'user_id' => $this->user_id, 'user_type' => $this->user_type, 'ip' => $this->ip, 'comment' => $tb['Comment'], 'url' => $this->url, ]; //插入日志主表 $returnid = Db::name(self::LOGT1)->insertGetId($data); //查询字段注释 $fields = Db::query('show full columns from ' . $this->prefix . $tbname); foreach ($fields as $v) { $commentArray[$v['Field']] = $v['Comment']; } //查询修改前数据信息 $rs = Db::name($tbname)->where($priIdName, $tbid)->find(); $keys = array_keys($rs); $values = array_values($rs); for ($i = 0; $i < count($keys); $i++) { Db::name(self::LOGT2)->insert([ 'operatelog_id' => $returnid, 'tbkey' => $keys[$i], 'tbvalue' => $values[$i], 'comment' => $commentArray[$keys[$i]] ]); } } catch (\Exception $e) { Log::error($e->getMessage()); } } /** * 查询表主键id名 * @param $tbname * @return mixed */ protected function getPrimaryKey($tbname) { $priIdTb = Db::query("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='" . $this->prefix . $tbname . "' AND constraint_name='PRIMARY'"); return $priIdTb[0]['column_name']; } }
数据表设计:
CREATE TABLE `yed_operatelog` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned DEFAULT '0' COMMENT 'user表用户id', `admin_id` int(11) unsigned DEFAULT '0' COMMENT 'admin表主键:管理员id', `user_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '操作用户类型:1管理员,admin_id | 2用户,user_id', `type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '操作类型:1新增2修改3删除', `tableid` int(11) unsigned NOT NULL, `tablename` varchar(255) NOT NULL COMMENT '表名', `comment` varchar(255) DEFAULT NULL COMMENT '表的comment属性', `create_time` int(11) unsigned NOT NULL COMMENT '创建时间', `tableid_name` varchar(50) NOT NULL DEFAULT '' COMMENT '主键id名', `ip` int(11) DEFAULT NULL COMMENT '操作ip', `url` varchar(800) DEFAULT NULL COMMENT '操作url', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='操作日志表'; CREATE TABLE `yed_operatelog_content` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `operatelog_id` int(11) NOT NULL COMMENT 'operatelog表id', `tbkey` longtext NOT NULL COMMENT '字段名', `tbvalue` longtext COMMENT '改之前值', `currenttbvalue` longtext COMMENT '改之后值', `comment` varchar(255) DEFAULT NULL COMMENT '字段注释', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='操作日志从表';
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了