mysql – 如何使用引号括起但不包含正文字段的标题标签选择INTO OUTFILE?
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何使用引号括起但不包含正文字段的标题标签选择INTO OUTFILE?,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2611字,纯文字阅读大概需要4分钟。
内容图文
我不相信这个问题是重复的.
我希望正文中的行是“可选地用双引号括起来的”.不应包含数值.没有标题就很容易做到.但是,当您使用UNION包含标头时,MySQL现在将每个列视为字符串类型,并将所有值括在引号中.
您可以像这样向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`
同样,如果我省略标题,则只有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”之后结束.我只是放了第3行的一部分.
" 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
内容总结
以上是互联网集市为您收集整理的mysql – 如何使用引号括起但不包含正文字段的标题标签选择INTO OUTFILE?全部内容,希望文章能够帮你解决mysql – 如何使用引号括起但不包含正文字段的标题标签选择INTO OUTFILE?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。