MySQL Cookbook, 2nd Edition

Specifying Arbitrary Output Column Delimiters

Problem

You want mysql to produce statement output using a delimiter other than tabs.

Solution

mysql itself offers no capability for setting the output delimiter, but you can postprocess mysql output to reformat it.

Discussion

In noninteractive mode, mysql separates output columns by tabs and there is no option for specifying the output delimiter. Under some circumstances, it might be desirable to produce output that uses a different delimiter. Suppose that you want to create an output file for use by a program that expects values to be separated by colon characters (:) rather than tabs. Under Unix, you can convert tabs to arbitrary delimiters by using a utility such as tr or sed. For example, to change tabs to colons, any of the following commands would work (TAB indicates where you type a tab character):^[4]

%mysql cookbook < 
               inputfile 
                | sed -e "s/
               TAB
               /:/g" > 
               outputfile
% mysql cookbook < 
               inputfile 
                | tr "
               TAB
               " ":" > 
               outputfile
% mysql cookbook < 
               inputfile 
                | tr "\011" ":" > 
               outputfile

sed is more powerful than tr because it understands regular expressions and allows multiple substitutions. This is useful when you want to produce output in something like comma-separated values (CSV) format, which requires three substitutions:

1. Escape any quote characters that appear in the data by doubling them, so that when you use the resulting CSV file, they won’t be interpreted as column delimiters.

2. Change the tabs to commas.

3. Surround column values ...