shell - MySQL INTO OUTFILE issue with new lines in content -

i'm exporting database report shell file. if run query in phpmyadmin file comes out fine, new lines @ end of each row in database only.

however when run query in shell script using outfile generate file /n, /r , /r/n in of columns content. can't work out causes or how avoid it.

the issue seems caused in colour column third in example export.

query:

mysql $mysqlopts << eofmysql select product_name, item_size, item_colour, item_price, current_stock, item_price * current_stock stock_value items order product_name outfile '$file' fields terminated ',' optionally enclosed '"' lines terminated '\n' eofmysql 

example result:

"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 

you have 2 options:

1. replace carriage return , line feed characters empty string within query. pro: characters filter out , fields. con: have create expression each affected field manually.

2. use fields escaped character option of select ... outfile ... command:

fields escaped controls how write special characters. if fields escaped character not empty, used when necessary avoid ambiguity prefix precedes following characters on output:

• the fields escaped character

• the fields [optionally] enclosed character

• the first character of fields terminated , lines terminated values

• ascii nul (the zero-valued byte; written following escape character ascii “0”, not zero-valued byte)

the fields terminated by, enclosed by, escaped by, or lines terminated characters must escaped can read file in reliably. ascii nul escaped make easier view pagers.

pro: fast , standard approach, can apply export functionality using approach. con: less flexible. example, if lines terminated option set \n, \r not going escaped, can still cause issues on systems.