MySQL change field separator for select dump

You can select from a table into an outfile, and change the field delimiter. This is interactive, and you must be logged in locally to the mysql server, with write permissions to wherever you want to write the file.

mysql> SELECT 'pid','email' UNION SELECT pid,email INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' FROM table;
Query OK, 11837 rows affected (0.21 sec)

mysql> Bye
# head /tmp/test.csv
pid,email
1081603,user1@fordodone.com
888151,user2@fordodone.com
781,user3@fordodone.com
2307364,user4@fordodone.com
2286573,user5@fordodone.com
2212194,user6@fordodone.com
2137603,user7@fordodone.com
500492,user8@fordodone.com
1514582,user9@fordodone.com

This is non-interactive and can be done from remote host:

# echo "select pid,email from table;" | mysql -h dbhost -udbuser -p -Ddbname | awk '{for (i=1;i<NF;i++) printf "%s,",$i; printf $i"\n"}' > /tmp/test.csv

Leave a Reply

Your email address will not be published. Required fields are marked *