How to Export Table Data in MySQL

Data export is the process of transferring data from an application to an external file in a specified format or to another software. The exported data can then be used by different applications or shared with users. A key aspect of data export is format conversion - ensuring compatibility with other programs, and making it accessible across various applications and platforms.

Many work scenarios require exporting data in different formats, and multiple methods are available to achieve this. The main differences lie in the number of supported formats and the ease of configuring the export process. In this article, we will explore ways to export data from MySQL database tables.

Overview of MySQL data export options

In MySQL, you can export data using several methods:

• mysqldump - a command-line utility that allows users to export entire databases or specific tables in SQL or other formats.
• SELECT INTO OUTFILE - an SQL statement that writes query results directly to a file in CSV or TSV format.
• GUI tools - visual tools that provide flexible options for selecting and exporting data.

Let's explore these options in MySQL.

Using mysqldump for data export

mysqldump is a command-line tool provided by MySQL that allows users to export individual tables, entire databases, or even full servers. It is commonly used for database backup and recovery as it creates logical backups that can be imported into other MySQL servers.

To use mysqldump, you need access to the MySQL server along with valid credentials, such as a username and password.

Basic mysqldump commands

The basic syntax for using mysqldump to export an individual MySQL table is as follows:

mysqldump -u<username> -p<password> database_name table_name > dumpfile_name.sql

Parameters:

• username and password - your credentials to connect to a MySQL server.
• database_name - the name of the database to dump.
• table_name - the name of the table to dump.
• dumpfile_name.sql - the name of the dump file.

Assume we want to export the full film table from the sakila database (we will use this popular test database to illustrate our cases).

mysqldump -u root -p sakila film > "D:\sakila_scripts\film_backup.sql"

The result is a SQL file containing the CREATE TABLE script to reproduce the film table and the INSERT script to populate the table with data.

When we execute that SQL file, we have a full copy of the film table with all its data in a database.

Exporting only the data without the table structure

In many cases, we need to export data from a table without including its structure. The previous example showed how to export both the table structure and its data. However, mysqldump also allows us to export only the data using the --no-create-info option.

The command syntax is:

mysqldump -u username -p --no-create-info database_name table_name > datafile.sql

For instance, we need to export data only from the film table. The command is:

mysqldump -u root -p --no-create-info sakila film > "D:\sakila_scripts\film_data_only.sql"

This command exports data only from the film table.

The output is a SQL file containing the INSERT statement, and using the --no-create-info option ensures that the target table structures remain unchanged when we execute that SQL file to import data into a table.

Customizing data export with filters

Using mysqldump allows us to export a part of query results filtered based on a specified condition. It can be achieved with the help of the --where option.

The command syntax is as follows:

mysqldump -u username -p database_name table_name --where="condition" > datafile.sql

Assume we want to export the list of films with the PG-13 rating from the film table. We use the below command:

mysqldump -u root -p sakila film --where="rating = 'PG-13'" > "D:\sakila_scripts\film_PG-13.sql"

We can see the result SQL file with the list of those films.

This method applies to table data only, not the table structure.

Though mysqldump is a popular tool, it has its limitations. Let us consider another option, which is using the SELECT INTO OUTFILE command.

Using SELECT INTO OUTFILE

In MySQL, the SELECT...INTO OUTFILE statement is a variation of the SELECT statement that exports data from a table into a TXT, CSV, or TSV file. Simply put, it saves the query result directly to a file.

This method is especially useful when you need to perform complex operations on the data, such as joins, aggregations, and filtering, and export the results of that complex calculation.

Simple SELECT INTO OUTFILE syntax

The basic syntax of the query with the SELECT...INTO OUTFILE statement is as follows:

SELECT column1, column2, ...
INTO OUTFILE '/path/to/output_file.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE conditions;

Parameters:

• column1, column2...columnN - the columns to extract data from.
• '/path/to/output_file.csv' - the path where the file should be created and the output file name.
• FIELDS TERMINATED BY ',' - the delimiter between fields.
• ENCLOSED BY '"' - double quotes to wrap rows and handle values with delimiters.
• LINES TERMINATED BY '\n' - the line break character (typically \n for Unix-like systems, \r\n for Windows).
• FROM table_name - the table from which data is being exported.
• WHERE conditions - the data filtering conditions (optional).

Let us explore how this statement works. We'll use dbForge Studio for MySQL, a powerful and feature-rich integrated development environment (IDE) for MySQL and MariaDB. This tool supports all database tasks in these systems, including constructing, formatting, and executing queries against the specified databases.

Assume we need to export data from the customer table of the sakila database.

SELECT
  first_name,
  last_name,
  email INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 9.1\\Uploads\\customer.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customer;

Another use case requires exporting the list of action films only and we want to save it in TXT format. This requires a more complex query with JOIN clauses.

