设置mysql数据包大小_MYSQL 大量插入数据设置数据包max_allowed_packet大小

查询目前大小

show VARIABLES like '%max_allowed_packet%';

修改.my.cnf

[mysqld]

max_allowed_packet=200M

命令行

set global max_allowed_packet = 20*1024*1024*10

/**

* 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;