gpt4 book ai didi

php - PDO - 将大数组插入 MySQL 数据库

转载 作者:太空宇宙 更新时间:2023-11-03 10:38:58 26 4
gpt4 key购买 nike

我需要一些帮助来改进我当前的代码。我有一个巨大的数组(里面大约有 20,000 个对象)。该数组如下所示:

  Array
(
[0] => Player Object
(
[name] => Aaron Flash
[level] => 16
[vocation] => Knight
[world] => Amera
[time] => 900000
[online] => 1
)

[1] => Player Object
(
[name] => Abdala da Celulose
[level] => 135
[vocation] => Master Sorcerer
[world] => Amera
[time] => 900000
[online] => 1
)

[2] => Player Object
(
[name] => Ahmudi Segarant
[level] => 87
[vocation] => Elite Knight
[world] => Amera
[time] => 900000
[online] => 1
)

[3] => Player Object
(
[name] => Alaskyano
[level] => 200
[vocation] => Royal Paladin
[world] => Amera
[time] => 900000
[online] => 1
)

[4] => Player Object
(
[name] => Aleechoito
[level] => 22
[vocation] => Knight
[world] => Amera
[time] => 900000
[online] => 1
)

依此类推...总共有大约 20,000 个 Player Object。

现在我想将它们全部插入到我的数据库中。我想找到一种不遍历所有玩家的方法。它导致了很多性能问题,几乎要毁了我的电脑。我想在一次查询中完成。

但是我如何才能在不循环遍历的情况下获取玩家对象的属性,例如每个对象的“名称”、“级别”和“职业”?

我的代码是这样的:

// Insert player list to database
$sql = $db->prepare("INSERT INTO players (name, level, vocation, world, month, today, online) VALUES (:name, :level, :vocation, :world, :time, :time, :online) ON DUPLICATE KEY UPDATE level = :level, vocation = :vocation, world = :world, month = month + :time, today = today + :time, online = :online");

foreach ($players as $player) {
$query = $sql->execute([
":name" => $player->name,
":level" => $player->level,
":vocation" => $player->vocation,
":world" => $player->world,
":time" => $player->time,
":online" => $player->online
]);
}

因为现在在底部的 foreach 上,它正在遍历我数组中的 20,000 个玩家对象,并获取他们的名字/级别/职业/世界等等。

有更好的方法吗?我这样做的方式不可能是最好的解决方案。我能听到我的 PC 正在重载工作,感觉它好像快要崩溃了。

最佳答案

虽然我仍然怀疑事务和/或批量插入是否是解决资源使用问题的可行解决方案,但它们仍然比像 Dave 建议的那样准备大量语句更好的解决方案。

试一试,看看它们是否有帮助。

以下假设PDO的错误处理方式设置为抛出异常。例如:$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 如果由于某种原因,你不能使用异常模式,那么你需要检查 execute() 每次都抛出你自己的异常。

单笔交易:

$sql = $db->prepare("INSERT INTO players (name, level, vocation, world, month, today, online) VALUES (:name, :level, :vocation, :world, :time, :time, :online) ON DUPLICATE KEY UPDATE level = :level, vocation = :vocation, world = :world, month = month + :time, today = today + :time, online = :online");

$db->beginTransaction();
try {
foreach ($players as $player) {
$sql->execute([
":name" => $player->name,
":level" => $player->level,
":vocation" => $player->vocation,
":world" => $player->world,
":time" => $player->time,
":online" => $player->online
]);
}
$db->commit();
} catch( PDOException $e ) {
$db->rollBack();
// at this point you would want to implement some sort of error handling
// or potentially re-throw the exception to be handled at a higher layer
}

批量交易:

$batch_size = 1000;
for( $i=0,$c=count($players); $i<$c; $i+=$batch_size ) {
$db->beginTransaction();
try {
for( $k=$i; $k<$c && $k<$i+$batch_size; $k++ ) {
$player = $players[$k];
$sql->execute([
":name" => $player->name,
":level" => $player->level,
":vocation" => $player->vocation,
":world" => $player->world,
":time" => $player->time,
":online" => $player->online
]);
}
} catch( PDOException $e ) {
$db->rollBack();
// at this point you would want to implement some sort of error handling
// or potentially re-throw the exception to be handled at a higher layer
break;
}
$db->commit();
}

关于php - PDO - 将大数组插入 MySQL 数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42233178/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com