huahuibao/console/controllers/SaltController.php

<?php

namespace console\controllers;

use bizGhs\order\models\Order as GhsOrder;
use bizHd\purchase\models\Purchase;
use common\components\stringUtil;
use Yii;
use yii\console\Controller;
use yii\console\ExitCode;

/**
 * 补充历史订单 salt 字段
 *
 * 用法:
 *   php yii salt/fill
 *   php yii salt/fill --dryRun=1
 *   php yii salt/fill --batchSize=50 --sleepMs=200
 *   php yii salt/fill-ghs-order
 *   php yii salt/fill-cg
 */
class SaltController extends Controller
{
    /** @var int 每批处理条数 */
    public $batchSize = 100;

    /** @var int 每批之间的休眠毫秒数，降低白天执行时的数据库压力 */
    public $sleepMs = 100;

    /** @var int 最多处理条数，0 表示全部 */
    public $limit = 0;

    /** @var int 是否仅预览，1=是 */
    public $dryRun = 0;

    public function options($actionID)
    {
        return array_merge(parent::options($actionID), ['batchSize', 'sleepMs', 'limit', 'dryRun']);
    }

    /**
     * 补充 xhGhsOrder、xhCg 历史数据的 salt 字段
     */
    public function actionFill()
    {
        return $this->runFill([
            'xhGhsOrder' => GhsOrder::tableName(),
            'xhCg' => Purchase::tableName(),
        ]);
    }

    /**
     * 仅补充 xhGhsOrder
     */
    public function actionFillGhsOrder()
    {
        return $this->runFill([
            'xhGhsOrder' => GhsOrder::tableName(),
        ]);
    }

    /**
     * 仅补充 xhCg
     */
    public function actionFillCg()
    {
        return $this->runFill([
            'xhCg' => Purchase::tableName(),
        ]);
    }

    private function runFill(array $tables)
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);

        if ($this->batchSize < 1) {
            $this->stderr("batchSize 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }

        $exitCode = ExitCode::OK;
        foreach ($tables as $label => $tableName) {
            $code = $this->fillTable($label, $tableName);
            if ($code !== ExitCode::OK) {
                $exitCode = $code;
            }
        }

        return $exitCode;
    }

    private function fillTable($label, $tableName)
    {
        $db = Yii::$app->db;
        $remaining = (int)$db->createCommand(
            "SELECT COUNT(*) FROM {$tableName} WHERE salt = ''"
        )->queryScalar();

        $this->stdout("{$label}({$tableName}) 待补充: {$remaining}\n");
        if ($remaining === 0) {
            return ExitCode::OK;
        }

        $processed = 0;
        $maxProcess = $this->limit > 0 ? min($this->limit, $remaining) : $remaining;
        $lastId = 0;

        while ($processed < $maxProcess) {
            $batchLimit = min($this->batchSize, $maxProcess - $processed);
            $rows = $db->createCommand(
                "SELECT id FROM {$tableName} WHERE salt = '' AND id > :lastId ORDER BY id ASC LIMIT {$batchLimit}"
            )->bindValue(':lastId', $lastId)->queryColumn();

            if (empty($rows)) {
                break;
            }

            foreach ($rows as $id) {
                $id = (int)$id;
                $lastId = $id;

                if ($this->dryRun) {
                    $this->stdout("[dry-run] {$label} id={$id}\n");
                    $processed++;
                    continue;
                }

                $salt = stringUtil::charsShuffleLowerCase(10);
                $affected = $db->createCommand(
                    "UPDATE {$tableName} SET salt = :salt WHERE id = :id AND salt = ''"
                )->bindValues([
                    ':salt' => $salt,
                    ':id' => $id,
                ])->execute();

                if ($affected > 0) {
                    $processed++;
                }
            }

            $this->stdout("{$label} 已处理 {$processed}/{$maxProcess}\n");

            if ($this->sleepMs > 0 && $processed < $maxProcess) {
                usleep($this->sleepMs * 1000);
            }
        }

        $left = (int)$db->createCommand(
            "SELECT COUNT(*) FROM {$tableName} WHERE salt = ''"
        )->queryScalar();
        $this->stdout("{$label} 完成，本次处理 {$processed} 条，剩余 {$left} 条\n");

        return ExitCode::OK;
    }
}