使用Zend\Db批量插入数据范例

<?php
$rootPath = dirname(__DIR__);
$library = $rootPath."/library";

$includePath = array(
    $library,
    get_include_path()
);
set_include_path(implode(PATH_SEPARATOR, $includePath));

chdir($rootPath);
if(file_exists('vendor/autoload.php')){
    $loader = include '/vendor/autoload.php';
}else{
    exit("Autoload Failed. ");
}
//$loader->setUseIncludePath(true);
$loader->setPsr4("Zend\\",$library.'/zf2_psr4');

$adapter = new Zend\Db\Adapter\Adapter(array(
    'hostname' => 'localhost',
    'database' => 'test',
    'username' => 'root',
    'password' => '',
    
    'driver' => 'mysqli',
    'charset' =>'utf8'
    
    //'driver' => 'pdo_mysql',
    //'driver_options' => array(
    //  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    //),
));

header("Content-type:text/html;charset=utf-8");

$connection = $adapter->getDriver()->getConnection();
$resource = $connection->getResource();

$valuss = [];
for($i = 0; $i < 100000; $i++) {
    $row   = [];
    $row[] = "'".$resource->real_escape_string('\'s vfeelit\\')."'";
    $row[] = "'".$resource->real_escape_string("sku".$i)."'";
    $row[] = "'".$resource->real_escape_string(round(($i+1)/3,2))."'";
    $row[] = "'".$resource->real_escape_string("仓库? 'd".$i)."'";
    $row[] = "'".$resource->real_escape_string("供应:商".$i)."'";
    $row[] = "'".$resource->real_escape_string("链接\\".$i.'\\')."'";
    $row[] = "'".$resource->real_escape_string("采购员".$i.'dd')."'";
    $valuess[] = "(".implode(",",$row).")";
}

$s = microtime(true);
$rs = $adapter->query("INSERT INTO `product`(`name`,`sku`,`purchase_price`,`warehouse_name`,`supplier_name`,`purchase_link`,`who_purchase`) values".implode(",",$valuess),Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$e = microtime(true);
echo "100000 Record into DB:".(float)($e-$s)."\n";

批量插入数据数据是非常快的。不过可能遇到MySQL server has gone away异常,这种异常要么就是数据库链接异常断了,要么就是发送的包太大,这里就是数据包太大导致无法解析。

show global variables like 'max_allowed_packet';
set global max_allowed_packet=1024*1024*16; #改大

补注:
在新版本的Zend Framework中(2.4.8),Zend\Db\Adapter\Platform\Mysql的quoteValue改为如下:

    public function quoteValue($value)
    {
        if ($this->resource instanceof DriverInterface) {
            $this->resource = $this->resource->getConnection()->getResource();
        }
        if ($this->resource instanceof \mysqli) {
            return '\'' . $this->resource->real_escape_string($value) . '\'';
        }
        if ($this->resource instanceof \PDO) {
            return $this->resource->quote($value);
        }
        return parent::quoteValue($value);
    }

填补了之前的Bug。现在是直接调用Resource的具体方法。