php 整行插入mysql_PHP：一口气向MySQL插入10.000行

我需要读取一个具有约10.000行的excel文件,并将其保存到MySQL中的表中.我使用的方法是创建一个foreach()循环,并在其内部准备,绑定和执行每一行.

执行时间大约是130秒,我认为这很糟糕.那是在localhost上,因此当脚本将实时运行(共享主机)时,执行时间肯定会更长.

这是代码

ini_set('max_execution_time', 300);

$time_start = microtime(true);

$user = 'root';

$pass = '';

$driver_options = array(

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,

);

try {

$dbh = new PDO('mysql:host=127.0.0.1;dbname=excel', $user, $pass, $driver_options);

} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "
";

die();

}

set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');

/** PHPExcel_IOFactory */

include 'PHPExcel/IOFactory.php';

$inputFileName = 'original.xls';

try {

$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

} catch(Exception $e) {

die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

}

/*

cap [X] - loc [AK]

targa [D]

fabbrica [F]

provincia di residenza [V] - loc [AI]

comune di residenza [W] - loc [AJ]

data prima immatricolazione [AB]

dati anagrafici [T] - loc [AG]

*/

$xls = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$headers = $xls[1];

$loops = 0;

$rows = array_slice($xls, 1);

foreach ( $rows as $row ) {

$excelData = array(

'targa' => $row['D'],

'fabbrica' => $row['F'],

'immatricolazione' => $row['AB'],

'cap' => $row['AK'] == '' ? $row['X'] : $row['AK'],

'datiAnagrafici' => $row['AG'] == '' ? $row['T'] : $row['AG'],

'comuneResidenza' => $row['AJ'] == '' ? $row['W'] : $row['AJ'],

'provinciaResidenza' => $row['AI'] == '' ? $row['V'] : $row['AI']

);

$insert = $dbh->prepare("

INSERT INTO

data(targa, fabbrica, immatricolazione, cap, datiAnagrafici, comuneResidenza, provinciaResidenza)

VALUES(:targa, :fabbrica, :immatricolazione, :cap, :datiAnagrafici, :comuneResidenza, :provinciaResidenza)

");

$insert->execute($excelData);

if ( $insert->rowCount() != 1 ) {

echo 'Script interrupted at loop nr. '.$loops;

break;

}

++$loops;

}

$time_end = microtime(true);

$execution_time = ($time_end - $time_start);

echo 'Total Execution Time: '.$execution_time.' s';

有什么办法可以优化代码性能吗？循环有问题吗？

谢谢.