making mysql STDOUT less (or more) of a mess

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 |
+---+----------------+-------------+
grant horwood
Website | + posts

co-founder of fruitbat studios. cli-first linux snob, metric evangelist, unrepentant longhair. all the music i like is objectively horrible. he/him.

Posted by: grant horwood

co-founder of fruitbat studios. cli-first linux snob, metric evangelist, unrepentant longhair. all the music i like is objectively horrible. he/him.