如何在laravel 5中使用以下插入到查询中?
INSERT INTO connection2.table (SELECT * from connection1.table);
我正在寻找两个不同的连接,Connection1.table记录应该转到Connection2.table。
尝试
$c1 = DB("Connection1")->select("SELECT * from table")
foreach($c1 as $record){
DB("Connection2")->table("table")->insert(get_object_vars($record))
}
由于laravel 5.7+insertUsing(array $columns, Closure|Builder|string $query)
可用,
文档:https://laravel.com/api/5.8/Illuminate/Database/Query/Builder.html#method_insertUsing
因此,您现在可以使用类似的Laravel链接样式进行整个查询
DB::table('newTable')->insertUsing(
['column1', 'column2', 'column3',], // ..., columnN
function ($query) {
$query
->select(['column1', 'column2', 'column3',]) // ..., columnN
->from('oldTable');
// optional: you could even add some conditions:
// ->where('some_column', '=', 'somevalue')
// ->whereNotNull('someColumn')
}
);
DB
在app.php
'aliases'
中设置为'DB' => Illuminate'Support'Facades'DB::class,
@Wistar,感谢回复,您的代码$record附带了一个插入不接受的对象类,它需要数组类型。
我使用它如下:
DB::setFetchMode(PDO::FETCH_ASSOC);
$table_records = DB::connection('Connection1')->select("SELECT * from table");
DB::setFetchMode(PDO::FETCH_CLASS);
DB::connection('Connection2')->table("table")->insert($table_records);
详细说明如何在大型表的情况下对结果进行块处理。当然,你可以随心所欲,5000只是一个例子。
$chunk1 = DB("Connection1")->table("table")->orderBy("id")->chunk(5000, function($c1) {
foreach($c1 as $record) {
DB("Connection2")->table("table")->insert(get_object_vars($record))
}
});
$basket_data = DB::table('baskets')->get();
foreach($basket_data as $records)
{
DB::table('basket_after_payments')->insert(get_object_vars($records));
}