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='操作日志从表';
