MySQL Export using INTO OUTFILE

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)

 

Common Error

We generally get this error when we to export to arbitrary directory. The server that runs with the  –secure-file-priv option.

mysql> SELECT * INTO OUTFILE ‘Country.txt’ FROM Country ;

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Command to check the variable:

mysql> SHOW VARIABLES LIKE “secure_file_priv”;
+——————+————————————————+
| Variable_name | Value |
+——————+————————————————+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+——————+————————————————+
1 row in set (0.01 sec)

We can export to a file to the directory specified in the variable.

 

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/