MySQL Cookbook, 2nd Edition

Repeating and Editing SQL Statements

Problem

The statement you just entered contains 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.

Solution

Use mysql’s built-in input-line editing capabilities.

Discussion

If you issue a long statement only to find that it contains a syntax error, what should you do? Type in the entire corrected statement from scratch? No need: mysql maintains a statement history and supports input-line editing. This enables you to recall statements 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. 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 Enter to issue the statement (the cursor need not be at the end of the line when you do this).

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
Ctrl-A	Move to beginning of line
Ctrl-E	Move to end of line
Backspace	Delete previous character
Ctrl-D	Delete character under cursor

On Windows, the arrow key and Backspace editing functions are available as described in the table, Home and End take the place of Ctrl-A and Ctrl-E, and pressing F7 gives you a menu of recent commands.

Input-line editing is useful for more than just fixing mistakes. You can use it to try variant forms of a statement without retyping the entire thing each time. It’s also handy for entering a series of similar statements. For example, if you want to use the statement history to issue the series of INSERT statements shown earlier in Creating a Database and a Sample Table 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 Enter.

The input-line editing capabilities in mysql are based on the GNU Readline library. You can read its documentation to find out more about the many editing functions that are available. Readline documentation is part of the bash manual, which is available online at http://www.gnu.org/manual/.