MYSQL 大量插入数据设置数据包max_allowed_pac

查询目前大小

show VARIABLES like '%max_allowed_packet%';

修改.my.cnf

[mysqld]
max_allowed_packet=200M

命令行

set global max_allowed_packet = 20*1024*1024*10

<?php
/**
 * CREATE TABLE `demo` (
 * `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 * `data` varchar(255) NOT NULL,
 * PRIMARY KEY (`id`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 */

/*
 * 连接数据库
 */
$dsn      = 'mysql:host=127.0.0.1;dbname=testdb;';
$user     = 'root';
$password = '123456';
try {
    $dbh = new PDO( $dsn , $user , $password );
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}

/*
 * 调整 Mysql Server接受的数据包
 */
$dbh->exec ( "set global max_allowed_packet = 2*1024*1024*1024" );

/*
 * 测试记录总数
 */
$rowsCount = 10000;

/*
 * 1 普通方式,逐行写入测试数据
 */
$time_start = microtime ( true );
try {
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql = "insert into demo( data ) value ('" . mt_rand ( 10000000 , 99999999 ) . "')";
        $dbh->exec ( $sql );
    }
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "1 Execution time: {$time} s" . PHP_EOL;

/*
 * 2 事务
 */
$time_start = microtime ( true );
$dbh->beginTransaction ();
try {
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql = "insert into demo(data) value ('" . mt_rand ( 10000000 , 99999999 ) . "')";
        $dbh->exec ( $sql );
    }
    $dbh->commit ();
} catch ( \Exception $e ) {
    $dbh->rollBack ();
    throw new \Exception( $e->getMessage () );
}

$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "2 Execution time: {$time} s" . PHP_EOL;

/*
 * 3 值合并方式,values (...),(...)
 */
$time_start = microtime ( true );
try {
    $sql = "insert into demo( data ) values ";
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql .= "('" . mt_rand ( 10000000 , 99999999 ) . "'),";
    }
    $dbh->exec ( rtrim ( $sql , ',' ) );
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "3 Execution time: {$time} s " . PHP_EOL;

/*
 * 4 合并加事务
 */
$time_start = microtime ( true );
$dbh->beginTransaction ();
try {
    $sql = "insert into demo( data ) values ";
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql .= "('" . mt_rand ( 10000000 , 99999999 ) . "'),";
    }
    $dbh->exec ( rtrim ( $sql , ',' ) );
    $dbh->commit ();
} catch ( \Exception $e ) {
    $dbh->rollBack ();
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "4 Execution time : {$time} s " . PHP_EOL;