How to convert MySQL output to HTML tables

In this article I’ll explain how to control the output of the mysql client program and feed it to another program to transform the results as desired. I often transform output into HTML tables for these blog articles – at least, I do when I’m not being lazy.

The mysql command-line program can accept a command and print the results directly to STDOUT. The default output behavior differs depending on where its input comes from. When the input comes via STDIN, the output is tab-separated values. When the input comes via the -e or --execute options, or when the -t option is specified, the output is in “tabular” format, with borders drawn by pipes, dashes and plus characters.

Results can also be printed vertically, as they are when an interactive command is terminated with \G instead of a semicolon. The command-line option for this format is -E or --vertical.

I usually select results in non-tabular format and feed them to awk to turn them into HTML. Here is a quick one-liner that will format the first two columns: