MySQL“加载数据文件”和缺少双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5904909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL "LOAD DATA INFILE" and Missing Double Quotes
提问by swt83
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.
我正在尝试使用 LOAD DATA INFILE 技术将 CSV 加载到 MySQL 中。它工作正常，但我遇到了一些列使用双引号而有些不使用的问题。
Example:
例子：
something,123,something,"Bauer, Hyman",123,something
What happens is the commas inside the quotes break the import, so my data is all Hymaned 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>
回答by Andreas Krueger
You need to execute the statement LOAD DATA INFILE with the additional option
您需要使用附加选项执行语句 LOAD DATA INFILE
FIELDS OPTIONALLY ENCLOSED BY '"'
Thus the whole statement becoming
因此整个语句变成
LOAD DATA INFILE '<FILENAME>' INTO TABLE <TABLENAME>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(col1, col2, col3, ...)
For further readings, please consult the excellent MySQL Reference Manuale.g. for MySQL 5.1 GA.
如需进一步阅读，请参阅优秀的MySQL 参考手册，例如 MySQL 5.1 GA。
回答by Bob
Hopefully you have figured out what to do by now, but If you haven't hopefully this will help you out.
希望您现在已经弄清楚该怎么做，但是如果您还没有希望这会帮助您。
I have had trouble in Excel 2007 exporting to a customized CSV file. I found that you can change the fields terminator here: http://www.tek-tips.com/viewthread.cfm?qid=1635599&page=15
我在 Excel 2007 导出到自定义 CSV 文件时遇到了问题。我发现您可以在此处更改字段终止符：http: //www.tek-tips.com/viewthread.cfm?qid=1635599& page=15
I prefer to use the pipe | character as it is uncommon, and a little more flexible when dealing with inch", foot" measurements.
我更喜欢用管道| 字符，因为它不常见，并且在处理英寸“英尺”测量时更加灵活。
