MySQL批量加载 [英] MySQL bulk load

问题描述

我正在尝试将csv文件加载到mysql表中。
分隔符：，（逗号）

I'm trying to load csv files into mysql table. Delimiter : ,(comma)

作为源数据的一部分，很少有字段值用双引号引起来，而在双引号中则包含了
几乎没有/包含在字段数据中的记录，因此我们需要对其进行转义。

As part of the source data few of the field values are enclosed in double quotes and inside the double quotes we have , There are few records for which / is part of the field data and we need to escape it.

默认情况下，/会转义，当我指定因为逃脱字符正在逃脱。由于我们在同一个文件中有多个特殊字符，因此我们需要对多个特殊字符进行转义。

By default / is getting escaped and when I specified the " as escape character " is getting escaped. As we have multiple special characters inside the same file, we need to escape multiple special characters.

任何建议

例如：

 id name location
 1  A   "Location , name here"
 2  B   "Different Location"
 3  C   Another Location
 4  D   Location / with escape character  

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

推荐答案

我认为这是不可能的。请参考加载数据参考

I think it's not possible. Referring to LOAD DATA reference

任何字段或行处理选项都可以指定一个空字符串（''）。如果不为空，则[OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是单个字符。

Any of the field- or line-handling options can specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character.

仅a ESCAPED BY字段支持单个字符。
我的建议是使用任何编程语言（例如PHP，C＃等）使用regexp逐行打开和处理文件

Only a single char is supported for ESCAPED BY field. My proposal is to use any programming language (e.g. PHP, C# etc.) for opening and processing file line-by-line using regexp

这篇关于MySQL批量加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！