Monday, 6 August 2007

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.


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

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

No comments: