我有一个大的csv,我想解析并插入到我的数据库中。这里是PHP:
$target = '../uploads/'.$f;
$handle = fopen($target, "r");
$data = fgetcsv($handle, 0, ",");
$rows = array();
while ($data !== FALSE) {
$rows[] = $data;
}
fclose($handle);
if (count($rows)) {
foreach ($rows as $key => $value) {
echo $value;
}
}
每次我试图运行我的脚本,我得到这个错误:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)
有什么办法吗?
我认为这部分是错误的:
$data = fgetcsv($handle, 0, ",");
$rows = array();
while ($data !== FALSE) {
$rows[] = $data;
}
对fgetcsv
的一次调用读取$handle
的一行。您需要将fgetcsv
放入循环条件:
$handle = fopen($target, "r");
$data = fgetcsv($handle, 0, ",");
while (($row = fgetcsv($handle, 0, ",")) !== FALSE) {
// Example insert - obviously use prepared statements/escaping/another DAL
$db->query("INSERT INTO tbl (columns..) VALUES ({$row[0]}, {$row[1]} ... )");
}
使用mysqlimport代替
虽然你可以用PHP解析和构建查询,但如果让MySQL直接处理,你会得到更好的性能。你的数据库会感谢你的。
<?php
exec("mysqlimport mysqlimport [options] db_name textfile1");
?>
来源:
- http://www.php.net/manual/en/function.exec.php
- http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
您不需要在处理之前将文件中的所有csv数据读入内存。
相反,创建一个while循环,每次从文件中读取一行。每次从文件中读取时,都应该向数据库中插入一行。
或者一次读取几行并插入几行。
例子:
$i = 0;
while (($data = fgetcsv($handle, 0, ",") !== FALSE) {
$rows[] = $data;
$i++;
// insert 100 rows at one time.
if ($i % 100 === 0) {
//insert these rows to db
insert_to_db($rows);
//then reset $rows
$rows = array();
}
}
insert_to_db($rows);
使用标准的MySQL LOAD DATA INFILE语句逃避通过PHP读取/解析/插入数据:
function import_csv( $table, $afields, $filename, $delim = ',', $enclosed = '"', $escaped = '''', $lineend = '''r''n', $hasheader = FALSE) {
if ( $hasheader ) $ignore = "IGNORE 1 LINES ";
else $ignore = "";
$q_import =
"LOAD DATA INFILE '" . $_SERVER['DOCUMENT_ROOT'] . $filename . "' INTO TABLE " . $table . " " .
"FIELDS TERMINATED BY '" . $delim . "' ENCLOSED BY '" . $enclosed . "' " .
" ESCAPED BY '" . $escaped . "' " .
"LINES TERMINATED BY '" . $lineend . "' " . $ignore . "(" . implode(',', $afields) . ")"
;
return mysql_query($q_import);
}
在这种情况下,您不需要在PHP中打开/读取CSV文件,MySQL将自行处理数据导入