The simplest way of exporting a table data into a text file is by using the SELECT...INTO OUTFILE statement that exports a query result directly into a file on the server host.
The syntax for this statement combines a regular SELECT command with INTO OUTFILE filename at the end. The default output format is the same as it is for the LOAD DATA command. So, the following statement exports the tutorials_tbl table into /tmp/tutorials.txt as a tab-delimited, linefeed-terminated file.
mysql> SELECT * FROM tutorials_tbl -> INTO OUTFILE '/tmp/tutorials.txt';
You can change the output format using various options to indicate how to quote and delimit columns and records. To export the tutorial_tbl table in a CSV format with CRLF-terminated lines, use the following code.
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n';
The SELECT ... INTO OUTFILE has the following properties −
The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
The output file must not already exist. This prevents MySQL from clobbering files that may be important.
You should have a login account on the server host or some way to retrieve the file from that host. Otherwise, the SELECT ... INTO OUTFILE command will most likely be of no value to you.
Under UNIX, the file is created world readable and is owned by the MySQL server. This means that although you will be able to read the file, you may not be able to delete it.
To export a table in SQL format to a file, use the command shown below.
The FIELDS clause can specify other format options besides TERMINATED BY. By default, LOAD DATA assumes that values are unquoted and interprets the backslash (\) as an escape character for the special characters. To indicate the value quoting character explicitly, use the ENCLOSED BY command. MySQL will strip that character from the ends of data values during input processing. To change the default escape character, use ESCAPED BY.
When you specify ENCLOSED BY to indicate that quote characters should be stripped from data values, it is possible to include the quote character literally within data values by doubling it or by preceding it with the escape character.
For example, if the quote and escape characters are " and \, the input value "a""b\"c" will be interpreted as a"b"c.
For mysqlimport, the corresponding command-line options for specifying quote and escape values are --fields-enclosed-by and --fields-escaped-by.