TP5数据库数据变动日志记录设计
2023-09-27 14:21:28 时间
根据网友的设计进行了部分调整: 用户分为管理员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='操作日志从表';
相关文章
- 史上最简单的 MySQL 教程(二)「关系型数据库」
- 电脑上不安装Oracle时,C# 调用oracle数据库,Oracle客户工具
- MYSQL数据库-基础概念
- 数据库日志——mysql与Oracle的日志
- Sybase数据库截断和清空日志的方法
- MySQL 数据库之Binlog日志使用总结
- 图数据库neo4j添加算法包
- 装饰者模式---使用装饰者模式实现带日志记录功能的数据库命令执行类
- plsql链接数据库配置
- 清空数据库日志
- c3p0 连接数据库失败的问题
- 数据库三范式理解
- 数据库(3)数据库操作
- 在IDEA 、springboot中使用切面aop实现日志信息的记录到数据库
- 创建数据库文件-日志文件-次要数据库文件
- 这么简单的数据库面试题,第一眼竟无从下手。。
- 数据库错误日志惹的祸
- 数据库提示日志文件不可用
- HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询
- HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)
- 数据库层预克隆报无法创建日志文件
- Mysql——查看数据库,表占用磁盘大小