A lot of times, developers are asked to export data from a MySQL database to hand over to a client. In most of the cases, when tools like phpMyAdmin are available, the data is exported through the web tool.
But when no such tools are at hand, developers most likely turn to their developers skills and start scripting away.
What strikes me is that not a lot of developers know that you can export to CSV directly from the MySQL command line. And quit easy if I might add.
Take the example below:
SELECT field1, field2 FROM table1 INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' FIELDS ESCAPED BY '\' LINES TERMINATED BY '\n';
So lets break it down into pieces. The first part before the INTO line is your query. This can be a simple SELECT query, but you can add conditions and joins, to retrieve the data-set you require.
After you SELECT query, you start setting your CSV definition:
INTO OUTFILE : here you state the path where you want MySQL to store the CSV file. Keep in mind that the path needs to be writeable for the MySQL user
FIELD TERMINATED BY ‘,’ : this is the field delimiter character. Some prefer a semi-colon instead of a comma.
ENCLOSED BY : the character you use to enclose the fields in.
FIELDS ESCAPED BY: the escape character used in case of special characters in your output
LINES TERMINATED BY: here you define the line-end.
In your output CSV you might see values as \N when NULL values are present in your data-set. This is a good thing if you want to re-import that CSV file at a later stage.
You can achieve this by using the following command:
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table1;
Keep in mind that the CSV NULL value ( \N) will only be recognized as a NULL value when you use the backslash as a FIELD ESCAPED character.
For more detailed information, I recommend you check the corresponding MySQL documentation for the INTO OUTFILE and LOAD DATA INFILE syntax.