MySQL tip of the day: getting rid of the results formatting on the CLI

• More posts on mysql

The problem

When using MySQL from the CLI, the -e flag comes in handy to execute MySQL commands directly in bash, like mysql -uroot -p -e "SHOW DATABASES;". Which gives nicely formatted output like:

+-----------------------+
| Database              |
+-----------------------+
| drop_7                |
| drop_8                |
| pressflow             |
(...snip...)
+-----------------------+

The problem is that this table drawing takes up space on screen. Isn't there a way to get rid of it ?

The solution

The first idea is to check for the MySQL command separators. For example \G gives us this:

mysql -uroot -p -e "SHOW DATABASES\G"
*************************** 1. row ************************** 
drop_7 
*************************** 2. row ************************** 
drop_8 
*************************** 3. row **************************
pressflow(...snip...)

So we got rid of the header, but got row separators instead. Not really a net gain here! Of course we could use a

| grep -vF "row **************************"

but that's not exactly convenient, and might even cause false positives for some queries. Can we do better ?

Turns out we can ; when emitting output, the mysql client checks whether the output is a TTY and uses that info to emit the decorative wrapping around results. If the output is not a TTY, it just outputs plain, pipable, results. Which gives us a nice solution, with trusty old cat(1):

mysql -uroot -p -e "SHOW DATABASES;" | cat
Database
drop_7
drop_8
pressflow
(...snip...)

Now we're getting somewhere: piping the mysql stdout to another command makes it no longer be detected as a TTY, so the decoration is no longer used. Of course, we could also just have used the -s option to get that format, but the point here was more to take note of TTY vs pipe. In both cases, all that remains is the headers (here "Database"). Easy to remove too.

mysql -uroot -p -e "SHOW DATABASES;" | tail +2
drop_7
drop_8
pressflow
(...snip...)

That's it. The +2 option to tail makes it start output at line 2, skipping the header. No long command, no false positives.

Closing note: per the man tail reference, the command should actually be tail -n +2, but all versions of tail I know do not actually need the -n in that case.