MySQL LOAD DATA INFILE：工作，但不可预测的行终止符 [英] MySQL LOAD DATA INFILE: works, but unpredictable line terminator

问题描述

MySQL有一个不错的CSV导入功能 LOAD DATA INFILE 。

MySQL has a nice CSV import function LOAD DATA INFILE.

我有一个大型数据集，定期从CSV导入，因此此功能正是我需要的。我有一个工作脚本，可以完美地导入我的数据。

I have a large dataset that needs to be imported from CSV on a regular basis, so this feature is exactly what I need. I've got a working script that imports my data perfectly.

.....除了....我不知道提前什么结束行终止符。

.....except.... I don't know in advance what the end-of-line terminator will be.

我的SQL代码目前看起来像这样：

My SQL code currently looks something like this:

LOAD DATA INFILE '{fileName}'
 INTO TABLE {importTable}
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
( {fieldList} );

但是，导入数据来自多个来源，其中有些包含 \\\
终止符;其他人有 \r\\\
。我无法预测我会有哪一个。

However, the import data is coming from multiple sources. Some of them have the \n terminator; others have \r\n. I can't predict which one I'll have.

是否有使用 LOAD DATA INFILE 指定我的行可以用 \\\
或 \r\\\
？我如何处理这个问题？

Is there a way using LOAD DATA INFILE to specify that my lines may be terminated with either \n or \r\n? How do I deal with this?

推荐答案

我只是预处理它。作为导入过程的一部分，全局搜索/替换从命令行工具更改\r\\\
到\\\
done应该是简单和高效的。

I'd just pre-process it. A global search/replace to change \r\n to \n done from a command line tool as part of the import process should be simple and performant.

这篇关于MySQL LOAD DATA INFILE：工作，但不可预测的行终止符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！