How to dump temporary MySQL table into a file?

Question

Is there a way to create a dump/export/save a temporary MySQL table into a file on disk(.sql file that is, similar to one that is created by mysqldump)?

Answer 1

Sorry, I did not read the question properly the first time around... at any rate, the best I can think of is using the SELECT ... INTO OUTFILE statement, like this:

SELECT * INTO OUTFILE 'result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM temp_table;

This does have many limitations thought, for instance, it only dumps the raw data without including the field headers. The other thing I found that may or may not be of use is the SHOW CREATE TABLE statement. If you can find some way of combining the output from these two statements, you may be able to get a proper "dump" file as produced by my command below.

---

You should be able to use the mysqldump application:

mysqldump --databases temptable > file.sql

This will dump the table with CREATE decelerations.