MySQL: how to export query-results to flatfile

To save the result of a query in mysql to a file you can do this:

  1. save the query-string in an inputfile (e.g.: “SELECT * FROM t1;“)
  2. execute the query via mysql-console:
    mysql -u username -pPWD dbname < inputfilename > outputfilename

You now have the output of the query tab-delimited with columnnames in the given outputfile.

You could also pass the query as a parameter to mysql:

mysql -u username -pPWD dbname -B -e “SELECT * FROM t1;” > outputfilename

The -B tells mysql to execute the query in batchmode and terminate afterwards. As obviouse the -e stands for “execute the following query”.

This way is easier than the first one but now you have to take care of masking the query-string if needed.

Alternatively you can use the “SELECT … INTO OUTFILE …”-directive in mysql. This also gives you the possibility to define another field- or line-terminator as shown in this example copied from the mysql-manual:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

To use this your mysql-user-account needs the FILE-privilege. Also the file can only be created on the mysql-server-host and there only in a directory where your mysql-process has write-access on.

To get the same result without the given restrictions you can do some sed-operations on the output as shown here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: