dongsuiwo0279 2017-02-14 18:03
浏览 55
已采纳

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

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
        )

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?

This is what my code looks like:

// 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
  ]);
}

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.

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.

  • 写回答

2条回答 默认 最新

  • dongyu4554 2017-02-15 01:58
    关注

    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.

    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.

    Single transaction:

    $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
    }
    

    Batched Transactions:

    $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();
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 电脑开机过商标后就直接这样,求解各位
  • ¥15 mysql , 用自己创建的本地主机和用户名 登录不上
  • ¥15 关于#web项目#的问题,请各位专家解答!
  • ¥15 vmtools环境不正常
  • ¥15 请问如何在Ubuntu系统中安装使用microsoft R open?
  • ¥15 jupyter notebook
  • ¥30 informer时间序列预测
  • ¥20 SSR引物多态性分析
  • ¥15 大漠插件在Win11易语言注册调用和免注册灵异事件,VS上注册调用完全没问题
  • ¥15 Addressable缓存机制做热更新的问题