mysql command-line tricks

I use the MySQL shell a lot. A couple of tricks can make it more usable:

First, prompt customisation. I work with lots of different databases on different hosts. So, I customised my prompt via an environment variable:

export MYSQL_PS1="\\d@\\h> "

This makes mysql display the database name and the hostname instead of the fixed string "mysql":

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 143955 to server version: 5.0.27-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

test@counterfly>

Secondly, readline configuration. When editing long SQL statements, I prefer to use vi-like keybindings. That can be selected by adding the following lines to your ~/.inputrc file:

$if Mysql
    set keymap vi
    set editing-mode vi
$endif

You can then navigate history and edit like like you would do (almost) in vi. See your readline manual for more details.