How to Export MySQL Data to CSV

Introduction

Not many applications read raw MySQL tables. On the other hand, a CSV file is versatile and functions with different database software and programs.

CSV is a standard database format in which a comma separates values between rows and columns. The added benefit of CSV files is that they are human-readable.

This detailed guide will show you how to export MySQL data to a CSV file.

Prerequisites

• Access to a command line/terminal window.
• A user with sudo or root access.
• A MySQL user account with root privileges.
• Preconfigured phpMyAdmin account (optional).

Export MySQL to CSV with phpMyAdmin

phpMyAdmin provides a GUI for managing MySQL databases. It contains various administration functionalities, including exporting databases to CSV. Follow the steps below:

1. Log in to phpMyAdmin using your credentials.

2. Click the Databases tab on the top banner.

3. Click the database name.

4. The following screen shows the tables in that database. Check the boxes next to the tables you want to export.

Alternatively, check all the tables to export the whole database.

5. Expand the With selected drop-down menu and choose Export.

6. Open the Format drop-down menu, select CSV, and then click Go.

The file is saved to the downloads location of the browser (typically the Downloads directory).

Export MySQL to CSV via Command Line

Another way to export to a CSV file is through a MySQL statement. Follow the steps below:

1. Open the MySQL shell in the terminal:

mysql -u [user] -p

Enter the user's password when prompted. The prompt changes to the MySQL shell.

2. Switch to the desired database:

USE [database_name];

3. See the following example to export data to a CSV file:

SELECT * FROM [table_name]
INTO OUTFILE '[path].csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Replace [table_name] with the table's actual name. Provide the desired location ([path]) and file name, ensuring it ends with .csv.

4. If the previous statement shows an error, the server is set up to limit the directory where files can be loaded.

Use the following statement to see which directory is set as the default:

SHOW VARIABLES LIKE "secure_file_priv";

Use the output path and rerun the export statement from the previous step.

Export and Timestamp CSV File

Follow the steps below to add a timestamp to the file name during export:

1. Switch to the desired database:

USE [database_name];

2. Set the timestamp and directory path in variables:

SET @TS = DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%s');
SET @FILENAME = CONCAT('[path]', @TS, '.csv');

The variables contain the following:

• SET @TS. Sets the timestamp in the %Y_%m_%d_%H_%i_%s format using the DATE_FORMAT function. Adjust the timestamp if a different format is required.
• SET @FILENAME. Concatenates the directory path ([path]), the timestamp (@TS), and the .csv file extension. The path can contain a filename prefix. Ensure that the MySQL server has permission to load files to the location (use SHOW VARIABLES LIKE "secure_file_priv"; to check).

3. Construct the SQL query:

SET @SQL = CONCAT(
    'SELECT * FROM [table_name] INTO OUTFILE \'', @FILENAME, '\' ',
    'FIELDS ENCLOSED BY \'"\' ',
    'TERMINATED BY \',\' ',
    'ESCAPED BY \'"\' ',
    'LINES TERMINATED BY \'\\r\\n\' '
);

The statement creates a variable that concatenates the previously generated file name with the SQL query to select a table.

4. Prepare the SQL statement:

PREPARE stmt FROM @SQL;

5. Execute the statement:

EXECUTE stmt;

The CSV file appears in the previously provided location.

6. Deallocate the statement:

DEALLOCATE PREPARE stmt;

The prepared statement is now empty.

Export with Column Headers

Use a UNION ALL statement to add column headers to the exported file:

SELECT '[column_1]', '[column_2]', '[column_3]'
UNION ALL
SELECT [column_1], [column_2], [column_3]
FROM [table_name]
INTO OUTFILE '[path].csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

The query uses the UNION ALL statement to combine two SELECT statements. The first statement creates the column headers, and the second selects the table data.

The exported CSV file contains the column names in the first row.

Dealing with NULL Values

If there are null (empty) values in the database, this process exports them as \N. The following example shows how to replace the null values with another string:

SELECT [column_1], [column_2], IFNULL([column_3], 'N/A')
FROM [table_name] INTO OUTFILE '[path].csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n';

In this case, the IFNULL function checks for empty values in the provided column.

If found, it replaces them with the text string N/A.

Export MySQL to CSV Using mysqldump

The mysqldump CLI tool helps export a MySQL database to a CSV file. Enter the following command into a command prompt using your information:

sudo mysqldump -u [username] -p [database_name] --tab=[path] --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' [table_name]

The command prompts for the MySQL user password before execution. It creates two files: a .sql file with the database schema and a .txt file in CSV format.

To change the file extension, use the mv command:

sudo mv [path].txt [path].csv

The mv command can also rename the file in the same step or move it to another location.

Export MySQL to CSV Using CSV Engine

Another way to export to CSV is to change a table's storage engine. However, this method won't work if a MySQL table has an index, contains null values, or uses auto-increment (in those cases, use one of the other methods).

To change a table's storage engine, run:

ALTER TABLE [table_name] ENGINE=CSV;

This statement changes the storage engine to CSV and converts the MySQL database file to CSV. To find the file's location, run:

SHOW VARIABLES LIKE 'datadir';

The MySQL server stores all database files in the directory shown in the output. To see the file, use the ls command and add the database name to the path.

Export MySQL to CSV Using MySQL Workbench

MySQL Workbench contains a convenient export wizard program for exporting MySQL database tables into different formats, including CSV.

To export using MySQL workbench, do the following:

1. Right-click the table name and choose Table Data Export Wizard.

The action opens the export wizard interface.

2. Select the columns to export and click Next to proceed.

3. Enter the file path in the appropriate field and provide the file name at the end. Optionally, adjust the export options and select whether to export the file to the local machine. Click Next to proceed.

4. The following screen shows the tasks that will be performed (preparing the export and exporting data to a file). Click Next again to execute the operation.

5. The wizard completes the export tasks. Click the Show Logs button to view the export logs, or click Next to continue.

6. The final screen shows the file location and the time taken to export.

7. Click Finish to complete the process.

Conclusion

This guide showed several different ways to export MySQL data into CSV files. Use a method that best fits your use case, table data, and environment.

Next, learn how to import CSV into a MySQL database.