MySQL query with an OUTFILE to csv not working when called from a batch file

MySQL

When I execute the following query, it works:

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

But when I call this query from the Windows command prompt or from a batch file using the following command:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/Stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"

it does not run. Here's what happens:

C:\Documents and Settings\188931>"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "
%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'foldrname/
orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe  Ver 14.14 Distrib 5.5.16,
 for Win32 (x86)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe [OPTIONS] [database
]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)

Another thing I noted is, when I run the following:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS';"

from the command prompt or a batch file, it does execute. (Note – I removed the csv export).

So it seems a combination of executing a query from batch file (or Windows command prompt) which is having a OUTFILE to csv is not working.

Could any body kindly help on this?

Best Answer

I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing. The solution is very simple: just escape the double quote in your SQL string:

... ENCLOSED BY '\"' ...

(I have to tell that on my local Windows box and psql (instead of mysql) client I don't have to escape the double quote.)

Related Question