How to Export MySQL Query Results to CSV Format in Linux

Querying from a MySQL database shell is always fun and techy until you need a database output saved somewhere for easy access and reference; especially when dealing with large datasets.

Quick data access saves you from having to each time log in to a MySQL server via a terminal shell to reference specific MySQL-query-associated outputs. The CSV (Comma Separated Value) file is an ideal candidate for resolving these types of repetitive user-to-database interactions.

CSV’s file format is best applicable for saving MySQL outputs because of its prominent attributes which include:

Its a widely acceptable comma-separated data saving format.
Its human-readable added advantage.
Its easy importation to any application because of its plain text nature.
Its adaptability in managing and organizing large datasets.

Prerequisites

The CSV file you will be associated with the MySQL query outputs should not yet exist as it will be auto-generated during the execution of a targeted MySQL query output.
Have root privileges on both the MySQL database and Linux system.

Creating Sample Database Table with Multiple Row Values

For this tutorial to be engaging and better understood, a database table with some values need to exist. For this tutorial, you can either be under MySQL or MariaDB RDBMS. Since MariaDB is an open-source fork of MySQL, these two RDBMS reference the same implementation of their database shell commands.

Log into your MySQL database as a root DB user or with an existing database user credential.

$ sudo mysql -u root -p

We will create a new database to host our new database table.

MariaDB[(none)]> show databases;
MariaDB[(none)]> create database lst_db;
MariaDB[(none)]> use lst_db;

Next, create the database with some tables as shown.

MariaDB[(none)]>  CREATE TABLE lst_projects(
	project_id INT AUTO_INCREMENT, 
	project_name VARCHAR(100) NOT NULL,
	project_category VARCHAR(100) NOT NULL,
	project_manager VARCHAR(100) NOT NULL,
	start_date DATE,
	end_date DATE,
	PRIMARY KEY(project_id)
);

Populate MySQL Database Table with Data

We have verified that our created MySQL database table does exist. It’s time to populate it with some data.

MariaDB[(none)]> show tables;
MariaDB[(none)]> INSERT INTO 
	lst_projects(project_name, project_category, project_manager, start_date, end_date)
VALUES
	('Marketing','AI','David Guitar','2021-08-01','2021-12-31'),
            ('Copy writing','AI','Viola Guin','2022-01-01','2022-03-31'),
            ('Modeling','Robotics','Mary Atkins','2023-04-01','2023-07-31'),
            ('API','ML','Duncan Reeves','2024-02-01','2024-06-20'),
	('Sales','ML','Anthony Luigi','2025-05-15','2025-11-20');

Let us confirm the existence of our lst_projects table values.

MariaDB[(none)]> SELECT * FROM lst_projects;

Exporting MySQL Query Results to CSV Format

The temporary directory “/var/tmp” gives MySQL the needed read and write privileges. We will use it to host all the CSV files auto-generated from MySQL queries.

Several conditions determine how we export a MySQL query result to a CSV file format.

Exporting All MySQL Queries to CSV

To export this db query “SELECT * FROM lst_projects;” into a CSV file, we would implement it in the following manner:

MariaDB[(none)]> SELECT * FROM lst_projects
INTO OUTFILE '/var/tmp/get_all_queries.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Let us try to retrieve the generated file:

Export MySQL Tables to CSV with Headers

This approach gives your generated CSV file a professional look.

MariaDB[(none)]> (SELECT 'Project Name','Project Category','Project Manager','Start Date','End Date')
UNION 
(SELECT project_name,project_category, project_manager, start_date, end_date
FROM lst_projects
INTO OUTFILE '/var/tmp/included_column_headings.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Let us again retrieve the generated file:

Export MySQL Queries to CSV with Headings

As noted, the MySQL query CSV export is now well organized with column headings.

Dealing with Null Values on Exported MySQL Queries

Let’s add a column that accepts Null values to our Database table lst_projects.

MariaDB[(none)]> ALTER TABLE lst_projects ADD COLUMN project_status VARCHAR(15) AFTER end_date;

We will not insert any values to this new column to ensure it remains empty. MySQL query exports with Null values are prerecorded with “"N” on the generated CSV file. To fix this issue, we can replace the “"N” value with something more relatable like “N/A”.

MariaDB[(none)]> (SELECT 'Project Name','Start Date','End Date','Project Status')
UNION 
(SELECT 
    project_name, start_date, end_date, IFNULL(project_status, 'N/A')
FROM
    lst_projects INTO OUTFILE '/var/tmp/with_null.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n');

Let us check the generated CSV file.

Check MySQL Queries to CSV with Null Values

Export MySQL Tables to CSV with Timestamp Filename

It creates a more accurate management routine in terms of when your CSV files were generated.

MariaDB[(none)]> SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '/var/tmp/';
SET @PREFIX = 'lst_projects';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM lst_projects INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
"  LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

The generated CSV filename should now have a timestamp.

Export MySQL Tables to CSV with Timestamp Filename

Outputting your MySQL query results to a CSV file is an efficient way of managing large datasets as it saves you both time and money especially when managing data for large organizations.