cxinv/app/command/copyCxData.php

<?php

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Db;
use think\helper\Str;


//将wsm_sale_caixiao和wsm_cgd_caixiao的数据复制到cfp_caixiao_data
class copyCxData extends command
{

    private $interval = 500 * 60;//执行间隔，单位：分（每次同步前5分钟的数据）

    protected function configure()
    {
        parent::configure(); // TODO: Change the autogenerated stub

        $this->setName('copyCxData')->setDescription('复制采销数据到中间表');

    }

    protected function execute(Input $input, Output $output)
    {
//        $start = microtime(true);

        $date = date('Y-m-d H:i:s');

        //先处理wsm_sale_caixiao数据
        $sale = Db::connect('mysql_wsm')
            ->name('sale_caixiao')
            ->field(true)
            ->limit(1)
            ->whereBetween('updatetime', [date('Y-m-d H:i:s', time() - $this->interval), $date])
            ->cursor();

        $insert_data = "INSERT INTO `cfp_caixiao_data` (`order_type`, `data`, `uniqkey`, `status`, `addtime`) VALUES ";

        $is_insert = false;

        foreach ($sale as $value) {
            $value['cat_name'] = json_decode($value['cat_name'], true);
            $insert_data .= "(1,'" . json_encode($value, JSON_UNESCAPED_UNICODE) . "', '" . Str::random(32, 5) . "',0,'" . $date . "'),";
            $is_insert = true;
        }

        //再处理 wsm_cgd_caixiao 数据
        $cgd = Db::connect('mysql_wsm')
            ->name('cgd_caixiao')
            ->field(true)
            ->whereBetween('updatetime', [date('Y-m-d H:i:s', time() - $this->interval * 60), $date])
            ->cursor();

        foreach ($cgd as $val) {
            $val['cat_name'] = json_decode($val['cat_name'], true);
            $insert_data .= "(2,'" . json_encode($val, JSON_UNESCAPED_UNICODE) . "', '" . Str::random(32, 5) . "',0,'" . $date . "'),";
            $is_insert = true;
        }

        if ($is_insert) {

            //把最后一个,换成;
            $insert_data = substr($insert_data, 0, -1) . ';';

            $res = Db::execute($insert_data);

            $output->writeln('本次共处理' . $res . '条数据');//原生sql，耗时1.9961631298065s,二维数组insertAll，耗时23.434303045273
        }

//        $end = microtime(true);

//        $output->writeln('本次耗时：' . ($end - $start));

//        return parent::execute($input, $output); // TODO: Change the autogenerated stub
    }

}