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.
- 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:
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
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.
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.
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.