MYSQL大量挿入データ設定パケットmax_allowed_packetサイズ

show VARIABLES like '%max_allowed_packet%';

[mysqld]
max_allowed_packet=200M

set global max_allowed_packet = 20*1024*1024*10

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;