MySQL中的INTO OUTFILE问题：内容中有换行符

Question

我正在使用一个shell文件导出数据库报告。如果我在PHPMyAdmin中运行查询，文件会正常生成，并且每行末尾只有换行符。

然而，当我在我的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

- Chris Morris

---

你测试过数据以查看字段中是否实际存在空格（即\n）吗？我问这个是因为最后我看到一些记录有空格，也许你也有\n。 - davejal

在你的查询中，你可以用空字符串替换回车和换行字符。 - Shadow

你能看一下这个问题吗？ - davejal

@davejal，我在那个问题中没有看到任何与列内容中的额外换行有关的信息。 - Chris Morris

@Shadow，如果我在选择语句中将颜色列更改为REPLACE(REPLACE(Item_Colour, CHAR(13), ' '), CHAR(10), ' ')，似乎可以解决问题。虽然一个避免使用长字符串转义所有列的方法会更理想，但对于这个脚本来说已经足够了。谢谢。 - Chris Morris

Answer 1

你有两个选项：

1. 在查询中使用空字符串替换回车和换行符。优点：完全由您决定过滤哪些字符以及从哪些字段中过滤。缺点：必须手动为每个受影响的字段创建表达式。

2. 使用 SELECT ... INTO OUTFILE ... 命令的 FIELDS ESCAPED BY character 选项：

FIELDS ESCAPED BY 控制如何编写特殊字符。如果 FIELDS ESCAPED BY 字符不为空，则在必要时用作前缀，以避免输出的后续字符产生歧义：

- FIELDS ESCAPED BY 字符 - FIELDS [OPTIONALLY] ENCLOSED BY 字符 - FIELDS TERMINATED BY 和 LINES TERMINATED BY 值的第一个字符 - ASCII NUL（零值字节；实际上在转义字符后面写入的是 ASCII“0”，而不是零值字节）

必须转义 FIELDS TERMINATED BY、ENCLOSED BY、ESCAPED BY 或 LINES TERMINATED BY 字符，以便您可以可靠地读取文件。为了更容易使用某些分页器查看，ASCII NUL 被转义。

优点：这是一种快速和标准的方法，您可以轻松地使用此方法应用于所有导出功能。缺点：灵活性较差。例如，如果将行终止选项设置为\n，则\r不会被转义，这仍然可能在某些系统上引起一些问题。

- Shadow