Laravel本地订单数据同步至线上数据库存储问题求助

我正尝试从本地数据库的order表获取数据集合，并存储到线上数据库的order表中。目前操作步骤如下：

• 从本地数据库的order表获取数据，将其作为orders变量传递到blade视图
• 通过Ajax将orders变量发送至线上服务器
• 将orders变量存储到线上数据库的order表中

第三步是我遇到的问题所在。

本地获取数据代码 #

$orders = DB::table("orders")->where('created_at', '>', Carbon::now()->subHours(15))->latest()->get()->toArray();
return $orders;

Blade视图中的orders变量 #

[{"id":11,"drinkName":"Power Horse","drinkId":"19","drinkType":"Energy Drink","costPerDrink":"5000","totalOrdered":"8","totalOrderAmount":"40000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:56","updated_at":"2024-08-27 14:52:56"},{"id":10,"drinkName":"Coke","drinkId":"9","drinkType":"Chasers","costPerDrink":"1000","totalOrdered":"3","totalOrderAmount":"3000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:49","updated_at":"2024-08-27 14:52:49"},{"id":9,"drinkName":"Crystal","drinkId":"1","drinkType":"Champagne","costPerDrink":"800000","totalOrdered":"3","totalOrderAmount":"2400000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:41","updated_at":"2024-08-27 14:52:41"}]

Ajax发送数据代码 #

function oneSecondFunction(){ 
  $.ajax({
    type:'POST', 
    url:"mysite/app/api/testItem",
    data:{orders : orders, _token: "<?php echo csrf_token(); ?>" },
    success:function(data){                     
      console.log(data);                     
    }
  }); 
}

服务器端接收的数据 #

[{"id":11,"drinkName":"Power Horse","drinkId":"19","drinkType":"Energy Drink","costPerDrink":"5000","totalOrdered":"8","totalOrderAmount":"40000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:56","updated_at":"2024-08-27 14:52:56"},{"id":10,"drinkName":"Coke","drinkId":"9","drinkType":"Chasers","costPerDrink":"1000","totalOrdered":"3","totalOrderAmount":"3000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:49","updated_at":"2024-08-27 14:52:49"},{"id":9,"drinkName":"Crystal","drinkId":"1","drinkType":"Champagne","costPerDrink":"800000","totalOrdered":"3","totalOrderAmount":"2400000","discount":null,"transactionId":null,"status":"Initiated","waitressName":"Jane","waitressId":"3","created_at":"2024-08-27 14:52:41","updated_at":"2024-08-27 14:52:41"}]

请问如何将这些数据存储到线上的order表中？希望能提供代码示例及相关指导。

1. 服务器端数据解析与验证 #

首先在Laravel控制器的对应方法中，先验证并解析接收到的订单数据：

public function testItem(Request $request)
{
    // 验证请求结构合法性
    $request->validate([
        'orders' => 'required|array',
        'orders.*' => 'required|array',
        // 可根据表字段添加更严格的验证，比如：
        // 'orders.*.drinkName' => 'required|string|max:255',
        // 'orders.*.totalOrdered' => 'required|integer|min:1'
    ]);

    // 获取订单数据，兼容字符串转义的JSON格式
    $orders = $request->input('orders');
    if (is_string($orders)) {
        $orders = json_decode($orders, true);
    }

2. 批量插入数据到线上表 #

使用Laravel的批量插入方法提升效率，同时避免本地主键冲突：

// 移除本地订单的id字段，使用线上表的自增主键
    $insertData = collect($orders)->map(function ($order) {
        unset($order['id']);
        return $order;
    })->toArray();

    // 开启事务保证数据一致性
    DB::beginTransaction();
    try {
        DB::table('orders')->insert($insertData);
        DB::commit();

        return response()->json([
            'success' => true,
            'message' => '同步成功',
            'count' => count($insertData)
        ]);
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'error' => $e->getMessage()
        ], 500);
    }
}

3. 前端请求优化（可选） #

为了数据传输更可靠，建议前端发送JSON格式数据：

function oneSecondFunction(){ 
  $.ajax({
    type:'POST', 
    url:"mysite/app/api/testItem",
    contentType: "application/json",
    data: JSON.stringify({orders : orders, _token: "<?php echo csrf_token(); ?>"}),
    success:function(data){                     
      console.log(data);                     
    },
    error: function(xhr) {
        console.error('同步失败:', xhr.responseText);
    }
  }); 
}

此时服务器端需改用$request->json('orders')获取数据：

$orders = $request->json('orders');

关键注意事项 #

• 主键冲突：必须移除本地订单的id字段，除非线上表主键策略和本地完全一致且无冲突风险。
• 字段匹配：确保线上orders表的字段名、数据类型与本地完全匹配，否则会插入失败。
• 重复同步：后续可通过transactionId等唯一标识做幂等校验，避免重复插入相同订单。

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