Laravel与MySQL中玩家时段可用性数据存储及查询方案咨询

一、数据库表设计 #

采用一对多关联结构，适配一个玩家对应多个可用时段的场景：

1. players表（已有字段）：

   • id (主键, 自增)
   • name (字符串, 玩家名称)
   • level (整数, 玩家等级)
   • email (字符串, 唯一索引, 邮箱)

2. player_availability表（新增存储时段）：

   • id (主键, 自增)
   • player_id (外键, 关联players.id)
   • day_of_week (tinyint, 取值1-7，对应周一到周日)
   • start_hour (tinyint, 起始小时，如9/11)
   • end_hour (tinyint, 结束小时，如11/13)
   • 联合唯一索引 (player_id, day_of_week, start_hour, end_hour)：防止同一玩家同一天重复提交同一时段

为什么用数字存储起止小时？
数字类型比直接存9h-11h这类字符串更灵活，后续支持区间查询（比如查所有10点左右可用的玩家）；如果坚持用字符串存储，可替换为time_slot字段（varchar类型），但查询灵活性会降低。

二、Laravel 迁移文件 #

1. Players表迁移（已有则跳过） #

Schema::create('players', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->integer('level');
    $table->string('email')->unique();
    $table->timestamps();
});

2. PlayerAvailability表迁移 #

Schema::create('player_availability', function (Blueprint $table) {
    $table->id();
    $table->foreignId('player_id')->constrained()->onDelete('cascade');
    $table->tinyInteger('day_of_week')->between(1,7);
    $table->tinyInteger('start_hour')->between(0,23);
    $table->tinyInteger('end_hour')->between(0,23);
    $table->unique(['player_id', 'day_of_week', 'start_hour', 'end_hour']);
    $table->timestamps();
});

三、模型与关联关系 #

1. Player模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Player extends Model
{
    protected $fillable = ['name', 'level', 'email'];

    public function availability(): HasMany
    {
        return $this->hasMany(PlayerAvailability::class);
    }
}

2. PlayerAvailability模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class PlayerAvailability extends Model
{
    protected $fillable = ['player_id', 'day_of_week', 'start_hour', 'end_hour'];

    public function player(): BelongsTo
    {
        return $this->belongsTo(Player::class);
    }
}

四、表单提交与数据存储 #

前端表单示例（Blade模板） #

<form method="POST" action="{{ route('players.store-availability') }}">
    @csrf
    <!-- 玩家基础信息 -->
    <div>
        <label>名称：</label>
        <input type="text" name="name" required>
    </div>
    <div>
        <label>等级：</label>
        <input type="number" name="level" required>
    </div>
    <div>
        <label>邮箱：</label>
        <input type="email" name="email" required>
    </div>

    <!-- 可用时段选择 -->
    <h3>选择可用时段</h3>
    @php
        $days = [1 => '周一', 2 => '周二', 3 => '周三', 4 => '周四', 5 => '周五', 6 => '周六', 7 => '周日'];
        $timeSlots = [
            ['start' => 9, 'end' => 11, 'label' => '9h-11h'],
            ['start' => 11, 'end' => 13, 'label' => '11h-13h'],
            ['start' => 13, 'end' => 15, 'label' => '13h-15h'],
        ];
    @endphp

    @foreach($days as $dayNum => $dayName)
        <div>
            <h4>{{ $dayName }}</h4>
            @foreach($timeSlots as $slot)
                <label>
                    <input type="checkbox" 
                           name="availability[{{ $dayNum }}][]" 
                           value="{{ $slot['start'] . '-' . $slot['end'] }}">
                    {{ $slot['label'] }}
                </label>
            @endforeach
        </div>
    @endforeach

    <button type="submit">提交</button>
</form>

后端控制器处理 #

namespace App\Http\Controllers;

use App\Models\Player;
use Illuminate\Http\Request;

class PlayerController extends Controller
{
    public function storeAvailability(Request $request)
    {
        // 验证数据
        $validated = $request->validate([
            'name' => 'required|string',
            'level' => 'required|integer',
            'email' => 'required|email|unique:players',
            'availability' => 'array',
            'availability.*' => 'array',
        ]);

        // 创建玩家
        $player = Player::create([
            'name' => $validated['name'],
            'level' => $validated['level'],
            'email' => $validated['email'],
        ]);

        // 处理可用时段数据
        $availabilityData = [];
        foreach ($request->input('availability', []) as $dayNum => $slots) {
            foreach ($slots as $slot) {
                list($start, $end) = explode('-', $slot);
                $availabilityData[] = [
                    'player_id' => $player->id,
                    'day_of_week' => $dayNum,
                    'start_hour' => (int)$start,
                    'end_hour' => (int)$end,
                ];
            }
        }

        // 批量插入时段
        if (!empty($availabilityData)) {
            $player->availability()->createMany($availabilityData);
        }

        return redirect()->back()->with('success', '信息提交成功');
    }
}

五、按条件查询玩家 #

示例1：查询周一（day_of_week=1）9h-11h可用的玩家 #

// Eloquent关联查询
$players = Player::whereHas('availability', function ($query) {
    $query->where('day_of_week', 1)
          ->where('start_hour', 9)
          ->where('end_hour', 11);
})->get();

// 或者关联表查询（适合复杂场景）
$players = Player::join('player_availability', 'players.id', '=', 'player_availability.player_id')
    ->where('player_availability.day_of_week', 1)
    ->where('player_availability.start_hour', 9)
    ->where('player_availability.end_hour', 11)
    ->select('players.*')
    ->distinct() // 去重，避免玩家多时段匹配时重复
    ->get();

示例2：查询周三11h-13h可用且等级≥10的玩家 #

$players = Player::whereHas('availability', function ($query) {
    $query->where('day_of_week', 3)
          ->where('start_hour', 11)
          ->where('end_hour', 13);
})->where('level', '>=', 10)
->get();

若用字符串存储time_slot的查询方式 #

如果表中用time_slot字段存9h-11h这类字符串，查询代码改为：

$players = Player::whereHas('availability', function ($query) {
    $query->where('day_of_week', 1)
          ->where('time_slot', '9h-11h');
})->get();

六、优化建议 #

• 给player_availability表添加索引：(day_of_week, start_hour, end_hour)，大幅提升查询速度
• 固定时段可存入配置文件或单独的time_slots表，避免硬编码
• 更新玩家时段时，建议先删除该玩家原有所有时段，再插入新数据，避免重复冲突

内容的提问来源于stack exchange，提问作者Leila Oliveira