MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Data Back Up / Export Data / Question No: 172

How to use SELECT INTO OUTFILE statement to export data?

I want to use MySQL SELECT INTO OUTFILE statement to export my table data. Can you help me to explain, how to use SELECT INTO OUTFILE statement to export data?

Answer No: 172

The SELECT INTO OUTFILE SQL statement is actually a variant of the SELECT query. It is used when you want to direct query output to a text file. This file can then be opened by a spreadsheet application, or imported into another database like Microsoft Access, Oracle, or any other software that supports delimitation. Its general syntax format follows:

SELECT [select options go here] INTO {OUTFILE | DUMPFILE} filename
EXPORT_OPTIONS
FROM table_references [additional select options go here]

Key Command Options of SELECT INTO OUTFILE

  • OUTFILE: Selecting this option causes the query result to be output to the text file. The formatting of the query result is dependent upon how the EXPORT OPTIONS are set.
  • DUMPFILE: Selecting this option over OUTFILE results in the query results being written as a single line, omitting column or line terminations. This is useful when exporting binary data such as a graphic or a Word file. Keep in mind that you cannot choose OUTFILE when exporting a binary file, or the file will be corrupted. Also, note that a DUMPFILE query must target a single row; combining output from two binary files doesn’t make any sense, and an error will be returned if you attempt it.
  • EXPORT OPTIONS: The export options determine how the table fields and lines will be delimited in the outfile. Their syntax and rules match exactly those used in LOAD DATA INFILE.

Important Tips About usage of SELECT INTO OUTFILE

  • If a target file path is not specified, the directory of the present database is used.
  • The executing user must possess the selection privilege (SELECT_PRIV) for the target table(s).
  • If a target file path is specified, the MySQL daemon owner must possess adequate privileges to write to the target directory.
  • One unexpected side effect of this process is that it leaves the target file world-readable and -writeable. Therefore, if you’re scripting the backup process, you’ll probably want to change the file permissions programmatically once the query has completed.
  • The query will fail if the target text file already exists.
  • Export options cannot be included if the target text file is a dump file.

SELECT INTO OUTFILE - Simple Data Export Example

Suppose you want to export books data to a tab-delimited text file, consisting of lines delimited by newline characters:

SELECT * INTO OUTFILE "/backup/books/allbooks.txt"
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM books;

Related MySQL FAQs to the Above FAQ

How-to-export-table-data-to-text-file-with-SELECT-INTO-OUTFILE-statement How to export table data to text file with SELECT INTO OUTFILE statement?

How-to-export-table-data-to-Microsoft-Excel-with-SELECT-INTO-OUTFILE-statement How to export table data to Microsoft Excel with SELECT INTO OUTFILE statement?

How-to-use-mysqldump-to-export-data How to use mysqldump to export data?

How-to-take-dump-of-database-without-its-data How to take dump of database without its data?

How-to-take-dump-of-the-database How to take dump of the database?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.