SELECT
  f.film_id,
  f.title,
  f.description,
  f.release_year,
  f.rating,
  f.length INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 9.1\\Uploads\\new_action_films.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM film f
  JOIN film_category fc
    ON f.film_id = fc.film_id
  JOIN category c
    ON fc.category_id = c.category_id
WHERE c.name = 'Action';

After retrieving the list of such movies, the output has been saved as an external CSV file.

Formatting data in exported files

When we use the INTO OUTFILE clause, we can export data from MySQL tables in different formats. The default format is CSV, where tabs are used to separate values, and those values aren't enclosed in quotes or other characters. It is possible to use custom formatting options to make the output data better suited for your needs.

The standard SELECT INTO OUTFILE query syntax includes the FIELDS TERMINATED BY 'delimiter' option, which defines how values in columns are separated. The default delimiters are:

• , (comma) - for CSV files
• \t (tab) - for TSV (Tab-Separated Values)
• | (pipe) - for structured text formats

Let us export the list of customers in TXT format using ; (semicolon) as a delimiter. The query is:

SELECT
  * INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 9.1\\Uploads\\customer-list.txt'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customer;

Handling file path and security concerns

The main issue with using SELECT INTO OUTFILE is the lack of necessary permissions to save output files to a specified folder. This happens because MySQL restricts file storage to specific directories.

In modern MySQL versions, attempting to save an output file to an unauthorized location using SELECT INTO OUTFILE results in the error:

The MySQL server is running with the secure_file_priv option so it cannot execute this statement.

To check the default directory where MySQL is allowed to save files on your system, run the below command:

SHOW VARIABLES LIKE "secure_file_priv";

Additionally, notice that data export and import operations require the FILE privilege. Also, SELECT INTO OUTFILE will fail if the target file already exists. To avoid errors, ensure that each output file has a unique name.

These methods of exporting data from MySQL tables are standard but lack many advanced options for data export and import that modern GUI tools offer. In the next section, we'll explore how GUI-based database management tools improve data migration operations.

Step-by-step guide to exporting table data in dbForge Studio

In the previous sections, we used dbForge Studio for MySQL to demonstrate the functionality of dedicated SQL queries. When it comes to data export and import, the Studio provides a powerful module that allows you to:

• Precisely select the data to be exported
• Choose from a wide range of supported output formats
• Execute the export operation seamlessly
• Schedule and automate recurring export tasks

Now, let us see how to export data from a MySQL database with the help of dbForge Studio for MySQL, step by step.

Connect to your MySQL database

dbForge Studio for MySQL provides a quick and simple way to connect to databases and manage connections visually.

Launch the Studio and navigate to Database > New Connection.

Or, you can click New Connection in the Database Explorer. Enter the connection properties: Host, Port, User, and Password. Optionally, choose the desired target database from the drop-down menu, and click Connect.

You can give the connection a name and mark it with color for your convenience.

Select the table for export

After connecting to the necessary database, go to Database > Tasks > Export Data.

You will be able to check the MySQL server connection, the database, and the table(s) or view(s) you want to export.

Choose export formats and options

dbForge Studio for MySQL allows you to export data from MySQL databases in 14 formats. In the Export format section, select the desired format. Click Next.

In the Output settings section, define the folder in which to store the exported data. By default, the data is exported into a single file, but you can choose to export it as multiple separate files. Click Next.

In the Options section, set additional settings for the selected export format. Click Next.

In the Data formats section, select columns for data export and click Next.

In the Exported rows section, select rows for data export. You can export all rows, selected ones, or a specified range of rows. Click Next.

In the Error handling section, specify how the application should behave if an error occurs.

Click Export, and view the file when it is exported.

Save the export settings and automate the process

The data export task settings can be saved as a template file for future usage. Click Save > Save Template and specify a path and the name of the template file.

When you need to apply the already configured settings to a new job, just select the necessary template.

Also, you can automate regular data export tasks, as dbForge Studio supports the command line. When you configure the task, click Save > Save Command Line. The .bat file it creates can be used to schedule the task and run it automatically.

dbForge Studio for MySQL is a powerful tool that allows you to configure the data export and import tasks in several clicks, visually, and export data quickly, safely, to the necessary location, and in the desired format.

Conclusion

In this article, we explored various methods for exporting data from MySQL database tables into files, including the built-in mysqldump tool, dedicated SQL commands, and professional GUI tools. While each method has its supporters, it's hard to deny that GUI tools, such as dbForge Studio for MySQL, offer the most robust functionality and flexibility. The Studio allows users to configure export tasks according to their specific needs and conveniently save the exported data. And that's just one of the many powerful features available in dbForge Studio.

dbForge Studio for MySQL is an all-in-one solution for database development, management, analysis, and administration in MySQL and MariaDB. From writing code to version control, it simplifies every aspect of database work. Best of all, you can try the Studio risk-free with a fully functional 30-day free trial. Download it today, install it, and experience its full capabilities under real workloads!

FAQ