MySQL secrets: \G instead of ;

I'm a MySQL command line junkie. Even when I have a GUI frontend installed, I always find myself typing mysql into terminal when I need to peek inside the database. As such, one trick that has changed my life for the better is the \G statement terminator. Normally when you execute a query you get something like:

1
2
3
4
5
6
7

mysql> select * from queries order by id desc limit 1;
+-----+------------------------------------+--------------+------------+------------+
| id  | query                              | query_set_id | created_at | updated_at |
+-----+------------------------------------+--------------+------------+------------+
| 969 | when a stranger calls sound boards |          103 | 2007-07-09 | 2007-07-09 | 
+-----+------------------------------------+--------------+------------+------------+
1 row in set (0.00 sec)

That's ok, but for tables with more than a few columns you get a nasty bunch of badly wrapped output that's essentially impossible to decipher. If you replace the terminating semicolon with \G, you'll get this output instead:

1
2
3
4
5
6
7
8

mysql> select * from queries order by id desc limit 1 \G
*************************** 1. row ***************************
          id: 969
       query: when a stranger calls sound boards
query_set_id: 103
  created_at: 2007-07-09
  updated_at: 2007-07-09
1 row in set (0.00 sec)

Aaaaaaaaah! Now we're talking. You can even copy this output and paste it right into a YAML fixture in your rails app (with a little fixing of the indentation)!

About this entry

You’re currently reading “MySQL secrets: \G instead of ;,” an entry on Ruby is Awesome

Tom Preston-Werner
Published:
July 10th 11:47 AM
Updated:
July 10th 11:47 AM
Sections:
 Subscribe in a reader