Achieving readable output with MySQL

Introduction

Although I like using GUI tools for a lot of things, there are some things I feel you should be able to do without being dependent on a graphical user interface. Using the MySQL console to administer a database is one of them. More than often your only channel of access to a MySQL database server is using a SSL connection to the server host. This is often the case if the database server is configured to disallow connections from any other host than the localhost.

A useful feature

Most users associated with MySQL are familiar with a SQL statement of the form “SELECT * FROM table;” to retrieve table information. It’s one of the first things you learn. However, if the table in question has a lot of columns and contains a lot of records, then you are going to find the MySQL console a bit of a pain when reading the data. Check out the familiar SQL output result in the figure below.

The alternative

Of course you can limit the output listing by using a SQL WHERE clause and specifying the exact names of the columns of interest, but that is not the point of this posting and in some cases you may not be aware of column names you need to use (yes, I am aware of the ‘DESCRIBE table;’ command). As you know, you terminate each SQL statement with the default semicolon ‘;’ character. Without this character the database server console just waits for you to finish your SQL statement indefinitely, but there is an alternative. By replacing ‘;’ with ‘\g’ gives the exact same console output as before. OK, I know what you are thinking. Why is that any better? It’s not, however, replacing ‘;’ with the uppercase variant ‘\G’ is, and gives an output listing in a different, and often more readable, format listing each table record by it’s columns:

SELECT * FROM table \G

See the figure below showing the end of row number 2, the full row number 3, and the beginning of row number 4 from the same table as earlier:

It was only recently that I stumbled across this feature, but I have found it useful on many occasions already.

Summary

I’m sure you can see the benefit of using this alternative statement termination character on occasion. As you can see, records with many columns are a lot easier to read without being forced to limit the record column output using a WHERE clause. I’m pretty sure that using the ‘\G’ termination character isn’t standard SQL, and not something you would want to use when terminating embedded SQL in your application, but a useful feature all the same. It definitely makes reading records easier even if the output may appear to be more verbose at first. You can of course use a WHERE clause or similar as before to narrow down your output further, but using ‘\G’ makes the output easier to read in many situations:

SELECT * FROM table WHERE id = ‘something’ \G