MySQL Cookbook, 2nd Edition

Writing Your Own Export Programs

Problem

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

Solution

Write your own utilities.

Discussion

When existing export software doesn’t do what you want, you can write your own programs. This section describes a Perl script, mysql_to_text.pl, that executes an arbitrary statement and exports it in the format you specify. It writes output to the client host and can include a row of column labels (two things that SELECT ... INTO OUTFILE cannot 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. You can find mysql_to_text.pl in the transfer directory of the recipes distribution.

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 that module has been installed, you can read its documentation like so:

%perldoc Text::CSV_XS

This module is convenient because it makes conversion of query output to CSV format relatively trivial. All you have to do is provide an array of column values, and the module packages 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, ...