如何在Laravel中实现仅Null字段更新及指定SQL转代码？

Hey there! Let's walk through how to tackle both of your Laravel update requirements clearly.

If you want to update a field only if it's currently NULL (either for a single model instance or batch rows), Laravel gives you a couple of clean ways to do this:

For a single Eloquent model instance #

Suppose you have a User model and you want to update their email only if it's currently null:

$user = User::find(123);

// Straightforward conditional check
if (is_null($user->email)) {
    $user->update(['email' => 'new-user@example.com']);
}

// Or use the fluent `when()` method for more concise code
$user->when(is_null($user->email), function ($model) {
    $model->update(['email' => 'new-user@example.com']);
});

For batch updating multiple rows #

If you need to update all rows where a specific column is NULL, use the whereNull() clause with the query builder:

DB::table('users')
    ->whereNull('email')
    ->update(['email' => 'default@example.com']);

Your original SQL uses ISNULL() (a SQL Server-specific function; use IFNULL() for MySQL or COALESCE() for cross-database support). Here's how to translate it to safe, Laravel-compatible code:

Using the Query Builder (DB Facade) #

We'll use DB::raw() to include the conditional logic, and parameter binding to avoid SQL injection (always prioritize this over raw string concatenation):

// Replace these with your actual values (e.g., from request input or variables)
$field1 = $request->input('field1');
$field2 = $request->input('field2');
$field3 = $request->input('field3');

DB::table('MyTable')
    ->where('field4', 1234)
    ->update([
        'Field1' => DB::raw("ISNULL(?, Field1)", [$field1]),
        'Field2' => DB::raw("ISNULL(?, Field2)", [$field2]),
        'Field3' => DB::raw("ISNULL(?, Field3)", [$field3])
    ]);

If you're using an Eloquent Model #

If you have a MyTable model set up (mapped to your database table), you can use it directly instead of the DB facade:

MyTable::where('field4', 1234)
    ->update([
        'Field1' => DB::raw("ISNULL(?, Field1)", [$field1]),
        'Field2' => DB::raw("ISNULL(?, Field2)", [$field2]),
        'Field3' => DB::raw("ISNULL(?, Field3)", [$field3])
    ]);

Quick note for other databases:

• For MySQL, replace ISNULL() with IFNULL()
• For cross-database compatibility (works in PostgreSQL, MySQL, SQL Server, etc.), use COALESCE():

  'Field1' => DB::raw("COALESCE(?, Field1)", [$field1]),
  

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