PDO-将大型阵列插入MySQL数据库 [英] PDO - Insert large array to MySQL database

问题描述

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

I need some help to improve my current code. I have a huge array (about 20,000 objects inside it). The array looks like this:

  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个玩家对象.

And so on... with about 20,000 Player Object in total.

现在，我要将它们全部插入到我的数据库中.我想找到一种不让所有玩家陷入困境的方法.它导致了很多性能问题，并且几乎使我的计算机丧命.我想一次完成一个查询.

Now I want to insert them all in to my database. I'd like to find a way to not loop through all players. It is causing a lot of performance issues and it's almost killing my computer. I'd like to make it in a single query, all at once.

但是如何获取播放器对象的属性，例如每个对象的名称"，级别"和职业"，而又不循环它们呢?

But how can I get the Player Object attributes, like the "name", "level" and "vocation" of each individual object without looping them through?

这是我的代码的样子:

// 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个播放器对象，并获取它们的名称/级别/职业/世界等.

Because right now on that foreach at the bottom, it is looping through 20,000 player objects in my array, and getting their names/level/vocation/world and so on.

是否有更好的方法可以做到这一点? 我的方法不可能是最好的解决方案.我可以听到我的PC正在超负荷工作，并且感觉好像即将崩溃.

Is there a better way to do this? My way of doing it can't be the best solution. I can hear my PC is working overload and it feels as if it's about to crash.

推荐答案

虽然我仍然怀疑事务和/或批处理插入是否是解决资源使用问题的可行解决方案，但与准备像这样的大量语句相比，它们仍然是更好的解决方案戴夫建议.

While I still doubt that transactions and/or batched inserts are a viable solution to your resource usage problem, they're still a better solution than preparing massive statements like Dave has suggested.

试一下，看看它们是否有帮助.

Give these a shot and see if they help.

以下内容假定将PDO的错误处理模式设置为引发异常.例如:$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);如果由于某种原因不能使用异常"模式，则需要每次检查execute()的返回值并抛出自己的异常.

The following assumes that PDO's error handling mode is set to throw exceptions. Eg: $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); If, for some reason, you can't use Exception mode then you'll need to check the return of execute() each time and throw your own Exception.

单笔交易:

$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();
}

这篇关于PDO-将大型阵列插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！