How to adjust display settings of mysql command line?

Question

Command Line of mysql is not displaying results properly. I mean some columns of table are in 1st line some in 2nd line. Output is also broken into two rows. How do I adjust these settings so that it properly display results.

This is cause by white-spaces. You can use `\G` instead of `;`. Or type `help;` — ajreal, Nov 26 '10 at 13:34
@ajreal, thanX, for time being, \G is nice. though there is some setting option to adjust things. — XCeptable, Nov 26 '10 at 13:50
I posted a question similar to this yesterday see http://stackoverflow.com/questions/4278592/mysql-the-command-line-and-pagers — martynthewolf, Nov 26 '10 at 14:15

score 76 · Accepted Answer · answered Nov 26 '10 at 13:37

76

You can use the \G command (instead of the ;) at the end of your SQL queries...

Example:

SELECT * FROM USER \G

It will display your table in row form instead of column form.

answered Nov 26 '10 at 13:37

Buhake Sindi

87,898
29
167
228

thanX, for time being, \G is nice. though there is some setting option to adjust things. – XCeptable Nov 26 '10 at 13:51
2

It's so beautiful! – justinpage Jul 25 '16 at 06:47

score 37 · Answer 2 · answered Nov 26 '10 at 13:33

37

mostly this happens when the row it fetches is too long. try playing with your terminal to have scroll bar and you could even reduce fonts.

The mysql option is

mysql> pager less -n -i -S

answered Nov 26 '10 at 13:33

Jinesh Parekh

2,131
14
18

1

It's good but we have to note [it works only on UNIX or Linux platforms](http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html). – bluish Nov 29 '11 at 10:02
1

Do you know how to put this settings automatically? – workdreamer Jul 26 '12 at 13:47
This is so fantastic. I've used MySQL for years and have never bothered to look up how to deal with tables that are too long, other than using '\G', which isn't always convenient. Thank you. – ctc Jul 03 '14 at 17:32
1

mysql> pager less -n -i -S -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pager less -n -i -S' at line 1 mysql> – Owl Aug 02 '16 at 15:33
This is great. -S Causes lines longer than the screen width to be chopped rather than folded. The result is one line per row. So much easier to read. In the Mac Terminal, the right arrow key will even show the part of the line that is 'chopped'. – Patrick Cullen Feb 03 '17 at 01:08

score 5 · Answer 3 · answered Nov 03 '12 at 06:35

5

To set the scrolling automatically add this to your my.cnf file in the [client] section like this:

[client]
pager = less -n -i -S

answered Nov 03 '12 at 06:35

SilverSurfer972

73
2
8

score 4 · Answer 4 · edited Sep 21 '16 at 15:45

4

In Microsoft Windows mysql Command Prompt, Right-click on the prompt boarder and select Properties, now select the Layout tab and change the Window Size width or screen buffer width to a more suitable view. That should fix the problem. enter image description here

edited Sep 21 '16 at 15:45

techspider

3,370
13
37
61

answered Sep 21 '16 at 15:30

cam clarke

41
2

score 1 · Answer 5 · edited Jun 20 '20 at 09:12

Another option, which I was looking for and finally found, is the MySQL string function LEFT().

LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

For example ...

mysql> SELECT
    ->   `bib`,
    ->   `name_id` AS `Print`,
    ->   `code`,
    ->   `name_en` AS `EN`,
    ->   `name_fr` AS `FR`,
    ->   `name_de` AS `DE`,
    ->   `Id`
    -> FROM `iso639_Language`
    -> WHERE `Id` LIKE 'in%'
    -> ORDER BY `bib`;
+-----+------------------------------------------------------------+------+------------------------------------------------------------+------------------------------------------------+------------------+-----+
| bib | Print                                                      | code | EN                                                         | FR                                             | DE               | Id  |
+-----+------------------------------------------------------------+------+------------------------------------------------------------+------------------------------------------------+------------------+-----+
| ina | Interlingua (International Auxiliary Language Association) | ia   | Interlingua (International Auxiliary Language Association) | interlingua (langue auxiliaire internationale) | Interlingua      | ina |
| ind | Indonesian                                                 | id   | Indonesian                                                 | indonésien                                     | Bahasa Indonesia | ind |
| inh | Ingush                                                     | NULL | Ingush                                                     | ingouche                                       | Inguschisch      | inh |
+-----+------------------------------------------------------------+------+------------------------------------------------------------+------------------------------------------------+------------------+-----+
3 rows in set (0.01 sec)

... becomes ...

mysql> SELECT
    ->   `bib`,
    ->   LEFT(`name_id`,15) AS `Print`,
    ->   `code`,
    ->   LEFT(`name_en`,10) AS `EN`,
    ->   LEFT(`name_fr`,10) AS `FR`,
    ->   LEFT(`name_de`,10) AS `DE`,
    ->   `Id`
    -> FROM `iso639_Language`
    -> WHERE `Id` LIKE 'in%'
    -> ORDER BY `bib`;
+-----+-----------------+------+------------+-------------+------------+-----+
| bib | Print           | code | EN         | FR          | DE         | Id  |
+-----+-----------------+------+------------+-------------+------------+-----+
| ina | Interlingua (In | ia   | Interlingu | interlingu  | Interlingu | ina |
| ind | Indonesian      | id   | Indonesian | indonésien  | Bahasa Ind | ind |
| inh | Ingush          | NULL | Ingush     | ingouche    | Inguschisc | inh |
+-----+-----------------+------+------------+-------------+------------+-----+
3 rows in set (0.00 sec)

I do like the \G and pager less -Sin options, and use them. Often, however, I need the results in a normal fashion. With the pager option I loose the ability to reference, visually, the previous results, as the output is displayed by the selected program, externally to MySQL, and is no longer displayed when that program is closed. With the \G option I can easily compare the data of each record as a whole, but comparing records, such as viewing the column looking for patterns, or discrepancies, is not so easy.

What I used to do was remove some of the columns until the results fit in the window, and rerun the queries with other columns removed, and developed a "view" of the table in my mind. (Not a good place to keep tables.) I seldom need to view the entire column, however, when using MySQL in the terminal, and selectively reducing an excessively long column from a query can be done by editing the last command to insert LEFT() around the offending column, and I'm back to doing what I needed to do, with little distraction or lost time.

How to adjust display settings of mysql command line?

5 Answers5

Linked