我以编程方式从excel文件导入行到数据库。excel文件有10列* 30000行。我已经将这些数据导入php数组然后插入到数据库中。
上传文件后，将所有行插入数据库需要7-8分钟。我知道两种插入
行的方法方法1:
生成动态查询，如
INSERT INTO table_name (col1, col2,..) VALUES (row1, row1, ....), (row2, row2, ....), (row3, row3, ....), (row4, row4, ....), ...
，并运行查询插入所有行。
方法2:
$con->prepare("INSERT INTO table_name (col1, col2, ...) VALUES (:col1, :col2, ...)");
foreach ($rows as $row) { // Loop through all rows and insert them
$result->bindParam(':col1', $val1);
$result->bindParam(':col2', $val2);
$result->bindParam(':col3', $val3);
...
...
$result->execute();
}
第一个看起来混乱和低效，我使用第二种方法，但它每秒只插入500-700行，需要7-8分钟的总时间来插入所有行。
还有什么方法比这些更有效和更快?
编辑:不建议直接导入excel文件到mysql。数据需要在插入数据库之前进行处理
最快的方法是将数百个插入封装到事务和提交中。MySQL将使用一个单一的输入/输出操作的硬盘驱动器写入许多记录-这是快速的。
$con->prepare("INSERT INTO table_name (col1, col2, ...) VALUES (:col1, :col2, ...)");
$inserts = 1000;
$counter = 0;
foreach ($rows as $row) { // Loop through all rows and insert them
if($counter === 0 && !$con->inTransaction()) {
$con->beginTransaction();
}
$result->bindParam(':col1', $val1);
$result->bindParam(':col2', $val2);
$result->bindParam(':col3', $val3);
...
...
$result->execute();
$counter++;
if($counter === $inserts) {
$con->commit();
}
}
if($con->inTransaction()) {
$con->commit();
$counter = 0;
}
上面的代码应该在每执行1000次插入之后提交。我没有测试过它，所以它很可能包含错误，但它的目的是说明如何在一个事务中包装1000个插入并提交它。
尝试将SQL封装到TRANSACTION中，并在最后提交数据。这将释放大量的资源，因为提交数据做了相当多的事情(它告诉你的DB保存你正在插入的数据，并REINDEX -假设你有它们)。
另外，尝试确保您到数据库的连接是Pooled的——这意味着您不会每次都从数据库获取新的连接，而是使用相同的连接。
使用事务的风险是，如果你的数据集中出现一个错误(阻止INSERT)，它将回滚整个数据集。
像这样的东西可以工作…
<?php
try {
$dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2',
array(PDO::ATTR_PERSISTENT => true)); // POOLED
echo "Connected'n";
} catch (Exception $e) {
die("Unable to connect: " . $e->getMessage());
}
try {
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dbh->prepare("INSERT INTO table_name (col1, col2, ...) VALUES (:col1, :col2, ...)");
foreach ($rows as $row) { // Loop through all rows and insert them
// I am not sure where you define $result
// Review in your implementation if you use
$result->bindParam(':col1', $val1);
$result->bindParam(':col2', $val2);
$result->bindParam(':col3', $val3);
...
...
$result->execute();
}
} catch (Exception $e) {
$dbh->rollBack();
echo "Failed: " . $e->getMessage();
}
$dbh->commit();
?>