Laravel动态表单批量插入数据库的高效实现方案咨询

我需要实现一个能提交多行表单数据并插入Laravel数据库的功能，目前已有可行方案，但希望找到更高效的实现方式。当前通过jQuery动态生成多行表单，控制器内用循环逐条插入数据，代码可正常运行，但想优化性能与代码质量。

当前实现代码 #

表单代码 #

<form action="" method="POST" id="myfrm">
    @csrf
    <div class="table-responsive">
        <table class="table table-primary" id="myTable">
            <thead>
                <tr>
                    <th scope="col">Title</th>
                    <th scope="col">Role</th>
                    <th scope="col">Email</th>
                    <th scope="col">Phone</th>
                    <th scope="col" class="pull-right"><button type="button" class="btn btn-success" onclick="btnAdd()">+</button></th>
                </tr>
            </thead>
            <tbody>
                <tr id="trow">
                    <td><input type="text" name="title[]"></td>
                    <td><input type="text" name="role[]"></td>
                    <td><input type="text" name="email[]"></td>
                    <td><input type="text" name="phone[]"></td>
                    <td><button type="button" class="btn btn-danger" onclick="btnDel(this)">x</button></td>
                </tr>
            </tbody>
        </table>
    </div>

    <div class="col-md-12">
        <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>

控制器代码 #

public function bulkcreate(Worker $worker, Request $request){ 
    
    $title = $request->title;
    $role = $request->role;
    $email = $request->email;
    $phone = $request->phone;

    for($i=0;$i<count($title);$i++){
        $datasave = [
            'title'=>$title[$i],
            'role'=>$role[$i],
            'email'=>$email[$i],
            'phone'=>$phone[$i],
            "created_at" =>  \Carbon\Carbon::now(),
            "updated_at" =>  \Carbon\Carbon::now()
        ];
        
        DB::table('workers')->insert($datasave);
    }
    
    return redirect('/workers')->with('message','Data input success');
}

优化方案 #

1. 批量插入替代循环逐条插入 #

当前循环中每次调用insert会触发多次数据库请求，改为批量插入仅需一次数据库交互，大幅降低连接开销。

public function bulkcreate(Request $request)
{
    $workers = [];
    $now = \Carbon\Carbon::now();

    foreach ($request->title as $index => $title) {
        // 跳过空行（可选，避免插入无效数据）
        if (empty($title) && empty($request->role[$index]) && empty($request->email[$index]) && empty($request->phone[$index])) {
            continue;
        }

        $workers[] = [
            'title' => $title,
            'role' => $request->role[$index],
            'email' => $request->email[$index],
            'phone' => $request->phone[$index],
            'created_at' => $now,
            'updated_at' => $now
        ];
    }

    // 单次批量插入
    \DB::table('workers')->insert($workers);

    return redirect('/workers')->with('message', 'Data input success');
}

2. 优化表单字段命名结构 #

将分散的数组字段改为按行分组的结构，让请求数据更直观，后续维护扩展更方便。

优化后表单代码

<form action="" method="POST" id="myfrm">
    @csrf
    <div class="table-responsive">
        <table class="table table-primary" id="myTable">
            <thead>
                <tr>
                    <th scope="col">Title</th>
                    <th scope="col">Role</th>
                    <th scope="col">Email</th>
                    <th scope="col">Phone</th>
                    <th scope="col" class="pull-right">
                        <button type="button" class="btn btn-success" onclick="btnAdd()">+</button>
                    </th>
                </tr>
            </thead>
            <tbody>
                <tr class="trow">
                    <td><input type="text" name="workers[][title]"></td>
                    <td><input type="text" name="workers[][role]"></td>
                    <td><input type="text" name="workers[][email]"></td>
                    <td><input type="text" name="workers[][phone]"></td>
                    <td><button type="button" class="btn btn-danger" onclick="btnDel(this)">x</button></td>
                </tr>
            </tbody>
        </table>
    </div>

    <div class="col-md-12">
        <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>

对应控制器代码

public function bulkcreate(Request $request)
{
    $now = \Carbon\Carbon::now();
    $workers = array_map(function ($worker) use ($now) {
        // 过滤空字段（可选）
        $worker = array_filter($worker);
        if (empty($worker)) return null;
        
        return array_merge($worker, [
            'created_at' => $now,
            'updated_at' => $now
        ]);
    }, $request->workers);

    // 移除空数组
    $workers = array_filter($workers);

    \DB::table('workers')->insert($workers);

    return redirect('/workers')->with('message', 'Data input success');
}

3. 添加请求验证 #

确保数据合法性，避免无效数据插入，可直接在控制器内验证或创建自定义请求类。

public function bulkcreate(Request $request)
{
    // 验证规则
    $validated = $request->validate([
        'workers.*.title' => 'required|string|max:255',
        'workers.*.role' => 'required|string|max:255',
        'workers.*.email' => 'required|email|max:255|unique:workers,email', // 邮箱唯一验证可选
        'workers.*.phone' => 'nullable|string|max:20',
    ]);

    $now = \Carbon\Carbon::now();
    $workers = array_map(function ($worker) use ($now) {
        return array_merge($worker, [
            'created_at' => $now,
            'updated_at' => $now
        ]);
    }, $validated['workers']);

    \DB::table('workers')->insert($workers);

    return redirect('/workers')->with('message', 'Data input success');
}

4. 使用Eloquent模型批量创建 #

若已有Worker模型，可直接使用模型方法，更符合Laravel架构规范：

• insert：不触发模型事件，性能与DB门面一致
• createMany：触发模型事件，但逐条创建，适合数据量小的场景

use App\Models\Worker;

public function bulkcreate(Request $request)
{
    $validated = $request->validate([
        'workers.*.title' => 'required|string|max:255',
        'workers.*.role' => 'required|string|max:255',
        'workers.*.email' => 'required|email|max:255',
        'workers.*.phone' => 'nullable|string|max:20',
    ]);

    $now = \Carbon\Carbon::now();
    $workers = array_map(function ($worker) use ($now) {
        return array_merge($worker, [
            'created_at' => $now,
            'updated_at' => $now
        ]);
    }, $validated['workers']);

    // 批量插入（不触发事件）
    Worker::insert($workers);

    // 若需触发模型事件，改用createMany（性能稍差）
    // Worker::createMany($workers);

    return redirect('/workers')->with('message', 'Data input success');
}

5. 前端动态生成逻辑优化 #

修复原代码中重复ID问题，优化DOM操作逻辑：

function btnAdd() {
    const tableBody = document.querySelector('#myTable tbody');
    const newRow = document.querySelector('.trow').cloneNode(true);
    
    // 清空新行输入值
    newRow.querySelectorAll('input').forEach(input => input.value = '');
    
    tableBody.appendChild(newRow);
}

function btnDel(button) {
    const row = button.closest('tr');
    // 至少保留一行表单
    if (document.querySelectorAll('#myTable tbody tr').length > 1) {
        row.remove();
    }
}

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