Store The Results Of A Query In A File With MySQL
From Wiki
You are running MySQL and would like to store the output of a query in a file. How can you do this? You can use SELECT INTO OUTFILE to do this.
Here is how, first create this table- CREATE TABLE TestOutput(Col1 INT, Col2 VARCHAR(30),Col3 DATETIME);
Then insert these 3 rows
- INSERT TestOutput VALUES (1,'test1','20080903');
- INSERT TestOutput VALUES (2,'test2','20080904');
- INSERT TestOutput VALUES (3,'test3','20080905');
On a non Windows box you would run something like this:
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE '/tmp/result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
On a Windows box, run this:
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE 'result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
Run that code we just had above again. You see what happened? You got error 1086: File already exists. Another problem is that you don't know where this file will be created. Is should be in MySQL\MySQL Server 5.0\data\<your default schema>
Of course you can also specify the exact location, just make sure to escape the slashes
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE 'c:\\result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
To find more info about this check out the MySQL documentations 12.2.7. SELECT Syntax
Contributed By --SQLDenis 15:39, 3 September 2008 (GMT)
