我正在使用一个shell文件导出数据库报告。如果我在PHPMyAdmin中运行查询,文件会正常生成,并且每行末尾只有换行符。
然而,当我在我的shell脚本中使用outfile生成文件时,某些列的内容中会出现/n、/r和/r/n。我无法确定是什么原因导致了这个问题,也不知道如何避免它。
这个问题似乎只在颜色列(在示例导出中为第三列)中出现。
查询内容如下:
然而,当我在我的shell脚本中使用outfile生成文件时,某些列的内容中会出现/n、/r和/r/n。我无法确定是什么原因导致了这个问题,也不知道如何避免它。
这个问题似乎只在颜色列(在示例导出中为第三列)中出现。
查询内容如下:
mysql $MYSQLOPTS << EOFMYSQL
SELECT Product_Name, Item_Size, Item_Colour, Item_Price, Current_Stock, Item_Price * Current_Stock AS Stock_Value
FROM Items
ORDER BY Product_Name
INTO OUTFILE '$FILE'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
EOFMYSQL
示例结果:
"Scarf_in_Peach","ONE SIZE","12/04-B2B2 ",10.00,3,30.00
"Scarf_in_Pink","ONE SIZE ","11/06-odds-C1C12100",10.00,0,0.00
"Scarf_in_Red","ONE SIZE ","11/06-B7B2-C1C12100",10.00,0,0.00
"Scarf_in_Sand_","ONE SIZE","11/06-B1I3-C1C12100
",10.00,0,0.00
"Scarf_in_Sand_/_Blue_Flowers","ONE SIZE","12/04-B2E2-C1C12100 ",10.00,4,40.00
"Scarf_in_Teal","ONE SIZE","11/06-B5G1-C1C12100
",10.00,0,0.00
"Scarf_in_Teal_/_Red_Flowers","ONE SIZE","12/04 - B2B2 ",10.00,1,10.00
"Sunrise_Skinnies","16","ODD-R1S009-1-BLUE",20.00,0,0.00
"Sunrise_Skinnies","8","ODD-R1S009-1
BLUE",20.00,0,0.00
\n
。 - davejalREPLACE(REPLACE(Item_Colour, CHAR(13), ' '), CHAR(10), ' ')
,似乎可以解决问题。虽然一个避免使用长字符串转义所有列的方法会更理想,但对于这个脚本来说已经足够了。谢谢。 - Chris Morris