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.
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 | [email protected] |
+-----------+----------------+---------------------+
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: [email protected]
3 rows in set (0.00 sec)
\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.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
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
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;
