Exporting UTF-8 Query Output from MySQL

I recently needed to export the results of a multi-table query from MySQL to a tab-delimited text file.  For someone such as myself that has only basic knowledge of MySQL, there were a couple of challenges to this seemly simple task.  The first challenge was handling the size of the query result set (millions of rows), and the second was preserving the data as UTF-8.

It took me about three attempts to get this right, so I thought I’d describe the solution here for future reference (and for anyone else who finds it useful).

STEP 1

Start by opening a Windows command prompt and navigating to the MySQL tools folder.  NOTE:  If your system’s PATH environment variable includes the path to the MySQL tools, you simply need to open a command prompt.

STEP 2

In order to preserve any Unicode characters in the query output, you must specify the correct code page before executing the query. To do this, execute the following command at the Windows command prompt:

chcp 65001

The chcp command will CHange the Code Page to code page 65001, which is the UTF-8 code page on Windows. By changing the code page, you ensure that when the output is redirected to a file, that file will be created as UTF-8.

Note that encodings are handled rather strangely on Windows; a detailed description of this can be found in the preferred answer to the question posed at http://stackoverflow.com/questions/1259084/what-encoding-code-page-is-cmd-exe-using. Note also that one of the final comments found there is “… the encoding that is used when cmd is redirecting stuff to a file… closely follows chcp”. Since we are in fact redirecting output to a file, we can safely ignore most of the encoding strangeness.

STEP 3

Now you are ready to issue the command that will export the data.  The command to use is:

mysql SCHEMANAME –q –uUSER –pPASSWORD –e “SELECT * FROM TABLENAME” > OUTPUTFILE.TSV

The following table explains the elements of the export command:

SCHEMANAME The name of the MySQL schema/database to query.
–q Prevents MySQL from caching the query results before outputting it.  This is important if your query returns a large amount of data and/or your system memory is limited.
-uUSER A MySQL user that has permission to execute the query.
-pPASSWORD The password for the MySQL user.
-e “SELECT * FROM TABLENAME” The query to be executed.  The actual query used can be as simple as the example given here, or it can be much more complex.  By default, results are printed using the tab as the column separator.  Each row is listed on a new line.
> OUTPUTFILE.TSV Redirects the tab-separated output of the query to a file named OUTPUTFILE.TSV.

 

For tips on producing output formats other than tab-separated, see http://giantdorks.org/alain/export-mysql-data-into-csv-or-psv-files/ for more information.

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: