Three different ways to create a CSV with MySQL

There are 2 well known ways to create a CSV using MySQL. But there is a technique I’ve been using for many years that most blogs and forums don’t talk about. I do believe it’s worth showing the technique I use since it’s faster.

First, there is the “standard” way:

The second technique is by using mysql client and sed

And the technique I use is by using the CSV storage engine.

I now have a file named test_csv_table.CSV in my MySQL datadir! If you don’t know where is your datadir, use the statement:

Obviously, it requires to have the privileges to read the mysql datadir. Also, keep in mind the CSV engine doesn’t support nullable columns.

What I like with that third technique is I don’t have to remember the complex syntax of the other techniques. It’s pretty straight forward.