LOAD DATA INFILE is the complementary of SELECT INTO OUTFILE.
When you use LOAD DATA INFILE, you can either load from a file resides on the same box as MySQL server, and also, you can specify the LOCAL INFILE so you can load a file from client file system to load the data into a table on the remote side.
However, when you use SELECT INTO OUTFILE, there is no option like SELECT INTO LOCAL OUTFILE where you can specify to write the file into local. In another way, you can only load the file to the remote server, and if you don’t have access to the filesystem, then you can not access the file.
“If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host’s file system.”
They mentioned a few approaches to work around that limit, like `mysql -e “select from..” > mylocalfile`, which requires the mysql client to be installed on the client side, which is the equivalent of exporting the select results in my sequel pro.
Beyond the discussion about if you can read/write SQL result to either remote/local filesystem, there is a format clause where you might need to pay attention to. The syntax for the COLUMNS and LINES clauses is the same for both SELECT INTO OUTFILE and LOAD DATA INFLE.
Those formats are optional and the value would be like below when they are omitted.
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY ''
Which might be different from the formats of the client software that you were using. For example, here is the format when I export from my Sequel Pro client.
And here are a few examples that showed the output of testing.
Lesson, if you don’t pay attention to the column format before you load data, you might have the command run successfully, and the loaded data is completely messed up.
datafireball 