Laravel-在重复键上批量插入会更新大数据集 [英] Laravel - Bulk insert on duplicate key update large data set
问题描述
我有大约80k条记录,我每天需要运行几次插入/更新脚本.
I have approx 80k records that I need to run an insert/update script on several times per day.
INSERT INTO `my_rankings` (`id`, `rank`) VALUES (1,100),(2,99)(3,102)...(80000,3)
ON DUPLICATE KEY UPDATE `rank` = values(`rank`);
这些记录当前为数组格式:
These records are currently in an array format:
$rankings = [
['id' => 1, 'rank' => 100],
['id' => 2, 'rank' => 99],
['id' => 3, 'rank' => 102],
...
['id' => 80000, 'rank' => 3],
]
我可以运行上述更新查询吗?
Is there a nice / performant way I can run the above mentioned update query?
我看过Eloquent的Model::updateOrCreate(...)
.但是我认为我不能将其用于批量插入/更新.
I have looked at Eloquent's Model::updateOrCreate(...)
. However I don't think I can use this for bulk insert/updates.
我想避免在$rankings
数组上使用foreach
并插入/更新单个记录,因为脚本将花费很长时间.
I want to avoid using foreach
on my $rankings
array and insert/updating individual records, because the script will take far too long.
我在 https://stackoverflow.com/a/34815725/1239122 下有此问题的答案它远非优雅.
I have an answer for this question below https://stackoverflow.com/a/34815725/1239122, however it is far from elegant.
推荐答案
我有一个解决方案-它不美观,但速度很快. 8万条记录为1.6秒. 任何更好的解决方案将不胜感激.
I have a solution - It's not elegant, but very fast. 1.6s for 80k records. Any better solutions would be much appreciated.
$allResults = [
['id' => 1, 'rank' => 100],
['id' => 2, 'rank' => 99],
['id' => 3, 'rank' => 102],
...
['id' => 80000, 'rank' => 3],
];
$rankings = [];
foreach ($allResults as $result) {
$rankings[] = implode(', ', ['"' . $result['id'] . '"', $result['rank']]);
}
$rankings = Collection::make($rankings);
$rankings->chunk(500)->each(function($ch) {
$rankingString = '';
foreach ($ch as $ranking) {
$rankingString .= '(' . $ranking . '), ';
}
$rankingString = rtrim($rankingString, ", ");
try {
\DB::insert("INSERT INTO my_rankings (`id`, `rank`) VALUES $rankingString ON DUPLICATE KEY UPDATE `rank`=VALUES(`rank`)");
} catch (\Exception $e) {
print_r([$e->getMessage()]);
}
});
这篇关于Laravel-在重复键上批量插入会更新大数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!