The SELECT… INTO OUTFILE statement is meant to allow the server host to dump a table to a text file.
SELECT… INTO OUTFILE is the opposite of LOAD DATA. Column values are written in the character set supplied in the CHARACTER SET clause. If no such clause is present, values are dumped using the binary character set. In practice, there is no character set conversion. If a result set has columns in multiple character sets, so does the output data file, and it may be impossible to reload the file correctly.
SELECT… INTO OUTFILE is normally unsuitable for creating the resulting file on another host because there is no way to write a path to the file relative to the server host file system, unless the location of the file on the remote host can be accessed using a network-mapped path on the server host file system.
When you wish to dump all columns of a table into a text file, use INTO OUTFILE with a TABLE command. ORDER BY and LIMIT clauses must come before INTO OUTFILE to regulate the ordering and quantity of rows. TABLE… INTO OUTFILE provides the same export options as SELECT… INTO OUTFILE, and it is subject to the same file system writing limits. Here’s an example of such a statement:
Examples:
TABLE Emp ORDER BY EmpNo LIMIT 10
INTO OUTFILE ‘/tmp/emp_data.txt’
FIELDS TERMINATED BY ‘\t’
OPTIONALLY ENCLOSED BY ‘”‘, ESCAPED BY ‘\’
LINES TERMINATED BY ‘\n’;
TABLE tableName
INTO OUTFILE ‘path/output.csv’
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ESCAPED BY ”
LINES TERMINATED BY ‘n’;
SELECT * FROM Emp
INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/backupfile.sql’;
Hope you find this article helpful.
Big Data and SQL 