MySQL Linux command line tool and properly formatted results

Question

Does anyone know of a good tool to use from the shell to query the database and get intelligible results?

On the command line, from SSH, I want to query the database using mysql but the results are pretty much unreadable because there is no formatting for the terminal screen.

If I run the following it is vary hard to make sense of the results.

use mydb;
select * from db3_settings;

I know I could use phpMyAdmin or the MySQLCC but I'm looking for a command-line solution.

score 45 · Accepted Answer · edited Feb 17 '18 at 15:03

You can obtain a vertically formatted output with \G.

This is the standard output:

mysql> select * from tblSettings;
+-----------+----------------+---------------------+
| settingid | settingname    | settingvalue        |
+-----------+----------------+---------------------+
|         1 | maxttl         | 30                  |
|         2 | traceroutepath | /usr/bin/traceroute |
|         3 | alertemail     | me@host.com         |
+-----------+----------------+---------------------+
3 rows in set (0.00 sec)

And this is what the output looks like with \G:

mysql> select * from tblSettings \G;
*************************** 1. row ***************************
   settingid: 1
 settingname: maxttl
settingvalue: 30
*************************** 2. row ***************************
   settingid: 2
 settingname: traceroutepath
settingvalue: /usr/bin/traceroute
*************************** 3. row ***************************
   settingid: 3
 settingname: alertemail
settingvalue: me@host.com
3 rows in set (0.00 sec)

It would be useful to include what `\G` means, also I believe `\G` is a terminator so you don't need a semi colon to terminate this otherwise an additional query is expected. — geedoubleya, May 31 '16 at 15:15

score 7 · Answer 2 · edited Feb 17 '18 at 15:05

7

There are a number of ways you can make results more readable.

Using limit to only view a few rows:

SELECT * FROM table LIMIT 10;

Only returning select columns:

SELECT a,b,c FROM table;

Terminating your queries with \G to send the current statement to the server to be executed and display the result using vertical format:

SELECT * FROM table \G

edited Feb 17 '18 at 15:05

Ronan Boiteau

9,608
6
34
56

answered Oct 08 '13 at 10:51

ModulusJoe

1,416
10
17

score 2 · Answer 3 · answered Nov 08 '17 at 06:09

For non-Windows systems, you can use less to make the output formatted or more readable.

mysql > pager less -SFX;
mysql > select * from databasename.table;

it pipes the sql output to less giving a tabular output that can be scrolled horizontally and vertically with the cursor keys. exit by pressing 'q'

if you don't want, reset by using

mysql> nopager

score 1 · Answer 4 · edited Aug 25 '16 at 10:03

1

In command line, you can get have the results of the returned records shown as individual, formatted records (as opposed to a long table) by using \G at the end of your query, like this:

select * from <tablename> \G;

edited Aug 25 '16 at 10:03

Gareth Parker

5,012
2
18
42

answered Aug 24 '16 at 06:18

karthik339

199
1
6

This answer has been flagged as low quality. If it answers the question, consider adding a bit of text to explain how it works. – lmo Aug 24 '16 at 22:31

MySQL Linux command line tool and properly formatted results

4 Answers4