我不认为这个问题是重复的。
我希望体内的行被"可选地用"双引号。数值不应封闭。没有标头,这很容易做到。但是,当您使用Union包含标头时,MySQL现在将每一列视为字符串类型,并将所有值封闭在引号中。
您可以将标头添加到这样的外档中:
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`
再次,如果我省略了标题,则只会用引号包围string_values
。使用标头,所有列都被视为字符串列,并将被封闭。
我尝试了:
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`
我认为这会起作用,但是我最终得到了一堆额外的空格。这是我刚刚使用第二种方法创建的文件。标题行以" 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 ,
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