使用加载数据本地INFILE将CSV数据存储到MySQL中的麻烦

Question

>更新：

根据建议使用Table::insert的答案，我更新了查询。

// At this point the data is stored in an array
// I turn it into a collection to make use of the chunk function
$records = collect($records); 
// I split the array into chunks
// Inserting everything at once gave memory issues
$chunks = $records->chunk(500); 
// Then I iterate the chunks and insert them into the database
foreach ($chunks as $chunk) {
DB::table('table')->insert($chunk->toArray()); 
}

这样做的问题是，虽然比使用以前的方法更快，但将所有内容插入我的数据库仍然需要几分钟，因此这不是实际的解决方案。

我不确定这是否是错误的方法，如果我在这里错过了什么/做错了什么。

>描述：

我有一个 Angular 表单，用户可以在其中上传包含 CSV 数据的.txt文件。这是它的工作原理：

.HTML：

<input type="file" class="form-control w-auto" (change)="getFile($event)" accept=".txt" />

打字稿：

getFile(e) {
this.file = e.target.files[0];
this.file_description = e.target.files[0].name;
}
submit() {
let reader = new FileReader();
reader.onload = () => {
let result = reader.result;
this.ImportService.import(result).subscribe(
(response) => {
// Do success stuff
},
(_error) => {
// Do error stuff
}
);
};
if (this.file) {
reader.readAsText(this.file, "UTF-16LE");
}
}

这会将数据发送到我的Laravel实例，我想将其存储到MySQL中。 我已经能够通过将接收到的数据处理到数组中并逐一插入每一行来做到这一点，如下所示：

.PHP：

$array = array();
$csv = str_getcsv($request->file, "n");
foreach ($csv as &$row) {
$row = str_getcsv($row, ";");
$array[] = $row;
}
array_splice($array, 0, 1);
foreach ($array as &$row) {
$query = Table::firstOrNew(['col2' => $row[1], 'col3' => $row[2]]);
$query->col1 = $row[0];
$query->col2 = $row[1];
$query->col3 = $row[2];
$query->col4 = $row[3];
$query->col5 = $row[4];
// [...]
$query->col72 = $row[71];
$query->col73 = $row[72];
$query->save();
}

>问题：

此处发送的数据包含大约 100.000 条记录。这种方法太慢了，通常会导致超时/5-10+ 分钟的等待。

<小时 />

尝试的解决方案：

我一直在尝试使用LOAD DATA LOCAL INFILE，但我无法让它工作。

代码如下：

$results = DB::connection()->getpdo()->exec(
"LOAD DATA LOCAL INFILE '" . $request->file . "' IGNORE INTO TABLE `table`
FIELDS TERMINATED BY ';'
LINES TERMINATED BY 'n'
IGNORE 1 LINES (
`col1`,
`col2`,
`col3`,
// etc
)"
);

简而言之，这种方法存在三个问题：

• 如果CSV数据包含任何撇号，则会出现语法错误。
• 我的 MySQL 实例上的local_infile属性默认处于禁用状态，并在重新启动时恢复为禁用。
• 修复上述问题后，我收到以下错误：General error: 7890 Can't find file 'col1;col2;col3;etc'.

我尝试使用LOAD DATA INFILE但是当查询尝试获取文件内容时，我收到几个"拒绝访问"错误。

---

如果您需要任何进一步的信息，请告诉我。

Answer 1

像这样的任务可能更适合在队列/作业中处理，这样用户就不会等待实时完成。

无论哪种方式，我都建议重构代码。 firstOrNew(( 将为您处理的每条记录运行查询，因此这将对数据库造成重大压力。

方法insert((和update((支持一次处理许多记录，因此使用PHP预处理记录，以便您可以使用这两种方法。

使用一个数据库查询获取所有现有记录，然后构建两个数组来调用 insert(( 和 update((。

我认为如果您创建要搜索的值对，则可以创建这样的 where 查询：

Table::where(function($query) use ($pairs) {
foreach ($pairs as $pair) {
$query->where('col2', $pair['col2'])->where('col3', $pair['col3']);
}
});

这将为您提供现有记录，因此您可以在 PHP 中遍历所有记录以构建两个数组，然后仅运行一次插入和更新查询：

Table::insert($new_records);
Table::update($existing_records);