MySQL 如何 SELECT INTO OUTFILE 用引号括起来的标题标签而不是正文字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13216667/
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
How to SELECT INTO OUTFILE with header labels enclosed by quotes but not body fields?
提问by Buttle Butkus
I do not believe this question is a duplicate.
我不相信这个问题是重复的。
I want rows in the body to be "OPTIONALLY ENCLOSED BY" double quotes. Numerical values should not be enclosed. That's easy to do without a header. But when you include a header using UNION, MySQL now treats every column as a string type and encloses all the values in quotes.
我希望正文中的行是“OPTIONALLY ENCLOSED BY”双引号。不应包含数值。没有标题，这很容易做到。但是当您使用 UNION 包含标题时，MySQL 现在将每一列视为字符串类型并将所有值括在引号中。
You can add a header to SELECT INTO OUTFILE like this:
您可以像这样向 SELECT INTO OUTFILE 添加标题：
SELECT "id", "numerical_values", "string_values" #header section of csv
UNION ALL
SELECT `id`, `numerical_values`, `string_values` #body section of csv
INTO OUTFILE "/tmp/values.csv"
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM `values_table`
Again, if I omit the header, only string_valueswill be enclosed by quotes.
With the header, all columns are seen as string columns and will be enclosed.
同样，如果我省略标题，则只会string_values用引号括起来。使用标题，所有列都被视为字符串列并将被封闭。
I tried this:
我试过这个：
SELECT '"', "id", ',"', "numerical_values", ',"', "string_values", '"'
#added quotes/commas to header
UNION ALL
SELECT `id`, ',', `numerical_values`, ',"', `string_values`, '"'
#added commas, and add quotes around string_values
INTO OUTFILE "/tmp/values.csv"
FIELDS TERMINATED BY "" ENCLOSED BY '' #empty values for fields terminated and enclosed
# empty because manually selected in query
LINES TERMINATED BY "\n"
FROM `values_table`
I thought that would work, but I ended up with what looks like a bunch of extra whitespace. This is from an file I just created with the 2nd method. The header row ends at "string7". The 1st line of data ends after "207". I just put part of the 3rd line.
我认为这会奏效，但我最终得到了一堆额外的空白。这是来自我刚刚用第二种方法创建的文件。标题行以“string7”结尾。第一行数据在“207”之后结束。我只是把第三行的一部分。
" num1"," string1"," num2"," string2"," num3"," string3"," num4"," string4"," num5"," string5"," num6"," string6"," string7""
33.95 ," 1023 ", 7.50 ," 207-1023 ", 26.95 ,"2 1023 ",23.00 ,"3Wx4Hx4D ",19.00 ,"1023 ", 0.00 ,"UPC: 123456789012 "," 207 "
40.95 ," 1058 ", 9.00 ,
回答by eggyal
SELECT '"id"', '"numerical_values"', '"string_values"'
UNION ALL
SELECT id, numerical_values, CONCAT('"', REPLACE(string_values, '"', '""'), '"')
INTO OUTFILE '/tmp/values.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY '\n'
FROM values_table
