Specifying Arbitrary Output Column Delimiters
Problem
You want mysql to produce query output using a delimiter other than tab.
Solution
Postprocess mysql’s output.
Discussion
In non-interactive mode, mysql separates output
columns with tabs and there is no option for specifying the output
delimiter. Under some circumstances, it may be desirable to produce
output that uses a different delimiter. Suppose 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 utilities
such as tr and
sed. For example, to change tabs
to colons, any of the following commands would work
(TAB indicates where you type a tab
character):[8]
%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:
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 taken as column delimiters.
Change the tabs to commas.
Surround column values with quotes.
sed allows all three subsitutions to be performed in a single command:
%mysql cookbook <inputfile ...