This is done using the INTO OUTFILE command like so.
mysql> select emp_id, emp_name from emps
into outfile 'c:/test.txt';
Query OK, 4 rows affected (0.03 sec)
This simply produces the file without any fuss, each column output as is in the table.
With SPOOL we can manipulate the file by simply formatting the output of the SQL, so if we wanted to output each column in the table with a comma delimiter we would have to concatenate the values together in the SQL to produce the required result. In MySQL there are a number of options we can use with INTO OUTFILE to change the way data is written to the file. These are FIELDS ESCAPED BY, FIELDS ENCLOSED BY, FIELDS TERMINATED BY and LINES TERMINATED BY. So if we wanted our output to be enclosed in double quotes, with comma delimiters and a new line termination for each row we would use...
SELECT emp_id, emp_name
INTO OUTFILE 'c:/result.text'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM emps;
This would produce the following file.
"0","Barry"
"1","Paul"
"2","John"
"3","Alan"
There are of course some advantages to both, you can't for example continue to output to the same file across multiple results when using INTO OUTFILE, which you can using spool, but when using INTO OUTFILE you can concentrate on what you want selected and let MySQL deal with the formatting of the output rather than having to write it long hand when using SPOOL.
