MySQL command output too wide in command-line client

Question

I am using mysql command line client in terminal emulator lxterminal in Ubuntu. When I run the following command:

mysql> select * from routines where routine_name = "simpleproc";

The output is a mess:

But if I copy and paste it here, the output shows me a nice table:

mysql> select * from routines where routine_name = "simpleproc";

+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                                     | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                                                  | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| simpleproc    | def             | test           | simpleproc   | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | BEGIN


SELECT COUNT(*) INTO param1 FROM CUSTOMERS1;

END | NULL          | NULL              | SQL             | NO               | CONTAINS SQL    | NULL     | DEFINER       | 2018-01-12 15:18:20 | 2018-01-12 15:18:20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

I wonder if it is possible to view the output in a terminal emulator as a nice table like this one?

Possible workaround [here](https://www.amazon.com/dp/B01BMES072/). — Michael - sqlbot, Jan 16 '18 at 08:57

Ronan Boiteau · Accepted Answer · 2019-11-14T06:00:35.107

11

Using `mysql`'s `ego` command

From mysql's help command:

ego (\G) Send command to mysql server, display result vertically.

So by appending a \G to your select, you can get a very clean vertical output:

mysql> select * from routines where routine_name = "simpleproc" \G

Using a pager

You can tell MySQL to use the less pager with its -S option that chops wide lines and gives you an output that you can scroll with the arrow keys:

mysql> pager less -S

Thus, next time you run a command with a wide output, MySQL will let you browse the output with the less pager:

mysql> select * from routines where routine_name = "simpleproc";

If you're done with the pager and want to go back to the regular output on stdout, use this:

mysql> nopager

edited Nov 14 '19 at 06:00

answered Jan 16 '18 at 01:53

Ronan Boiteau

9,608
6
34
56

Thanks. `\G` will solve the problem when there is only one record in the query result. If there are multiple records in the query results, will you still use `\G`? – Tim Jan 16 '18 at 02:15
Sure! `\G` uses a header to separate each record. Between the first and second rows, you will have a line like this one to warn you that it's the second row which is shown below: `*************************** 2. row ***************************`. – Ronan Boiteau Jan 16 '18 at 02:21

auwalup · Answer 2 · 2021-07-11T15:28:03.657

1

You can try also adjusting the font size of the terminal but displaying the output vertically should be clear if all doesn't. use the /G option to run the query i.e

mysql> select * from routines where routine_name = "simpleproc" \G

edited Jul 11 '21 at 15:28

answered Jan 16 '18 at 02:10

auwalup

93
8

1

It is backslash not slash – Sajjad Jun 15 '21 at 12:29

score -1 · Answer 3 · edited Mar 02 '18 at 00:54

-1

If you are running on Ubuntu you can use the bash shell, it looks nice and not messed up like this.

edited Mar 02 '18 at 00:54

Robert Columbia

6,313
15
32
40

answered Jan 16 '18 at 01:51

Danushka herath

39
4
13

I am already running `mysql` in `bash` in `lxterminal`. – Tim Jan 16 '18 at 01:53

MySQL command output too wide in command-line client

3 Answers3

Using `mysql`'s `ego` command

Using a pager

Linked

MySQL command output too wide in command-line client

3 Answers3

Using mysql's ego command

Using a pager

Linked

Using `mysql`'s `ego` command