内容中出现新行的 MySQL INTO OUTFILE 问题

Question

我正在使用 shell 文件导出数据库报告。如果我在 PHPMyAdmin 中运行查询，文件会很好地显示出来，只有数据库中每行末尾的新行。

但是，当我使用 outfile 在我的 shell 脚本中运行查询以生成文件时，我在某些列的内容中得到了/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

Answer 1

你有两个选择:

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

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

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used when necessary to avoid ambiguity as a prefix that precedes following characters on output:

• The FIELDS ESCAPED BY character

• The FIELDS [OPTIONALLY] ENCLOSED BY character

• The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

• ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII “0”, not a zero-valued byte)

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

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