mysql 批量插入数据

业务需要统计数据，刚开始用的是传统的方法，查询，然后循坏插入，竟然用了大半个钟：

    while ($item = $query->fetch()) {
            $barrel = new DataBarrel();
            $barrel->type = 4;
            $barrel->user_id = $item->buyer_id;
            $barrel->user_name = $item->nickname;
            $barrel->init_quantity = $item->use;
            $barrel->zero_time = $yesterday;

            if ($barrel->save() == false) {
                echo $item->buyer_id. '初始化失败：' . $barrel->getErrorMessage() . PHP_EOL;
            }
        }

然后换了另外一种方式，只用了10秒：

//循环插入表。用户名称需要转义
$str = '';
foreach ($buyerBarrel as $k => $item) {
    $str .= "('4', '{$item->buyer_id}', '". addslashes($item->nickname) ."', '{$item->use}', '0', '0', '{$yesterday}'),";

    if ($k % 1000 == 0) {
        $sql = 'INSERT INTO '. (new DataBarrel())->getSource() .
            ' (`type`, user_id, user_name, init_quantity, reduce, increase, zero_time) VALUES '. $str;

        $sql = rtrim($sql, ',');

        $this->db->query($sql);

        $str = '';
    }
}

if ($str) {
    $sql = 'INSERT INTO '. (new DataBarrel())->getSource() .
        ' (`type`, user_id, user_name, init_quantity, reduce, increase, zero_time) VALUES '. $str;

    $sql = rtrim($sql, ',');

    $this->db->query($sql);
}