MySQL Command Line Formatting Tips

There are times when I need to troubleshoot an issue related to data in a MySQL database. Usually I just need to find a specific piece of information in one column. But it might take some additional time to discover that one small value if that database table has another column with a large block of data, such as a JSON column. When using the “mysql” command line tool, I have found one way to change the MySQL database queries I make to help speed up the process.

What I want to do is just get the data that I am interested in quickly. I might be trying to correlate the data to another item outside of the database system. So I wouldn’t be too concerned with making efficient database queries at that moment, I just need to get the data and go.

How to Fetch Data the “Normal” Way

In this example, let’s say that there is a MySQL database with a “middleware_variant” table that has several columns of data that I am interested in (“sku” and “data_source_variant_id”) along with another column that has a very large set of JSON data (“data_destination_json”).

Also in this example, I would be connected to a server and using the “mysql” command line tool to make a quick database query. Since I am “already there” and working on that server, I may have decided not to use a different tool at that moment.

To make this simple, let’s say that I want to retrieve the data from the first row in this “middleware_variant” table. When I execute the following query:

mysql> SELECT * FROM middleware_variant LIMIT 1;

The results are presented in several screens:

Notice that in the default output, the “mysql” command line tool is doing its best to present the data in a table format. This would include visible dashed lines around the column names as well as the data values themselves. For most database tables with a few columns and small values, this works well as the default.

After scrolling through a few screens and eventually reaching the first record, I am able to see the “sku” and “data_source_variant_id” data, but I would have to do a bit of mental gymnastics to correlate the column names to the values. Because there is a JSON column with a very large value, this makes it harder to find the data that I am interested in.

Fetch Data with a Vertical Format

Now, if I adjust the query slightly to use “\G” instead of the semicolon at the end:

mysql> SELECT * FROM middleware_variant LIMIT 1\G

Then, I am able to see the column data easier, as shown here:

In this alternative output, the data is presented in a vertical format with the column name and the value displayed on one line.

For the previous “middleware_variant” database example, I am now able to find the “sku” and “data_source_variant_id” column data near the top of this output without having to scroll through multiple terminal screens.

How to Fetch JSON Data with a Pretty Format

As a bonus tip, if I need to look at the JSON data itself, I can use a query like the following:

mysql> SELECT JSON_PRETTY(data_destination_json) FROM middleware_variant LIMIT 1\G

Now, it is much easier to inspect the JSON data and search for a specific value that I might be looking for. Something like this:

Simple Command Line Formatting Tips Complete

When troubleshooting an issue, time is usually a factor. Getting information as quickly as possible is vital. I know that this simple change to using the “\G” parameter has helped a lot with my database data workflow. Hopefully this will help you, too.