TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

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

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 Secure File Priv

—

MySQL Tutorials

MySQL Tutorials on this website:

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

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL PI() Function› MySQL mysqldump Export

Recent Posts

  • MS Access Data Types
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version