Laravel中高效处理百万级数据定时重置字段的策略与实践

php artisan make:command ResetDailyStatusCommand

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use App\Models\User; // 假设你的用户模型是User

class ResetDailyStatusCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'daily:reset-status';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Reset the daily column for all users to 1.';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info('Starting daily status reset...');

        try {
            // 使用数据库事务确保更新的原子性
            DB::transaction(function () {
                // 对于百万级记录，建议使用原始SQL或分批更新
                // 但对于简单的字段重置，单条UPDATE语句通常效率很高
                User::query()->update(['daily' => 1]);
                // 或者使用DB facade
                // DB::table('users')->update(['daily' => 1]);
            });

            $this->info('Daily status reset completed successfully.');
        } catch (\Exception $e) {
            $this->error('Failed to reset daily status: ' . $e->getMessage());
            // 记录错误或发送通知
        }
    }
}

<?php

namespace App\Console;

use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * Define the application's command schedule.
     */
    protected function schedule(Schedule $schedule): void
    {
        // 每天午夜12点运行一次重置命令
        $schedule->command('daily:reset-status')->dailyAt('00:00');
        // 或者更精确到分钟
        // $schedule->command('daily:reset-status')->cron('0 0 * * *');
    }

    /**
     * Register the commands for the application.
     */
    protected function commands(): void
    {
        $this->load(__DIR__.'/Commands');

        require base_path('routes/console.php');
    }
}

* * * * * cd /path-to-your-project && php artisan schedule:run >> /dev/null 2>&1

DB::transaction(function () {
    // 仅在非常严格的场景下考虑，通常默认隔离级别已足够
    // DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
    User::query()->update(['daily' => 1]);
});

Schema::create('users', function (Blueprint $table) {
    // ... 其他字段
    $table->unsignedTinyInteger('daily')->default('1');
    $table->timestamp('last_daily_activity_at')->nullable(); // 记录上次每日活动的时间
    // ...
});

use Carbon\Carbon;

class DailyRewardController extends Controller
{
    public function claimDailyReward(Request $request)
    {
        $user = $request->user();

        // 检查是否需要重置daily状态
        $this->resetUserDailyStatusIfNeeded($user);

        if ($user->daily === 1) {
            // 用户可以进行点击操作
            // ... 执行奖励逻辑 ...

            // 更新daily状态为已使用
            $user->daily = 0;
            $user->last_daily_activity_at = Carbon::now(); // 记录本次活动时间
            $user->save();

            return response()->json(['message' => '奖励领取成功！']);
        } else {
            return response()->json(['message' => '今日奖励已领取，请明日再来。'], 403);
        }
    }

    protected function resetUserDailyStatusIfNeeded(User $user)
    {
        // 如果daily状态为0 (已使用)，并且上次活动时间是昨天或更早
        if ($user->daily === 0 && $user->last_daily_activity_at && $user->last_daily_activity_at->isBefore(Carbon::today())) {
            $user->daily = 1; // 重置为可用
            $user->save(); // 保存重置后的状态
        }
    }
}