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.

MySQL annoyance

Got bitten by a bit of insanity in MySQL 5.0.26. Imagine you have a bogus query, SELECT poo FROM SomeTable, that looks correct, except that there is no "poo" column in the said table. (You mispelled "foo". So much for your brain.) MySQL will correctly return an error, Unknown column or somesuch, when you try to run it.

Except in a subquery. Like, for example, in:

DELETE FROM SomeOtherTable WHERE id IN (SELECT poo FROM SomeTable)

which will be then exactly equivalent to a simple, unadorned DELETE FROM SomeOtherTable. And you loose your data.