I'm often running a single query that produces a set of results that I would like to export into a CSV.
For example, in a query against the url_alias table, I will want to grab the following and have it export into a spreadsheet rather than
mysql> SELECT source,alias FROM url_alias WHERE alias LIKE "library%";
+-----------+----------------------------------------------------------------+
| source | alias |
+-----------+----------------------------------------------------------------+
| node/1000 | library |
| node/1019 | library/map
.. etc
+-----------+----------------------------------------------------------------+
To start with I'll need to check what my permissions are on my target MySQL instance and I can do that with this query from the command line which checks the value of secure_file_priv:
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
That shows me what the value for that system variable is currently -- if its set to a value that I like, I'm fine and can move to the next step, but if its NULL or if I want to change it to another directory, I'm going to need to edit the my.cnf file and add the following:
secure_file_priv="MY_DIRECTORY"
Now I can take my query and add the following magic which will wrap the output of my query into a file, exported to my filesystem:
INTO OUTFILE 'MY_DIRECTORY/myexport.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
So my final combined query looks something like this:
SELECT source, alias
INTO OUTFILE 'MY_DIRECTORY/myexport.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM url_alias
WHERE alias LIKE "library%";