Well this rated high on my "Why didn't I think of that" factor, so I thought I'd share.

I wanted to run a query in MySQL  and save the results in a text file, I could write a script in Perl, PHP, Java etc to do this but saw this and liked it.

Code:

[johnm@linuxbox johnm]$ echo "select * from companies where something = \"No\"" | /usr/local/mysql/bin/mysql -uUSERNAME -pPASSWORD DB_NAME > /tmp/foo.txt;

The query is piped through to MySQL and redirected to a text file.

I liked it, hope it helps someone else too for a quick hack.

Note: I normally save text files from a query using 'dbish' but DBI.pm was not installed on the particular machine.

'dbish' is another goodie to look at.

When I get time I'll post a little discuss on it.

jfp

There's also an oprion on SELECT to redirect output to a file, but that will be a file on the machine on which the mysql daemon is running, and not the machine on which the client is running.  Of course, sometimes it's the same machine ...

Hello Graham

You can't just tease us with the mere mention of simpler way of doing this.
Any chance of an example query?

jfp

Sure, how about

Code:

select * into outfile "/tmp/openshow"  from lisbon;

Which will give you a tab separated file, with \ protection on special charcters.   There are FIELDS SEPARATED BY and other modifiers available too, and if you say "dumpfile" not "outfile", you'll get binary data in a single line.

Sorry to be a biot obtuse before, but I was away and didn't have all the books with me.   Internet access has been from a cafe, with 6 PCs sharing a modem or ISDN line and most computers there in use by the younger generation playing network games - it's been like trying to make a phone call in a war zone  

i am assuming that using the SELECT to output file method mentioned will create a file if not present and overwrite it if it is present.  am i right?  is there a way to append?  is there a way to do the opposite?  INSERT from a textfile?


thanks so much, 4est.

The output file CANNOT already exist.  This is done to prevent users overwriting / modifying system files.  You must also have file privilege on thge server.

---