MySQL tips

I've been studying recently for the MySQL certification test and have discovered a few tricks along the way. One of my favorites is the combination of using pager and vertical output.

To set your pager to use less:

mysql> pager less
PAGER set to less

Now when you perform a select which returns more rows than rows on your screen, the pager will allow you to paginate through the results.

To get MySQL to display results vertically instead of in columns horizontally, end your SQL with "\G" instead of with the semicolon (or "\g")...

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 lIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

 Thimble> Hi.  I think this is a good idea.  Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise, I'll put this on my TODO list and see what
Thimble> happens.

 Yes, please do that.

 Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)