Changing the MySQL command line tool

There are a number of GUI's for use with MySQL but being from an Oracle background I use the command line. I think it was Ford Prefect in Hitch Hikers Guide to the Galaxy that said always know where your SQL*Plus is (I think it was Towel actually). This is also true of MySQL which every OS version you're using you should be able to use the MySQL command line in exactly the same way on each machine.

While the command line doesn't offer the same powerfull reporting abilities as SQL*Plus it is possible to change it's behaviour somewhat.

Take for example the SQL*Plus command

SET HEADING OFF

Which turns off column headings during select statement execution like so...

SQL> select 'hello' as test from dual;

TEST
-----
hello

SQL> set heading off
SQL> select 'hello' as test from dual;

hello

SQL>

This can be replicated in mysql using the -N switch when starting the command line tool.

C:\Documents and Settings\Andrew>mysql -u root -p -N
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.0.7-beta-nt

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

mysql> use pers
Database changed
mysql> select * from emps;
+---+------+------+--------+
| 1 | Paul | 1    | 999.99 |
| 2 | John | 2    | 999.99 |
| 3 | Alan | 1    | 999.99 |
+---+------+------+--------+
3 rows in set (0.16 sec)
mysql>

Another common modification people make is to change the command line prompt in SQL*Plus this is done in MySQL using the --prompt switch or simply by using the word prompt on the MySQL command line.

mysql> prompt MyPrompt >
PROMPT set to 'MyPrompt >'
MyPrompt >

However MySQL offers a number of dynamic elements that you can use in the prompt. So for example lets say we wanted to see which database we are connected to we simply use \d when defining out prompt.

mysql> prompt \d >
PROMPT set to '\d >'
pers >

This means we don't need to keep changing the prompt when connecting to different databases, this sort of prompt in SQL*Plus is achieved using a login script, it then means using another script when changing connect, buy in MySQL this is all automatic.

pers >use information_schema
Database changed
information_schema >

There are a number of options that can be used, a full list can be found here...

http://dev.mysql.com/doc/mysql/en/mysql-commands.html