book_admin/application/admin/command/UpdateTest.php

<?php
/**
 * Created by: PhpStorm
 * User: lytian
 * Date: 2019/12/27
 * Time: 16:54
 */

namespace app\admin\command;


use think\Config;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\Db;
use think\Request;

class UpdateTest extends Command
{
    public function Configure()
    {
        $this->setName('UpdateTest')
            ->setDescription('清空consume表consume_info字段')
            ->addArgument('beginNum', Argument::REQUIRED, '数据库得开始编号')
            ->addArgument('endNum', Argument::REQUIRED, '数据库结束编号');
    }

    private $beginNum;
    private $endNum;

    public function execute(Input $input, Output $output)
    {
        Request::instance()->module('admin');
        $this->beginNum = $input->getArgument('beginNum');
        $this->endNum = $input->getArgument('endNum');
        $db = Config::get('db');
        $mod = $db['shard_num'];

        while ($this->beginNum <= $this->endNum) {
            //连接shard库
            $shardDbConfig = $this->getDbDeploy($this->beginNum, 'shard');
            $consumeTable = $shardDbConfig['table'].'.consume';
            unset($shardDbConfig['table']);
            //查询最小的id
            $sql = "SELECT min(id) as first_id, max(id) as last_id FROM {$consumeTable}";
            $row = Db::connect($shardDbConfig)->query($sql);
            $start = $row[0]['first_id'];
            $last = $row[0]['last_id'];
            $do = true;
            $userId = $this->beginNum + (int)$mod;
            model("Consume")
                ->setConnect($userId)
                ->update(['consume_info' => null], ['id' => $start]);
            while ($do) {
                $offset = $start + 10;
                $where = [
                    'id' => ['between', [$start+1, $offset]],
                    'consume_info' => ['exp', 'is not null'],
                ];
                model("Consume")
                    ->setConnect($userId)
                    ->update(['consume_info' => null], $where);
                if ($start >= $last) {
                    $do = false;
                }
                $start += 10;
            }
            $this->beginNum++;
        }
    }

    /**
     * 从库配置
     *
     * @param  $param
     * @param  string $deploy
     * @return array|mixed
     */
    private function getDbDeploy($param, $deploy = 'shard')
    {
        $db = Config::get('db');
        $mod = $param % $db[$deploy . '_num'];
        $mod = abs($mod);
        $list = explode(';', $db[$deploy . '_list']);

        foreach ($list as $item) {
            $con = explode(':', $item); // 0=0-191库编号 1=192.168.1.149主IP 2=3306主端口 3=192.168.1.150从IP 4=3306从端口
            if (count($con) >= 3) {
                $c = explode('-', $con[0]); //库编号  0开始 1结束
                if (count($c) >= 2) {
                    if ($c[0] <= $mod && $mod <= $c[1]) {
                        $database = Config::get('database');

                        if (count($con) >= 5) { //开启主从 & 带主从配置
                            $database['deploy'] = 1;
                            $database['rw_separate'] = true;
                            $database['hostname'] = $con[1] . ',' . $con[3]; //192.168.1.149,192.168.1.150
                            $database['hostport'] = $con[2] . ',' . $con[4]; //3306,3306
                        } else { //只有主库
                            $database['hostname'] = $con[1];
                            $database['hostport'] = $con[2];
                        }

                        $database['database'] = 'mysql';
                        $database['table'] = str_replace('$mod', $mod, $db[$deploy . '_database']);
                        return $database;
                    }
                }
            }
        }

        return [];
    }
}