Importing and Exporting Data - MySQL - page 388

Discussion
When existing export software doesn't do what you want, write your own programs.
This section describes a Perl script, mysql_to_text.pl , that executes an arbitrary state‐
ment 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 rec
ipes distribution.
mysql_to_text.pl is based on the Text::CSV_XS module, which you must install on your
system if it hasn't been already. To read its documentation, use this command:
% perldoc Text::CSV_XS
This module is convenient because it makes conversion of query output to CSV format
relatively trivial. Your script need only provide an array of values, and the module pack‐
ages them into a properly formatted output line. This makes it relatively trivial to convert
query output to CSV format. But the real benefit of Text::CSV_XS is that it's configu‐
rable; you can tell it what 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 tab-delimited output. We'll take advantage of that flex‐
ibility in this section for writing mysql_to_text.pl , and in Recipe 11.6 to write
cvt_file.pl , a utility that converts files from one format to another.
mysql_to_text.pl accepts several command-line options. Some are used for specifying
MySQL connection parameters (such as --user , --password , and --host ). You're al‐
ready familiar with these because they're used by the standard MySQL clients like
mysql . The script also can obtain connection parameters from an option file, if you
specify a [client] group in the file. In addition, mysql_to_text.pl accepts the following
options:
--execute= query , -e query
Execute query and export its output.
--table= tbl_name , -t tbl_name
Export the contents of the named table. This is equivalent to using --execute to
specify a query value of SELECT * FROM tbl_name .
--labels
Include an initial row of column labels in the output
--delim= str
Set the column delimiter to str . The option value can consist of one or more char‐
acters. The default is to use tabs.