Preventing Query Output from Scrolling off the Screen

1.20.1 Problem

Query output zooms off the top of your screen before you can see it.

1.20.2 Solution

Tell mysql to display output a page at a time, or run mysql in a window that allows scrollback.

1.20.3 Discussion

If a query produces many lines of output, normally they just scroll right off the top of the screen. To prevent this, tell mysql to present output a page at a time by specifying the --pager option.[6] --pager=program tells mysql to use a specific program as your pager:

[6] The --pager option is not available under Windows.

% mysql --pager=/usr/bin/less

--pager by itself tells mysql to use your default pager, as specified in your PAGER environment variable:

% mysql --pager

If your PAGER variable isn't set, you must either define it or use the first form of the command to specify a pager program explicitly. To define PAGER, use the instructions in Recipe 1.9 for setting environment variables.

Within a mysql session, you can turn paging on and off using P and . P without an argument enables paging using the program specified in your PAGER variable. P with an argument enables paging using the argument as the name of the paging program:

mysql> P
PAGER set to /bin/more
mysql> P /usr/bin/less
PAGER set to /usr/bin/less
mysql> 

PAGER set to stdout

Output paging was introduced in MySQL 3.23.28.

Another way to deal with long result sets is to use a terminal program that allows you to scroll back through previous output. Programs such as xterm for the X Window System, Terminal for Mac OS X, MacSSH or BetterTelnet for Mac OS, or Telnet for Windows allow you to set the number of output lines saved in the scrollback buffer. Under Windows NT, 2000, or XP, you can set up a DOS window that allows scrollback using the following procedure:

  1. Open the Control Panel.
  2. Create a shortcut to the MS-DOS prompt by right clicking on the Console item and dragging the mouse to where you want to place the shortcut (on the desktop, for example).
  3. Right click on the shortcut and select the Properties item from the menu that appears.
  4. Select the Layout tab in the resulting Properties window.
  5. Set the screen buffer height to the number of lines you want to save and click the OK button.

Now you should be able to launch the shortcut to get a scrollable DOS window that allows output produced by commands in that window to be retrieved by using the scrollbar.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net