NN2023021801/app/api/command/AutoSubmeter.php

<?php
declare (strict_types = 1);

namespace app\api\command;

use app\common\model\SubmeterModel;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Db;
use utils\RedisCache;

/**
 * 自动分表备份 by wes 每天凌晨 0点后运行，运行两次间隔10分钟
 * Class AutoSubmeter
 * @package app\api\command
 */
class AutoSubmeter extends Command
{
    protected $cacheTime = 86400; // 一天
    protected $tables = [
        // time 备份分表周期，save_time 数据保存时间天，time_field 时间字段
        'money_log'=>['time'=>2, 'save_time'=> 14,'time_field'=>'create_at'],
        'box_record'=>['time'=>2, 'save_time'=> 10,'time_field'=>'create_time'],
        'box_mid_handle'=>['time'=>2, 'save_time'=> 10,'time_field'=>'create_time'],
        'score_log'=>['time'=>2, 'save_time'=> 10,'time_field'=>'create_at'],
        'box_handle'=>['time'=>1, 'save_time'=> 0,'time_field'=>'create_time'],
    ];

    protected function configure()
    {
        $this->setName('auto_backup_submeter')
            ->setDescription('the auto_backup_submeter command');
    }

    /**
     * 处理分表
     * @param Input $input
     * @param Output $output
     * @return int
     */
    protected function execute(Input $input, Output $output)
    {
        if(date('H:i') > '08:00'){
            echo json_encode(['code'=>500,'msg'=>'不在运行时间段内，请于早上0点～8点执行','date'=>date('Y-m-d H:i:s')], 256)."\n";
            return false;
        }

        if(RedisCache::get("caches:submeter:lock")){
            echo json_encode(['code'=>500,'msg'=>'请不要频繁执行，稍后再试～','date'=>date('Y-m-d H:i:s')], 256)."\n";
            return false;
        }

        RedisCache::setnx("caches:submeter:lock", date('Y-m-d H:i:s'), rand(10, 20));
        Db::startTrans();
        try {
            $count = 0;
            $hasCount = 0;
            $cacheKey = "caches:submeter:backup:";
            foreach ($this->tables as $tableName => $item) {
                $time = isset($item['time']) ? $item['time'] : 0; // 备份周期
                $saveTime = isset($item['save_time']) ? $item['save_time'] : 0; // 保存时间
                $timeField = isset($item['time_field']) ? $item['time_field'] : 0; // 时间字段
                $time = $time ? $time : 2;

                // 验证是否备份
                $dateTime = strtotime(date('Y-m-d')) - 86400;
                if($tableName == 'box_handle'){
                    $index = 'all';
                    $newTableName = $tableName . '_' . $index;
                    $date = date('Y-m-d H:i:s', $dateTime);

                    if ($this->checkCatchByTime($tableName, $index, $time)) {
                        $hasCount++;
                        RedisCache::set($cacheKey . "{$newTableName}_error", ['data' => $item,'name'=>$newTableName, 'error' => '未到备份时间间隔'], $this->cacheTime);
                        continue;
                    }

                    // 复制数据
                    $prefix = env('database.prefix','db_');
                    $count1 = Db::name($tableName)->where('is_delete', 2)->whereNotIn('id',Db::name($newTableName)->column('id'))->count();
                    if(!$count1){
                        $hasCount++;
                        RedisCache::set($cacheKey . "{$newTableName}_error", ['sql'=>Db::name($tableName)->getLastSql(),'count'=>$count1,'data' => $item,'name'=>$newTableName, 'error' => '没有数据处理'], $this->cacheTime);
                        continue;
                    }

                    $count2 = Db::name($newTableName)->count();
                    $sql = "insert into `{$prefix}{$newTableName}` (select * from `{$prefix}{$tableName}` where `id` not in (select `id` from `{$prefix}{$newTableName}`) and `is_delete` = 2 and `create_time` < '".date('Y-m-d')."')";
                    $res = Db::query($sql);
                    $count3 = Db::name($newTableName)->count();
                    if (($count3 - $count2) < $count1) {
                        RedisCache::set($cacheKey."{$newTableName}_error", ['count'=>$count,'sql1'=>$sql,'msg'=> '复制分表数据错误','data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                        sr_throw("复制[{$newTableName}]分表数据错误");
                    }

                    // 清理原表多余数据
                    Db::name($tableName)->where(['is_delete'=>1])->where('update_time','<',$date)->delete();
                    $res = Db::name($tableName)->whereIn('id',Db::name($newTableName)->column('id'))->update(['is_delete'=>1,'update_time'=>date('Y-m-d H:i:s')]);
                    if(!$res){
                        RedisCache::set($cacheKey."{$newTableName}_error", ['count'=>$count,'sql'=>$sql,'sql1'=>Db::name($tableName)->getLastSql(),'msg'=> '清理备份分表数据错误','data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                        sr_throw("清理[{$tableName}]表旧数据错误");
                    }
                }else{
                    $index = date('Ymd', $dateTime);
                    $newTableName = $tableName . '_' . $index;
                    if ($this->checkCatchByTime($tableName, $index, $time)) {
                        $hasCount++;
                        RedisCache::set($cacheKey . "{$newTableName}_error", ['data' => $item,'name'=>$newTableName, 'error' => '未到备份时间间隔'], $this->cacheTime);
                        continue;
                    }

                    if ($this->checkCatch($tableName, $index)) {
                        $hasCount++;
                        RedisCache::set($cacheKey . "{$newTableName}_error", ['data' => $item,'name'=>$newTableName, 'error' => '已经处理过'], $this->cacheTime);
                        continue;
                    }

                    // 备份处理（1：复制表，2-清除日期间隔数据）
                    $prefix = env('database.prefix','db_');
                    $date = date('Y-m-d H:i:s', $dateTime);

                    // 复制表结构
                    $sql = "create table IF not exists `{$prefix}{$newTableName}` like `{$prefix}{$tableName}`";
                    Db::query($sql);

                    // 复制数据
                    $count1 = Db::name($tableName)->count();
                    $sql1 = "insert ignore `{$prefix}{$newTableName}` select * from `{$prefix}{$tableName}`";
                    Db::query($sql1);
                    if ($count1 != Db::name($newTableName)->count()) {
                        RedisCache::set($cacheKey."{$newTableName}_error", ['sql'=>$sql,'sql1'=>$sql1,'msg'=> '复制分表数据错误','data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                        sr_throw("复制备份[{$newTableName}]分表数据错误");
                    }

                    // 清理原表多余数据
                    $expiredRow = Db::name($tableName)
                        ->where($timeField,'<', $date)
                        ->where(function ($query) use($tableName){
                            if($tableName == 'money_log'){
                                $query->whereNotIn('type', [8]);
                            }
                        })
                        ->count();

                    $isDelete = Db::name($tableName)
                        ->where($timeField,'<', $date)
                        ->where(function ($query) use($tableName){
                            if($tableName == 'money_log'){
                                $query->whereNotIn('type', [8]);
                            }
                        })
                        ->delete();
                    if($expiredRow && !$isDelete){
                        RedisCache::set($cacheKey."{$newTableName}_error", ['sql'=>$sql,'msg'=> '清理备份分表数据错误','data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                        sr_throw("清理[{$tableName}]表旧数据错误");
                    }
                }


                $data = [
                    'table_name'=> $tableName,
                    'table_index'=> $index,
                    'update_time'=> date('Y-m-d H:i:s'),
                    'expired_at'=> $saveTime?date('Y-m-d H:i:s', time() + $saveTime * 24 * 3600):'',
                    'status'=>1
                ];
                if(!SubmeterModel::insertGetId($data)){
                    RedisCache::set($cacheKey."{$newTableName}_error", ['sql'=>$sql,'msg'=> '处理分表记录错误','log'=>$data,'data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                    sr_throw("处理[{$newTableName}]分表记录错误");
                }

                RedisCache::set($cacheKey."{$newTableName}_success", ['sql'=>$sql,'msg'=> '处理分表备份成功','log'=>$data,'data'=> $item,'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
                $count++;
            }

            Db::commit();
            echo json_encode(['code'=>200,'msg'=>"运行成功:共处理{$count}个表备份，{$hasCount}个已备份过",'date'=>date('Y-m-d H:i:s')],256)."\n";
        }catch (\Exception $exception){
            Db::rollback();
            RedisCache::clear("caches:submeter:lock");
            RedisCache::set("caches:submeter:error", ['msg'=> '运行错误：'.$exception->getMessage(),'trace'=>$exception->getTrace(),'date'=>date('Y-m-d H:i:s')], $this->cacheTime);
            echo json_encode(['code'=>500,'msg'=>'运行错误：'.$exception->getMessage(),'date'=>date('Y-m-d H:i:s')], 256)."\n";
        }

        return true;
    }

    /**
     * 验证是否备份分表过
     * @param $tableName 表名
     * @return bool|mixed
     */
    protected function checkCatch($tableName, $index)
    {
        $cacheKey = "caches:submeter:check:{$tableName}_{$index}";
        if(RedisCache::get($cacheKey)){
            return true;
        }

        $data = SubmeterModel::where(['table_name'=> $tableName,'table_index'=>$index,'status'=>1])->value('id');
        if($data){
            RedisCache::set($cacheKey, $data, rand(10, 20));
        }

        return $data;
    }

    /**
     * 验证是否备份分表过
     * @param $tableName 表名
     * @return bool|mixed
     */
    protected function checkCatchByTime($tableName, $index, $day)
    {
        $cacheKey = "caches:submeter:check:time_{$tableName}_{$index}_{$day}";
        if(RedisCache::get($cacheKey)){
//            return true;
        }

        $date = $day == 1? date('Y-m-d') : date('Y-m-d H:i:s', time() - $day * 24 * 3600);
        $data = SubmeterModel::where(['table_name'=> $tableName,'status'=>1])
            ->where('update_time','>=', $date)
            ->value('id');
var_dump(SubmeterModel::getLastSql());
        if($data){
            RedisCache::set($cacheKey, $data, rand(10, 20));
        }

        return $data;
    }
}