Useful MySQL Tips 1

I seldom get through the working week without spending at least a little time on the MySQL server command line. I love those little things that make life a little easier – especially anything that reduces the amount of typing I have to do. I am a great believer that time spent learning a few shortcuts makes for a more productive time later! Anyway, without much further ado, here I am beginning a series of handy hints… enjoy!

Avoiding Multi-Line Queries

How often have you seen an example of a query given in a multi-line style such as the below in books and on the net. Well if you are me, many times. I can appreciate that style wise, this is a nice way of laying out a query, but in practical terms not so.

mysql> SELECT a.foo, b.foobar
    -> FROM table_a a
    -> INNER JOIN table_b b
    -> ON a.baz = b.baz
    -> WHERE a.foo = 10;

Above you can see a return character for every “->” in the query. Editing this repeatedly becomes a pain because the MySQL comand line is not really the best tool for the job. So why not use the EDIT command instead? From the documentation at the MySQL web site:

Edits the current input statement. mysql checks the values of the EDITOR and VISUAL environment variables to determine which editor to use. The default editor is vi if neither variable is set.

The edit command works only in Unix.

So, to do the same query as above with the edit command is so simple…:

mysql>EDIT

Note that there is no semi-colon after the edit command. Pressing enter fires up your command line editor – enter the query there, and once you are happy with it quit the editor. This brings you back to here…

mysql> EDIT
    ->

At the “->” type a semi-colon and hit return. That’s it – query executed. Typing EDIT again will reload the query back into the editor, so that you can carry on and edit some more.

Use Help?

Yes, the MySQL command line has a really good help system right there – no need to resort to the web. To call it up, simply type, er, HELP – just like this:

mysql> HELP;

To get a list of help subjects, use:

mysql> HELP CONTENTS;

The help is context sensitive – you can get information on a particular item – such as syntax for the GREATEST comparison operator by typing:

mysql> HELP GREATEST;
Name: 'GREATEST'
Description:
Syntax:
GREATEST(value1,value2,...)

With two or more arguments, returns the largest (maximum-valued)
argument. The arguments are compared using the same rules as for
LEAST().

URL: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Examples:
mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST('B','A','C');
        -> 'C'

Execute System Commands

Now and again you may want to jump out of the MySQL command line to run something on the command line. This is always a drag, as it involves running \q or QUIT followed by whatever you want to do, and then logging back into the MySQL command line again. Or perhaps not… take a look at the SYSTEM command:

mysql> SYSTEM pwd
/home/raz
mysql> SYSTEM ls -ar
.irssi  .bashrc  .bash_profile  .bash_logout  .bash_history	..  .
mysql>

If you are familiar with the vi or vim editors, then you’ll find that :! commandname acts in a similar way.