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)
    {
        set_time_limit(0);
        if (date('H:i') > '12: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;

                $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, 'count1' => $count1, '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' => '清理备份分表数据错误', 'count' => $expiredRow, '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;
    }
}