Exporting UTF-8 Query Output from MySQL
January 8, 2013 Leave a comment
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).
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.
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:
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.
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.