Laravel中批量递增subscriptions表多行used_tokens字段的实现问题

逻辑背景 #

• subscriptions表存储用户订阅记录，每个用户可有多条记录，每条包含user_id、tokens（总额度）、used_tokens（已使用额度）字段
• products表中的产品需要消耗指定数量的Token（例如产品A需2个Token）
• 需按顺序从用户有效订阅记录中扣除Token：比如用户有两条订阅，一条剩余1个可用Token（tokens=1，used_tokens=0），另一条剩余5个可用Token（tokens=5，used_tokens=0），扣除2个Token时，先将第一条used_tokens增至1（用完剩余），再从第二条扣除1个，最终第二条used_tokens=1
• 目标是更新subscriptions表的used_tokens字段，标记已使用的Token

问题描述 #

无法实现对subscriptions表中符合条件的记录进行used_tokens的递增更新，需补充更新逻辑以达到预期的Token扣除效果。

现有代码 #

已获取目标产品和用户的有效订阅记录，代码如下：

$product = Product::findOrFail($request->input('product_id')); // 获取产品信息
$required_tokens = $product->required_tokens; // 获取产品所需Token数量（例如2）
$subscriptions = Subscription::where('user_id', $user->id)
    ->where('isPaid', true)
    ->where('used_tokens', '<', 'tokens')
    ->orderBy('created_at') // 建议按创建时间排序，确保按顺序扣除
    ->get(); // 获取用户的有效订阅记录

解决方案 #

通过遍历订阅记录，逐条计算可扣除的Token数量，更新used_tokens字段，直到所需Token全部扣除完成：

$remaining_tokens = $required_tokens;

foreach ($subscriptions as $subscription) {
    if ($remaining_tokens <= 0) {
        break;
    }

    // 计算当前订阅的剩余可用Token
    $available_tokens = $subscription->tokens - $subscription->used_tokens;
    if ($available_tokens <= 0) {
        continue;
    }

    // 取剩余需要的Token和当前可用Token的较小值作为本次扣除量
    $deduct = min($remaining_tokens, $available_tokens);

    // 更新并保存记录
    $subscription->used_tokens += $deduct;
    $subscription->save();

    // 剩余需扣除Token数量递减
    $remaining_tokens -= $deduct;
}

// 可选：若剩余Token不为0，说明用户可用Token不足，抛出异常或提示
if ($remaining_tokens > 0) {
    throw new \Exception("用户可用Token不足，无法兑换产品");
}

优化建议 #

• 避免并发冲突：查询订阅记录时添加lockForUpdate()开启行级锁，防止同一用户的订阅记录被同时修改：

$subscriptions = Subscription::where('user_id', $user->id)
    ->where('isPaid', true)
    ->where('used_tokens', '<', 'tokens')
    ->orderBy('created_at')
    ->lockForUpdate()
    ->get();

• 批量SQL更新（适合数据量较大场景）：用原生SQL直接完成更新，减少ORM操作次数：

WITH user_subscriptions AS (
    SELECT 
        id, 
        tokens, 
        used_tokens, 
        tokens - used_tokens AS available,
        SUM(tokens - used_tokens) OVER (ORDER BY created_at) AS cumulative_available
    FROM subscriptions
    WHERE user_id = ? AND isPaid = true AND used_tokens < tokens
)
UPDATE subscriptions s
JOIN (
    SELECT 
        id,
        CASE 
            WHEN cumulative_available <= ? THEN used_tokens + available
            WHEN cumulative_available - available < ? THEN used_tokens + (? - (cumulative_available - available))
            ELSE used_tokens
        END AS new_used_tokens
    FROM user_subscriptions
) AS updates ON s.id = updates.id
SET s.used_tokens = updates.new_used_tokens
WHERE s.id IN (SELECT id FROM user_subscriptions);

在Laravel中可通过DB::statement()执行该SQL，替换?为对应参数（用户ID、所需Token数量）。

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