Walker News

MySQL Dummy Guide: How To Display Long SQL Output In Page By Page Fashion?

After posting the earlier MySQL dummy guide, I found another easier way to display long SQL result in page by page fashion (tested in MySQL server version 4.1.7).

Unlike the previous post, you don’t have to write down the SQL statements in a script file and redirect it to the MySQL Command-line Tool (i.e. the mysql that also known as MySQL Monitor).

Indeed, the mysql build in a feature that allows user specify pager program (e.g. more or less in Linux-based system) to display long SQL output one page at a time.

MySQL Command-line Tool: How to pipe long SQL output to the less or more pager?
  • The first way is to start up MySQL Command-line Tool with --pager option switch that tells mysql to interactively display or show SQL results one page at a time, by using the pager program specified in $PAGER environment variable.
    In my RHEL environment, the two default pager programs are less and more. The $PAGER environment variable, however, is not set by default installation.

    Thus, in order to use --pager option switch, I need to setup the $PAGER environment variable before invoking MySQL Command-line Tool with –pager option switch:
    export PAGER=more
    mysql --pager -u walkernewsadmin -p
  • This second method doesn’t need to setup $PAGER environment variable. Before executing the SQL command, run the mysql command pager more to instruct MySQL Command-line Tool to pipe subsequent SQL results to the Linux pager program that called more. To disable the pager program, simply execute nopager mysql command. For example
    mysql> pager more
    PAGER set to 'more'
    mysql> select option_name from wp_options;
    mysql> nopager
    PAGER set to stdout

    To use other Linux pager program, e.g. less, simply replace more with less, i.e. pager less

Happy using MySQL database, one of the open source and most popular relational database system as of today!

Custom Search

2018  •  Privacy Policy