you’re doing some command line mysql work, something like piping in a query to mysql and dumping the output to STDOUT. what you want, of course, is some nicely-formatted output. what you get, instead, is hot trash like this:
$ echo "SELECT * FROM albums" | mysql record_collection_db
id artist tite
1 Bratmobile Pottymouth
2 Coltrane, John Giant Steps
fortunately, mysql has some built-in formatters to help the world make sense again.
standard table with -t:
adding the -t switch to mysql gives us the familiar ascii table we know and love.
$ echo "SELECT * FROM albums" | mysql -t record_collection_db
+----+----------------+-------------+
| id | artist | tite |
+----+----------------+-------------+
| 1 | Bratmobile | Pottymouth |
| 2 | Coltrane, John | Giant Steps |
+----+----------------+-------------+
html with -H:
everyone who does frontend dev always says “don’t use table tags”, but this is actually an honest-to-god table, so we’re cool. we use -H for this.
$ echo "SELECT * FROM albums" | mysql -H record_collection_db
<TABLE BORDER=1><TR><TH>id</TH><TH>artist</TH><TH>tite</TH></TR><TR><TD>1</TD><TD>Bratmobile</TD><TD>Pottymouth</TD></TR><TR><TD>2</TD><TD>Coltrane, John</TD><TD>Giant Steps</TD></TR></TABLE>
xml with -X:
if you want to go with a retro 2003 vibe, xml is available with the -X switch.
$ echo "SELECT * FROM albums" | mysql -X record_collection_db
<?xml version="1.0"?>
<resultset statement="SELECT * FROM albums
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="id">1</field>
<field name="artist">Bratmobile</field>
<field name="tite">Pottymouth</field>
</row>
<row>
<field name="id">2</field>
<field name="artist">Coltrane, John</field>
<field name="tite">Giant Steps</field>
</row>
</resultset>
single lines with -E:
if you’re in the mood for a format worse than no format at all, mysql’s got you covered with their one-row-per-line formatting using the -E switch.
$ echo "SELECT * FROM albums" | mysql -E record_collection_db
*************************** 1. row ***************************
id: 1
artist: Bratmobile
tite: Pottymouth
*************************** 2. row ***************************
id: 2
artist: Coltrane, John
tite: Giant Steps
suppressing column headers with --skip-column-names:
any of the above formatters can be combined with --skip-column-names to suppress column header output. if you enjoy trying to guess what a column of thirty NULLs is, this is for you. great for pranks.
$ echo "SELECT * FROM albums" | mysql -t --skip-column-names record_collection_db
+---+----------------+-------------+
| 1 | Bratmobile | Pottymouth |
| 2 | Coltrane, John | Giant Steps |
+---+----------------+-------------+
