Open Source ePortfolios
Create. Share. Engage.

Exposing My Ignorance /
Making the mysql client act like the psql client

I work a lot more with Postgres than with MySQL, consequently I'm a lot more familiar with all the ways to make the "psql" Postgress command-line client act nicely than I am with the "mysql" command-line client.

One of the things that I've often longed for in the mysql client, is psql's "\x" extended table formatting option. What "\x" does is change the display of records, so that instead of using ASCII to simulate a table in the terminal, it instead prints each field of each record on a separate line.

This comes in real handy when you're dealing with tables that have a lot of columns, which otherwise will tend to wrap in the terminal and become basically unreadable.

So for instance, without \x, the query "SELECT * FROM usr;" looks like this:

 id | username |                password                |   salt   | passwordchange | active | deleted |
expiry | expirymailsent |      lastlogin      | lastlastlogin |     lastaccess      | inactivemailsent |
staff | admin | firstname | lastname | studentid | preferredname |          email          | profileicon |
 suspendedctime | suspendedreason | suspendedcusr |  quota   | quotaused | authinstance | ctime |
showhomeinfo | logintries | unread | urlid | probation
----+----------+----------------------------------------+----------+----------------+--------+---------+---
-----+----------------+---------------------+---------------+---------------------+------------------+-----
--+-------+-----------+----------+-----------+---------------+-------------------------+-------------+-----
-----------+-----------------+---------------+----------+-----------+--------------+-------+--------------+
------------+--------+-------+-----------
  0 | root     | *                                      | *        |              0 |      1 |       0 |
        |              0 |                     |               |                     |                0 |
     0 |     0 | System    | User     |           |               | root@example.org        |             
|                |                 |               | 52428800 |         0 |            1 |       |
            1 |          0 |      0 |       |         0
  1 | admin    | $2a$12$Seg3PhfNnNSKEtYXY0JfyEe779zkOhW | 441d8efb |              0 |      1 |       0 |
        |              0 | 2015-11-10 14:13:06 |               | 2015-11-10 14:25:13 |                0 |
     0 |     1 | Admin     | User     |           |               | aaronw1@catalyst.net.nz |             
|                |                 |               | 52428800 |         0 |            1 |       |
            1 |          0 |      0 |       |         0
(2 rows)

 Turn on \x, and it instead looks like this:

-[ RECORD 1 ]----+---------------------------------------
id               | 0
username         | root
password         | *
salt             | *
passwordchange   | 0
active           | 1
deleted          | 0
expiry           |
expirymailsent   | 0
lastlogin        |
lastlastlogin    |
lastaccess       |
inactivemailsent | 0
staff            | 0
admin            | 0
firstname        | System
lastname         | User
studentid        |
preferredname    |
email            | root@example.org
profileicon      |
suspendedctime   |
suspendedreason  |
suspendedcusr    |
quota            | 52428800
quotaused        | 0
authinstance     | 1
ctime            |
showhomeinfo     | 1
logintries       | 0
unread           | 0
urlid            |
probation        | 0
-[ RECORD 2 ]----+---------------------------------------
id               | 1
username         | admin
password         | $2a$12$Seg3PhfNnNSKEtYXY0JfyEe779zkOhW
salt             | 441d8efb
passwordchange   | 0
active           | 1
deleted          | 0
expiry           |
expirymailsent   | 0
lastlogin        | 2015-11-10 14:13:06
lastlastlogin    |
lastaccess       | 2015-11-10 14:25:13
inactivemailsent | 0
staff            | 0
admin            | 1
firstname        | Admin
lastname         | User
studentid        |
preferredname    |
email            | aaronw1@catalyst.net.nz
profileicon      |
suspendedctime   |
suspendedreason  |
suspendedcusr    |
quota            | 52428800
quotaused        | 0
authinstance     | 1
ctime            |
showhomeinfo     | 1
:

It uses a lot more vertical space, but you can actually read it with your naked eyes and tell which value matches up with which column.

Well, having to debug Mahara's current problem with MySQL deadlocks, today I decided to figure it out. Taking a look through the mysql client's internal help and man pages, I discovered there's a "\G" command that will cause it to render the output in vertical format, that looks a lot like psql. \G is a bit strange. It's not a setting toggle like psql's \x. Instead, you use it in place of a semicolon to end your query.

mysql> select * from usr limit 1 \G
*************************** 1. row ***************************
              id: 0
        username: root
        password: *
            salt: *
  passwordchange: 0
          active: 1
         deleted: 0
          expiry: NULL
  expirymailsent: 0
       lastlogin: NULL
   lastlastlogin: NULL
      lastaccess: NULL
inactivemailsent: 0
           staff: 0
           admin: 0
       firstname: System
        lastname: User
       studentid: NULL
   preferredname: NULL
           email: root@example.org
     profileicon: NULL
  suspendedctime: NULL
 suspendedreason: NULL
   suspendedcusr: NULL
           quota: 52428800
       quotaused: 0
    authinstance: 1
           ctime: NULL
    showhomeinfo: 1
      logintries: 0
          unread: 0
           urlid: NULL
       probation: 0
1 row in set (0.00 sec)

I quickly discovered, however, that this doesn't automatically go to a pager like psql's "\x". So you you return more rows than will fit on the screen, they'll all zip off to outer space. However, MySQL does have a "\P" option to set your pager command. So if you do "\P less", then you'll send your output to "less", and be able to page up and down and search and all that good stuff. In fact, the mysql man page suggests you use "\P less -n -i -S -F -X", which will add line numbers, case-insensitive search, no wrapping of long lines, *and* print the results directly to the screen if they're short enough to fit all on one screen.

I find this easier to remember as "\P less -SinFX". ;)

In fact, with the "-S" option to disable line wrapping, you don't even really need the vertical output format anymore! But, if you do really like it, you can turn it on by default in mysql by using the flag "mysql --auto-vertical-output", which is even smarter than just toggling "\x" in psql, because it will automatically switch you to the vertical output mode when your query is too wide to fit on the screen.

This got me thinking, well, if mysql can do that, can't psql? So I dug through the psql man page and noticed this command:

\pset expanded auto

... which, as of psql version 9.2, will do the same thing, making psql switch between vertical mode and tabular mode depending on how wide your query output results are.

So in the end, I've discovered that one of the things I preferred most about the psql client can be done in mysql as well. And I've improved my ability to use the psql client at the same time. These are things that can happen when you read the man page, even for utilities you use every day!

And of course if you find some mysql/psql client options that you use every time, you can pop them in your "~/.my.cnf" or "~/.psqlrc" file, and/or use a shell alias, to make them happen every time.