MySQL"LOAD DATA INFILE"；和缺少双引号 [英] MySQL "LOAD DATA INFILE" and Missing Double Quotes

问题描述

我正在尝试使用LOAD DATA INFILE技术将CSV加载到MySQL中.它工作正常，但是我遇到了一个问题，其中某些列使用双引号，而有些则不使用双引号.

I'm trying to load a CSV into MySQL using the LOAD DATA INFILE technique. It's working fine, but I have a problem where some columns use double quotes and some do not.

示例:

something,123,something,"Bauer, Jack",123,something

发生的情况是引号内的逗号中断了导入，因此我的数据最后都被压缩了.不确定如何使导入内容转义双引号内的逗号.

What happens is the commas inside the quotes break the import, so my data is all jacked up at the end. Not sure how to get the import to escape commas inside the double quotes.

mysql --user=<USER> --password=<PASS> -e "LOAD DATA INFILE '<FILENAME>' INTO TABLE <TABLENAME> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (col1, col2, col3, ...)" <DATABASE>

推荐答案

您需要使用附加选项执行语句LOAD DATA INFILE

You need to execute the statement LOAD DATA INFILE with the additional option

FIELDS OPTIONALLY ENCLOSED BY '"'

因此整个语句变为

    LOAD DATA INFILE '<FILENAME>' INTO TABLE <TABLENAME>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(col1, col2, col3, ...)

有关更多信息，请查阅优秀的 MySQL参考手册例如适用于MySQL 5.1 GA.

For further readings, please consult the excellent MySQL Reference Manual e.g. for MySQL 5.1 GA.

这篇关于MySQL"LOAD DATA INFILE"；和缺少双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！