MySQL Export using INTO OUTFILE

Overview

MySQL offers various methods for importing and exporting database data. In this tutorial, we will learn about MySQL INTO OUTFILE clause. This clause allows us to export table data with an SQL query.

Export with Query

A SELECT statement can be used with the INTO OUTFILE clause to write the results set directly to a file. We can place the INTO OUTFILE clause before the FROM clause in the SELECT query.

Use of the INTO OUTFILE changes the operation of the SELECT statement in several ways:

The file is written to the server host, not to the client that executes the statement
Data is written to a new file. We cannot overwrite an existing file
The export file contains one line per row.By default, column values are delimited by tab characters and lines are terminated with newlines.

Example

To export table contents of the Country table from the world MySQL database to a file name called ‘Country.txt’

The name of the file indicates the location where we want to export the table data. MySQL interprets the pathname for files located on the server host.

Issue the following SQL statement at a MySQL prompt:

mysql> SELECT * INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Country.txt’ FROM Country ;
Query OK, 239 rows affected (0.09 sec)

MySQL INTO OUTFILE