ThinkPHP6同步千萬級流水資料

ThinkPHP6定時任務同步千萬級流水資料

多資料來源配置

自定義指令

定時同步單次1000條

<?php
declare (strict_types = 1);

namespace app\command\SyncDtaTask;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Exception;
use think\facade\Db;

/**
 * 分批同步資料
 */
class DevLogSyncCmd extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('DevLogSyncCmd')
            ->setDescription('分批次同步流水資料');
    }

    protected function execute(Input $input, Output $output)
    {
        $tableList = [
            'dev_log',
        ];
        //$citys = Db::query('SELECT id FROM city');
        //同步城市固定寫死
        $citys = [37];
        foreach ($tableList as $item) {
            foreach ($citys as $city) {
                // 檢查今年的流水錶
                $todayTable = date('Y');
                $tblName = "{$item}_{$city}_{$todayTable}";
                $res = Db::execute("show tables like '{$tblName}'");
                if ($res === 0) {
                    $output->writeln('補建流水錶'.$item.'_'.$city['id'].'_'.$todayTable);
                    Db::execute("create table {$tblName} like {$item}");
                }

                // 獲取源資料庫和目標資料庫的連線
                $sourceDb = Db::connect("source1");
                $targetDb = Db::connect();

                //獲取待同步最大值,現從目的資料庫中獲取
                $maxSyncedIdResult = $targetDb->query("SELECT MAX(id) as max_id FROM {$tblName}");
                //如果沒獲取到,則取源資料庫的最大ID
                $maxSyncedId = $maxSyncedIdResult[0]['max_id'] ?? 0;
                if (!$maxSyncedId){
                    $maxSyncedIdResult = $sourceDb->query("SELECT MAX(id) as max_id FROM {$tblName}");
                    $maxSyncedId = ($maxSyncedIdResult[0]['max_id'] ?? 0) - 10000;
                }

                // 獲取源庫中的資料
                $sourceData = $sourceDb->query("SELECT * FROM {$tblName} WHERE id > {$maxSyncedId} ORDER BY id asc LIMIT 1000");
                //方案一: 直接使用插入方法
                //$targetDb->name($tblName)->insertAll($sourceData);

                //方案二: 需要處理欄位
                if (!empty($sourceData)) {
                    // 準備插入語句
                    $insertQuery = "INSERT INTO {$tblName} (" . implode(", ", array_keys($sourceData[0])).",flag" . ") VALUES ";

                    // 準備引數繫結的佔位符和值
                    $insertValues = [];
                    $params = [];
                    foreach ($sourceData as $index => $row) {
                        $rowValues = [];
                        foreach ($row as $key => $value) {
                            //$paramKey = ":{$key}_{$index}";
                            //$params[$paramKey] = $value;
                            $rowValues[] = !empty($value)?"'".$value."'":"null";
                        }
                        $insertValues[] = "(" . implode(", ", $rowValues) .",1" . ")";
                    }
                    //每一行的value值用逗號分割
                    $insertQuery .= implode(", ", $insertValues);

                    // 執行插入操作
                    $targetDb->execute($insertQuery);

                    $output->writeln("已同步 " . count($sourceData) . " 條記錄到 {$tblName}。");
                } else {
                    $output->writeln("沒有需要同步的資料。");
                }

            }
        }
    }
}

迴圈同步單次1000條

<?php
declare(strict_types=1);

namespace app\command\sycnTask;

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

class SyncAllDevLogCmd extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('SyncAllDevLogCmd')
            ->setDescription('迴圈同步全部流水資料');
    }

    protected function execute(Input $input, Output $output)
    {
        $tableList = [
            'dev_log',
        ];

        // 同步城市固定寫死
        $citys = [37];

        foreach ($tableList as $item) {
            foreach ($citys as $city) {
                // 檢查今天的表
                $todayTable = date('Y');
                $tblName = "{$item}_{$city}_{$todayTable}";

                $res = Db::execute("SHOW TABLES LIKE '{$tblName}'");
                if ($res === 0) {
                    $output->writeln("補建流水錶 {$tblName}");
                    Db::execute("CREATE TABLE {$tblName} LIKE {$item}");
                }

                // 獲取源資料庫和目標資料庫的連線
                $sourceDb = Db::connect("source");
                $targetDb = Db::connect();

                $totalSynced = 0;
                while (true) {
                    // 獲取目標表中已同步的最大ID,沒有預設為0
                    $maxSyncedIdResult = $targetDb->query("SELECT MAX(id) AS max_id FROM {$tblName}");
                    $maxSyncedId = $maxSyncedIdResult[0]['max_id'] ?? 0;

                    // 從源資料庫中獲取資料
                    $sourceData = $sourceDb->query("SELECT * FROM {$tblName} WHERE id > {$maxSyncedId} ORDER BY id ASC LIMIT 1000");

                    if (empty($sourceData)) {
                        // 如果沒有資料則退出迴圈
                        $output->writeln('沒有需要同步的資料。');
                        break;
                    }

                    // 插入資料到目標資料庫
                    $targetDb->name($tblName)->insertAll($sourceData);

                    $count = count($sourceData);
                    $totalSynced += $count;
                    $output->writeln("已同步 {$totalSynced} 條記錄到 {$tblName}。");
                }

                $output->writeln("同步完成，共同步 {$totalSynced} 條記錄。");
            }
        }
    }
}

設定定時任務