Exporting MySQL data into CSV using command line client

Submitted by boaz on Friday, February 1, 2008.

I've recently needed to export some MySQL table data into a file.
Sure, there are gazillion ways to do this. I wanted to do this with as simple as possible tools - command line, that will be available always, and in a way which is not too awkward yet still powerful enough while done in a resource-conserving way.

Another requirement I should note is that its all done on a Linux server, meaning I have shell, command line and its needed tools (vi, scp...).

So I found some nice way (using several references to help me). I've decided to blog it for future generations smiley (and for my own future reference wink) . Here I summarize just the command. I'm sure most of you who come to need such a thing will know how to manipulate it to your needs.
 

Here Goes:
SELECT a.title, users.name, users.mail FROM users INNER JOIN (SELECT title, uid FROM node WHERE type='user_sales_data') a ON users.uid = a.uid INTO OUTFILE '/tmp/data' FIELDS ENCLOSED BY '"' TERMINATED BY ',';

 

Oh yes, the example above is from a Drupal installation and involves a "core" tables and one custom table (CCK). The query itself contains a subquery and a join.

Hope you find it useful,
Boaz.

Leave a Comment

Fields with * are required.