How 2 Guru

Export mysql select statement into csv

Today I would like to share some useful command for developer to export out data by using SQL statement.
I think most of the developer should familiar with the putty & command line environment.
But always facing an problem to export out the data in a excel/csv file in order to send it to customer.

Here is a simple but useful MySQL command to export out your data in a csv file.

Login into mysql and execute following statement. (For e.g. I am going to export out all the user’s name into a csv file)

SELECT user_name
INTO OUTFILE '/tmp/filename.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM users
WHERE status = 'active'

In order to perform the above statement, you need to have a root user or an user having the FILE permission.
Below is the grant command on how to grant a database user to have FILE permission.

GRANT FILE ON *.* TO 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD'

Due to the FILE permission only assigned as a GLOBAL permission like SHOW DATABASES.
So we can’t grant the permission to a specific database or table only.
I would always suggest to grant the FILE permission to the same db user (Since the db user should only have the permission on one database).
Or create another user just for this purpose.