MySQL the command line and pagers

Question

I cant find anything about this from searching here.

I use mysql on the command line at work and I work with fairly large tables so I set the mysql pager allowing a more readable result if I run a query, that returns 1000's of results. I use the command below to set the pager.

\P less -Sin

This suits my needs but has left me wondering if there are any more pager styles that mysql uses on the command line.

score 17 · Accepted Answer · answered Nov 25 '10 at 16:01

17

The MySQL client just passes its output to whatever command you specify with \P (for "Pager").

-Sin are commandline switches to the program less. From man less:

-i Causes searches to ignore case
-n Suppresses line numbers
-S Causes lines longer than the screen width to be chopped rather than folded.

For more options of the MySQL client, see reference.

answered Nov 25 '10 at 16:01

Piskvor left the building

91,498
46
177
222

4

Note: use `\n` or `nopager` to turn off paging – Mike B May 15 '20 at 23:19
TLDR: `\P` and `\n` – Elvin Nov 08 '22 at 14:25
And alternatively: `pager less` and `pager stdout` – Elvin Nov 08 '22 at 14:26

score 8 · Answer 2 · answered Dec 19 '13 at 00:27

8

mysql> pager less
PAGER set to 'less'

answered Dec 19 '13 at 00:27

paiego

3,619
34
43

1

also if you just type `pager` it will go back to standard output. – wranvaud Oct 31 '16 at 21:57
Also, if you are using a very minimal OS that does not include ```less``` by default like I do on Docker, setting pager to ```more``` would be of far better than working with ```stdout```. – Saeed Ahadian Aug 03 '22 at 07:46

score 5 · Answer 3 · answered Jan 30 '19 at 11:55

You might want to try pspg: Unix pager designed for work with tables. Designed for PostgreSQL, but MySQL is supported too.

Main target

possibility to freeze first few rows, first few columns

possibility to use fancy colors - like mcview or FoxPro

In action:

score 1 · Answer 4 · answered Apr 01 '15 at 12:15

This post is old, but still very helpful.

You can set the pager to whatever you want, including a script that parses all output before feeding it back to you. The examples there include using an add-on tool that makes EXPLAIN output more readable.

_{Also note that to turn off this functionality and return to normal stdout the command is nopager.}

score 0 · Answer 5 · answered Nov 25 '10 at 15:40

0

If you don't like less you can use more :)

\P more

answered Nov 25 '10 at 15:40

Khaled

1,114
14
31

it's not that I don't like it, I'm just interested to see if more pager outputs exist. I just tried more and its very similar to the basic output. Using less seems to display the result set in a much neater fashion – martynthewolf Nov 25 '10 at 15:45

score 0 · Answer 6 · answered Apr 27 '23 at 11:20

Use pager

mysql -u root --database=test
MariaDB[test]> pager less -SFX
DESCRIBE Alerts;

OUTPUT

    +----------------------+--------------+------+-----+---------+----------------+
    | Field                | Type         | Null | Key | Default | Extra          |
    +----------------------+--------------+------+-----+---------+----------------+
    | id_alert             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
    | id_importance_choice | varchar(255) | NO   |     | NULL    |                |
    | score                | varchar(255) | NO   |     | NULL    |                |
    | level                | varchar(255) | NO   |     | NULL    |                |
    | users                | longtext     | NO   |     | NULL    |                |
    | createdAt            | timestamp    | YES  |     | NULL    |                |
    | updatedAt            | timestamp    | YES  |     | NULL    |                |
    | deletedAt            | timestamp    | YES  |     | NULL    |                |
    +----------------------+--------------+------+-----+---------+----------------+

Desable pager

MariaDB[test]> pager more

MySQL the command line and pagers

6 Answers6

Linked