MySQL Cookbook

Writing Your Own Export Programs

Problem

MySQL’s built-in export capabilities don’t suffice.

Solution

Write your own utilities.

Discussion

When existing software doesn’t do what you want, you can write your own programs to export data. This section shows how to write a Perl script, mysql_to_text.pl, that executes an arbitrary query and exports it in the format you specify. It writes output to the client host and can include a row of column labels (neither of which SELECT ... INTO OUTFILE can do). It produces multiple output formats more easily than by using mysql with a postprocessor, and it writes to the client host, unlike mysqldump, which can write only SQL-format output to the client.

mysql_to_text.pl is based on the Text::CSV_XS module, which you’ll need to obtain if it’s not installed on your system. Once it’s installed, you can read the documentation like so:

% perldoc Text::CSV_XS

This module is convenient because all you have to do is provide an array of column values, and it will package them up into a properly formatted output line. This makes it relatively trivial to convert query output to CSV format. But the real benefit of using the Text::CSV_XS module is that it’s configurable; you can tell it what kind of delimiter and quote characters to use. This means that although the module produces CSV format by default, you can configure it to write a variety of output formats. For example, if you set the delimiter to tab and the quote character to undef, Text::CSV_XS generates ...