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.

Hurling

Answer 1

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.

Orvalorvan

Note: use \n or nopager to turn off paging – Boulevardier

TLDR: \P and \n – Siderolite

And alternatively: pager less and pager stdout – Siderolite

Answer 2

mysql> pager less
PAGER set to 'less'

Liana

also if you just type pager it will go back to standard output. – Gombroon

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. – Bloem

Answer 3

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:

Thorin

Answer 4

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.}

Shoelace

Answer 5

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

\P more

Phenology

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 – Hurling

Answer 6

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

Flopeared