laravel 批次插入並在遇到重複鍵時更新

pine007發表於2024-08-15
/**
 * 批次插入並在遇到重複鍵時更新
 * @param array $values
 * @return bool
 */
public static function insertOnDuplicate(array $values)
{
    if (empty($values)) {
        return true;
    }

    if (!is_array(reset($values))) {
        $values = [$values];
    }

    foreach ($values as $key => $value) {
        ksort($value);
        $values[$key] = $value;
    }

    $eloquentQuery = static::query();
    $sql = static::compileInsertOnDuplicate($eloquentQuery, $values);
    return $eloquentQuery->getConnection()->statement($sql, Arr::flatten($values, 1));
}

/**
 * @param Builder $query
 * @param array $values
 * @return string
 */
protected static function compileInsertOnDuplicate(Builder $query, array $values)
{
    $baseQueryBuilder = $query->getQuery();

    $table = $baseQueryBuilder->getGrammar()->wrapTable($baseQueryBuilder->from);

    if (!is_array(reset($values))) {
        $values = [$values];
    }

    $columns = $baseQueryBuilder->getGrammar()->columnize(array_keys(reset($values)));

    $parameters = collect($values)->map(function ($record) use ($baseQueryBuilder) {
        return '('.$baseQueryBuilder->getGrammar()->parameterize($record).')';
    })->implode(', ');

    $duplicates = collect(explode(',', $columns))->map(function ($column) {
        return "$column = values($column)";
    })->implode(', ');

    return "insert into $table ($columns) values $parameters on duplicate key update $duplicates";
}

相關文章