Repeating and Editing Queries

1.13.1 Problem

The query you just entered contained an error, and you want to fix it without typing the whole thing again. Or you want to repeat an earlier statement without retyping it.

1.13.2 Solution

Use mysql's built-in query editor.

1.13.3 Discussion

If you issue a long query only to find that it contains a syntax error, what should you do? Type in the entire corrected query from scratch? No need. mysql maintains a statement history and supports input-line editing. This allows you to recall queries so that you can modify and reissue them easily. There are many, many editing functions, but most people tend to use a small set of commands for the majority of their editing.[3] A basic set of useful commands is shown in the following table. Typically, you use Up Arrow to recall the previous line, Left Arrow and Right Arrow to move around within the line, and Backspace or Delete to erase characters. To add new characters to the line, just move the cursor to the appropriate spot and type them in. When you're done editing, press Return to issue the query (the cursor need not be at the end of the line when you do this).

[3] The input-line editing capabilities in mysql are based on the GNU Readline library. You can read the documentation for this library to find out more about the many editing functions that are available. For more information, check the Bash manual, available online at

Editing Key

Effect of Key

Up Arrow

Scroll up through statement history

Down Arrow

Scroll down through statement history

Left Arrow

Move left within line

Right Arrow

Move right within line


Move to beginning of line


Move to end of line


Delete previous character


Delete character under cursor

Input-line editing is useful for more than just fixing mistakes. You can use it to try out variant forms of a query without retyping the entire thing each time. It's also handy for entering a series of similar statements. For example, if you wanted to use the query history to issue the series of INSERT statements shown earlier in Recipe 1.3 to create the limbs table, first enter the initial INSERT statement. Then, to issue each successive statement, press the Up Arrow key to recall the previous statement with the cursor at the end, backspace back through the column values to erase them, enter the new values, and press Return.

To recall a statement that was entered on multiple lines, the editing procedure is a little trickier than for single-line statements. In this case, you must recall and reenter each successive line of the query in order. For example, if you've entered a two-line query that contains a mistake, press Up Arrow twice to recall the first line. Make any modifications necessary and press Return. Then press Up Arrow twice more to recall the second line. Modify it, press Return, and the query will execute.

Under Windows, mysql allows statement recall only for NT-based systems. For versions such as Windows 98 or Me, you can use the special mysqlc client program instead. However, mysqlc requires an additional library file, cygwinb19.dll. If you find a copy of this library in the same directory where mysqlc is installed (the bin dir under the MySQL installation directory), you should be all set. If the library is located in the MySQL lib directory, copy it into your Windows system directory. The command looks something like this; you should modify it to reflect the actual locations of the two directories on your system:

C:> copy C:mysqllibcygwinb19.dll C:WindowsSystem

After you make sure the library is in a location where mysqlc can find it, invoke mysqlc and it should be capable of input-line editing.

One unfortunate consequence of using mysqlc is that it's actually a fairly old program. (For example, even in MySQL 4.x distributions, mysqlc dates back to 3.22.7.) This means it doesn't understand newer statements such as SOURCE.

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 © 2008-2017.
If you may any questions please contact us: