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 http://www.gnu.org/manual/.

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.